如何生成包含列前十样本数据的数据库全表结构汇总？

原SQL存在几个核心问题：

• 静态SQL无法直接用变量作为表名，需通过动态SQL处理
• 子查询错误拼接了列名而非列的实际值
• 未限制仅获取前十条样本数据
• STUFF函数参数有误，应移除开头第一个逗号而非前两个

方案1：动态SQL生成完整结果 #

这种方式会遍历所有列，为每个列查询前十条数据并拼接成字符串：

DECLARE @SQL NVARCHAR(MAX) = ''

-- 生成每个列的查询语句
SELECT @SQL = @SQL + '
UNION ALL
SELECT 
    TABLE_SCHEMA AS [Schema],
    TABLE_NAME AS [Table],
    COLUMN_NAME AS [Column],
    DATA_TYPE AS [Data Type],
    IS_NULLABLE AS [NULL],
    STUFF((
        SELECT '', '' + CAST([' + COLUMN_NAME + '] AS NVARCHAR(MAX))
        FROM (SELECT TOP 10 [' + COLUMN_NAME + '] FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']) AS TopValues
        FOR XML PATH(''''), TYPE
    ).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''') AS [Reference_ Data (top ten values)]
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = ''' + TABLE_SCHEMA + ''' AND TABLE_NAME = ''' + TABLE_NAME + ''' AND COLUMN_NAME = ''' + COLUMN_NAME + ''''
FROM INFORMATION_SCHEMA.COLUMNS

-- 移除开头的UNION ALL
SET @SQL = STUFF(@SQL, 1, 10, '')

-- 添加排序规则
SET @SQL = @SQL + ' ORDER BY [Schema], [Table], [Column]'

-- 执行动态SQL
EXEC sp_executesql @SQL

方案2：自定义函数+查询 #

如果需要更灵活的调用，可先创建函数封装样本数据查询逻辑：

-- 创建自定义函数
CREATE FUNCTION dbo.GetTopTenValues(@SchemaName NVARCHAR(128), @TableName NVARCHAR(128), @ColumnName NVARCHAR(128))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX) = '
        SELECT @Result = STUFF((
            SELECT '', '' + CAST([' + @ColumnName + '] AS NVARCHAR(MAX))
            FROM (SELECT TOP 10 [' + @ColumnName + '] FROM [' + @SchemaName + '].[' + @TableName + ']) AS TopValues
            FOR XML PATH(''''), TYPE
        ).value(''.'', ''NVARCHAR(MAX)''), 1, 2, '''')'
    
    EXEC sp_executesql @SQL, N'@Result NVARCHAR(MAX) OUTPUT', @Result OUTPUT
    
    RETURN ISNULL(@Result, 'No data')
END
GO

-- 查询所有表结构及样本数据
SELECT
    TABLE_SCHEMA AS [Schema],
    TABLE_NAME AS [Table],
    COLUMN_NAME AS [Column],
    DATA_TYPE AS [Data Type],
    IS_NULLABLE AS [NULL],
    dbo.GetTopTenValues(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME) AS [Reference_ Data (top ten values)]
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
GO

注意事项 #

• 若列包含逗号、XML敏感字符，可在拼接时用REPLACE处理，避免格式混乱
• 大表执行会有一定性能开销，建议非高峰时段运行
• 其他数据库语法有差异：比如PostgreSQL用STRING_AGG+LIMIT 10，MySQL用GROUP_CONCAT+LIMIT 10

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