t-sql：：列出所有表、列和透视内容

我正在使用SSMS工具Data Discovery and Classification。

该工具自动搜索列名，如%address%、%name%、%surname%、%e-mail%、%tax%、%zip%等. 并且很好地向您建议它可能是一个合理的数据。

事实是，在盎格鲁-撒克逊社会之外，列名不是英语，但可以是法语、西班牙语、意大利语等。

所以我找到了一个查询，可以帮助我根据我的语言列出有意义的数据：

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;

非常好。

但是如果我能添加一个名为Content的最后一栏PIVOT每一栏的内容和每一栏的SELECT TOP 5并将其很好地打印在最后一栏中会更好。

您能帮我存档吗？

这将是DYO数据发现和分类。

编辑：我可能表达得不好。

我正在对AdventureWorks2019运行mz查询：

SCHEMA_NAME	TABLE_NAME	COLUMN_NAME	DATA_TYPE	Data_Preview
人员	地址	地址ID	int	空
人员	地址	地址行1	nvarchar	空
人员	地址	地址行2	nvarchar	空
人员	地址	城市	nvarchar	空
人员	地址	StateProvinceID	int	空
人员	地址	邮政编码	nvarchar	空

我想PIVOT最后一列的每一列(假设TOP 5)的内容

SCHEMA_NAME	TABLE_NAME	COLUMN_NAME	DATA_TYPE	Data_Preview
人员	地址	地址ID	int	1、2、3、4、5
人员	地址	地址行1	nvarchar	1970年纳帕山，9833Mt.DIAS BLV，7484 Roundtree Drive，9539 Glenside DR，1226鞋街
人员	地址	地址行2	nvarchar	NULL，NULL
人员	地址	城市	nvarchar	Bothell，Bothell
人员	地址	StateProvinceID	int	79，79，79，79
人员	地址	邮政编码	nvarchar	98011、98011、98011、98011、98011

每个数据都可以用逗号或其他符号分隔。结果是我希望预览列中实际存在的数据：

这些人显然是来存档的：

• https://docs.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/

谢谢

---

解决方案

已更新为支持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