如何获取表字段跨Schema出现次数及非空数据样本？

要获取每列的非空数据样本，核心是从元数据（INFORMATION_SCHEMA）拿到表和列信息后，动态生成查询语句去读取实际表的数据。下面以SQL Server为例，提供两种可行方案：

方案一：用动态SQL生成批量查询脚本 #

直接生成能输出所有列信息+样本的SQL语句，执行即可得到结果：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
SELECT 
    ''' + QUOTENAME(Table_Catalog) + ''' AS Table_Catalog,
    ''' + QUOTENAME(TABLE_SCHEMA) + ''' AS TABLE_SCHEMA,
    ''' + QUOTENAME(Table_name) + ''' AS TableNameWhereColumnAppears,
    ''' + QUOTENAME(Column_name) + ''' AS Column_name,
    ''' + DATA_TYPE + ''' AS DATA_TYPE,
    ' + CASE WHEN Character_Maximum_length IS NOT NULL THEN CAST(Character_Maximum_length AS NVARCHAR(10)) ELSE 'NULL' END + ' AS Character_Maximum_length,
    ''' + is_nullable + ''' AS is_nullable,
    ' + CAST(COUNT(Column_name) OVER (PARTITION BY Column_name) AS NVARCHAR(10)) + ' AS CountOfHowManyTimesThatColumnAppearsInAllSchemas,
    (SELECT STRING_AGG(CAST(' + QUOTENAME(Column_name) + ' AS NVARCHAR(MAX)), '', '') 
     FROM (SELECT TOP 10 ' + QUOTENAME(Column_name) + ' 
           FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_name) + ' 
           WHERE ' + QUOTENAME(Column_name) + ' IS NOT NULL) AS Sample) AS NonNullSample
UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS;

-- 去掉最后多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

EXEC sp_executesql @sql;

方案二：用存储过程封装逻辑 #

如果需要重复执行，可封装成存储过程，方便调用：

CREATE PROCEDURE GetColumnInfoWithSamples
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql += N'
    SELECT 
        ''' + QUOTENAME(Table_Catalog) + ''' AS Table_Catalog,
        ''' + QUOTENAME(TABLE_SCHEMA) + ''' AS TABLE_SCHEMA,
        ''' + QUOTENAME(Table_name) + ''' AS TableNameWhereColumnAppears,
        ''' + QUOTENAME(Column_name) + ''' AS Column_name,
        ''' + DATA_TYPE + ''' AS DATA_TYPE,
        ' + CASE WHEN Character_Maximum_length IS NOT NULL THEN CAST(Character_Maximum_length AS NVARCHAR(10)) ELSE 'NULL' END + ' AS Character_Maximum_length,
        ''' + is_nullable + ''' AS is_nullable,
        ' + CAST(COUNT(Column_name) OVER (PARTITION BY Column_name) AS NVARCHAR(10)) + ' AS CountOfHowManyTimesThatColumnAppearsInAllSchemas,
        (SELECT STRING_AGG(CAST(' + QUOTENAME(Column_name) + ' AS NVARCHAR(MAX)), '', '') 
         FROM (SELECT TOP 10 ' + QUOTENAME(Column_name) + ' 
               FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(Table_name) + ' 
               WHERE ' + QUOTENAME(Column_name) + ' IS NOT NULL) AS Sample) AS NonNullSample
    UNION ALL'
    FROM INFORMATION_SCHEMA.COLUMNS;

    SET @sql = LEFT(@sql, LEN(@sql) - 10);
    EXEC sp_executesql @sql;
END;

调用方式：EXEC GetColumnInfoWithSamples;

关键注意事项 #

• 权限要求：执行账号需要有所有目标表的SELECT权限，否则会报权限错误。
• 跨数据库适配：如果是MySQL，把STRING_AGG换成GROUP_CONCAT，QUOTENAME换成CONCAT('', 列名, '')；PostgreSQL则用STRING_AGG，引用表时用"schema"."table"格式。
• 样本调整：把TOP 10改成OFFSET X ROWS FETCH NEXT 10 ROWS ONLY可以获取中间10条样本。

内容的提问来源于stack exchange，提问作者IMeitzen