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

本教程将介绍t-sql::列出所有表、列和透视内容的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

t-sql::列出所有表、列和透视内容 教程 第1张

问题描述

我正在使用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
FROMsys.tables AS tab
JOINsys.schemas AS s
 ON s.schema_id = tab.schema_id
JOINsys.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)= '',
@DynamicQueryNVARCHAR(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 

好了关于t-sql::列出所有表、列和透视内容的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。