Summary of all tables in database

Question

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

This question has been asked many times... you can probably find them with a bit of research. — Dale K, Jul 09 '23 at 04:49
https://stackoverflow.com/questions/70006750/t-sql-list-all-tables-columns-and-pivot-content — Dale K, Jul 09 '23 at 04:55
You can't fetch data that way. Unfortunately you need some kind of loop that Selects from each table separately. — siggemannen, Jul 09 '23 at 08:00
Asides: You might want to extend your `[data type]` column to include `CHARACTER_MAXIMUM_LENGTH` for string types, `DATETIME_PRECISION` for `datetime` and `interval`, ... . You might also find the top 10 `distinct` values to be more illustrative examples than just the top 10 values which may all be the same for a column. — HABO, Jul 10 '23 at 00:49

Amira Bedhiafi · Accepted Answer · 2023-07-09T23:10:55.273

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]

Summary of all tables in database

1 Answers1