SQL Server Summary of all tables in database

I am trying to get a summary of all the tables in my database:

I want the results to look as follows where the last column is a sample of each column in each table in the database:
| schema | table | column | data type | NULL | [Reference_ Data (top ten values)] |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| dbo | mytable | mycolumn | int | yes | 1,2,3,4,5,6,7,8,9,10 |

I tried using the following query but it doesn't work

SELECT T.TABLE_SCHEMA AS [Schema], T.TABLE_NAME AS [Table], T.COLUMN_NAME AS [Column], T.DATA_TYPE AS [Data Type], T.IS_NULLABLE AS [NULL], STUFF
(
    (
        SELECT ',' + T.COLUMN_NAME
        FROM T.TABLE_NAME
        ORDER BY T.TABLE_NAME FOR XML PATH('')
    ), 1,2, ''
) [Reference_ Data (top ten values)]
FROM INFORMATION_SCHEMA.COLUMNS AS T
ORDER BY TABLE_SCHEMA, TABLE_NAME

I used a cursor to iterate through each table and column in the INFORMATION_SCHEMA.COLUMNS view, which builds and executes a dynamic SQL query for each combination to retrieve the top 10 values from that column Where I stored the results in a temporary table #Top10Values .

DECLARE @schema NVARCHAR(128)
DECLARE @table NVARCHAR(128)
DECLARE @column NVARCHAR(128)
DECLARE @sql NVARCHAR(MAX)

CREATE TABLE #Top10Values
(
    [Schema] NVARCHAR(128),
    [Table] NVARCHAR(128),
    [Column] NVARCHAR(128),
    [Data Type] NVARCHAR(128),
    [NULL] NVARCHAR(3),
    [Reference_Data (top ten values)] NVARCHAR(MAX)
)

DECLARE column_cursor CURSOR FOR
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA <> 'sys' -- Exclude system tables if necessary
    ORDER BY TABLE_SCHEMA, TABLE_NAME

OPEN column_cursor

FETCH NEXT FROM column_cursor INTO @schema, @table, @column

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @sql = '
        INSERT INTO #Top10Values ([Schema], [Table], [Column], [Data Type], [NULL], [Reference_Data (top ten values)])
        SELECT ''' + @schema + ''', ''' + @table + ''', ''' + @column + ''',
               DATA_TYPE, IS_NULLABLE,
               STUFF((SELECT TOP 10 '', '' + QUOTENAME(' + @column + ')
                      FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
                      WHERE ' + QUOTENAME(@column) + ' IS NOT NULL
                      ORDER BY ' + QUOTENAME(@column) + '
                      FOR XML PATH('''')), 1, 2, '''')
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = ''' + @schema + '''
          AND TABLE_NAME = ''' + @table + '''
          AND COLUMN_NAME = ''' + @column + ''''

    EXEC sp_executesql @sql

    FETCH NEXT FROM column_cursor INTO @schema, @table, @column
END

CLOSE column_cursor
DEALLOCATE column_cursor

SELECT *
FROM #Top10Values
ORDER BY [Schema], [Table]

DROP TABLE #Top10Values

This is an example of my fictive database :

Schema  Table           Column          Data Type   NULL    Reference_Data (top ten values)
dbo     ClientInterface ACCOUNT_OFFICER float       YES     [401], [401], [401], [401], [401], [401], [401], [401], [401], [401]
dbo     ClientInterface OTHER_OFFICER   float       YES     [2006], [2006], [2006], [2006], [2006], [2006], [2006], [2006], [2006], [2008]
dbo     ClientInterface INDUSTRY        float       YES     [1005], [1005], [1006], [1010], [1010], [1010], [1010], [1010], [1010], [1010]
dbo     ClientInterface NATIONALITY     nvarchar    YES     [TN], [TN], [TN], [TN], [TN], [TN], [TN], [TN], [TN], [TN]
dbo     ClientInterface GENDER          nvarchar    YES     [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE], [FEMALE]
dbo     ClientInterface SECTOR          float       YES     [1000], [1000], [1000], [1000], [1000], [1000], [1000], [1000], [1000], [1000]

SQL Server Summary of all tables in database

1条答案

相关问题

热门标签

最新问答