T-SQL :: List all tables, columns and pivot content

Question

I'm using the SSMS tool Data Discovery and Classification.

The tool automatically search for columns name like %address%, %name%, %surname%, %e-mail%, %tax%, %zip%, etc... and nicely suggests you what it might be a sensible data.

The fact is that outside from Anglo-Saxon societies the column name is not in English but it can be in French, Spanish, Italian, etc..

So I found a query that could help me out list sensible data based on my language:

SELECT schema_name(tab.schema_id) AS schema_name
    ,tab.name AS table_name
    ,col.name AS column_name
    ,t.name AS data_type
    ,NULL as Data_Preview
FROM sys.tables AS tab
INNER JOIN sys.columns AS col ON tab.object_id = col.object_id
LEFT JOIN sys.types AS t ON col.user_type_id = t.user_type_id
ORDER BY schema_name
    ,table_name
    ,column_id;

Very nice.

But it would be better if I could add a last column called Content that PIVOT the content of each column and SELECT TOP 5 of each one and nicely print it in the last column.

Can you help me archive that?

That would be a DYO Data Discovery and Classification.

EDIT: I might have expressed myself badly.

I'm running mz query against AdventureWorks2019:

schema_name	table_name	column_name	data_type	Data_Preview
Person	Address	AddressID	int	NULL
Person	Address	AddressLine1	nvarchar	NULL
Person	Address	AddressLine2	nvarchar	NULL
Person	Address	City	nvarchar	NULL
Person	Address	StateProvinceID	int	NULL
Person	Address	PostalCode	nvarchar	NULL

I want to PIVOT the content of each column (let's say the TOP 5) tho the last column

schema_name	table_name	column_name	data_type	Data_Preview
Person	Address	AddressID	int	1,2,3,4,5
Person	Address	AddressLine1	nvarchar	1970 Napa Ct.,9833 Mt. Dias Blv.,7484 Roundtree Drive,9539 Glenside Dr,1226 Shoe St.
Person	Address	AddressLine2	nvarchar	NULL,NULL,NULL,NULL,NULL
Person	Address	City	nvarchar	Bothell,Bothell,Bothell,Bothell,Bothell
Person	Address	StateProvinceID	int	79,79,79,79,79
Person	Address	PostalCode	nvarchar	98011,98011,98011,98011,98011

Each data can be separated by a coma or whatever. The result is that I want to have a preview of the data that is actually in the column:

This guys apparently arrived to archive that:

• https://learn.microsoft.com/en-us/answers/questions/285445/how-to-get-column-values-in-one-comma-separated-va.html
• Pivot and comma Separated value
• https://www.mikekale.com/rows-to-comma-separated-lists-in-sql-server/

Thank you

Answer 1

Updated to support 2016

DROP TABLE IF EXISTS #ColumnsToDisplay

SELECT    ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
          s.name AS SchemaName,
          tab.name AS table_name,
          col.column_id,
          col.name AS column_name,
          t.name AS data_type,
          col.max_length,
          col.precision AS PrecisionNumber,
          CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO      #ColumnsToDisplay
FROM      sys.tables AS tab
JOIN      sys.schemas AS s
    ON    s.schema_id = tab.schema_id
JOIN      sys.columns AS col
    ON    col.object_id = tab.object_id
LEFT JOIN sys.types AS t
    ON    col.user_type_id = t.user_type_id
     
DECLARE @Iterations       INT = 0,
        @CurrentIteration INT = 1;

SELECT @Iterations = MAX (Iteration)
FROM   #ColumnsToDisplay

WHILE @CurrentIteration <= @Iterations
BEGIN
    DECLARE @CurrentTableName  VARCHAR(100)   = '',
            @CurrentColumnName VARCHAR(100)   = '',
            @DynamicQuery      NVARCHAR(1000) = N''
    DECLARE @Sample VARCHAR(MAX)

    SET @CurrentTableName = '';
    SET @DynamicQuery = N'';
    SELECT @CurrentTableName = CONCAT (ttq.SchemaName, '.', ttq.table_name),
           @CurrentColumnName = ttq.column_name
    FROM   #ColumnsToDisplay AS ttq
    WHERE  ttq.Iteration = @CurrentIteration

    IF (@CurrentTableName = '')
    BEGIN
        SET @CurrentIteration += 1

        CONTINUE
    END

 -- SQL Server 2019
 -- SET @DynamicQuery = CONCAT (N'
 --       SELECT @Sample = STRING_AGG(t.ColumnData,'', '') 
 --       FROM (
 --               SELECT TOP 5  CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
 --               FROM ', @CurrentTableName, ' AS x 
 --               WHERE x.[', @CurrentColumnName, '] IS NOT NULL
 --       )t')

 -- SQL Server 2016 and lower where Stuff is supported   
    SET @DynamicQuery = CONCAT (N'
    SELECT @Sample =  STUFF((SELECT '', ''+ t.ColumnData  
    FROM (
            SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
            FROM ', @CurrentTableName, ' AS x 
            WHERE x.[', @CurrentColumnName, '] IS NOT NULL 
    ) AS t
       FOR XML PATH('''')),1,1,'''')')
        
    EXECUTE sys.sp_executesql @DynamicQuery,
                              N'@Sample VARCHAR(MAX) OUTPUT',
                              @Sample = @Sample OUTPUT

    UPDATE #ColumnsToDisplay
    SET    DataSample = @Sample
    WHERE  Iteration = @CurrentIteration

    SET @CurrentIteration += 1
END

SELECT ctd.Iteration,
       ctd.SchemaName,
       ctd.table_name,
       ctd.column_id,
       ctd.column_name,
       ctd.data_type,
       ctd.max_length,
       ctd.PrecisionNumber,
       ctd.DataSample
FROM   #ColumnsToDisplay AS ctd