在SQL Server中利用INFORMATION_SCHEMA.COLUMNS生成示例列的技术问题

问题根源 #

SQL Server的标量函数无法执行动态SQL，所以直接在函数里用变量作为表名/列名的方式行不通，必须用动态SQL来实现需求。

方案一：动态拼接UNION ALL查询 #

直接生成包含所有表列查询的动态SQL，一次性执行并返回结果：

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

-- 拼接每个表列的查询语句
SELECT @SQL += N'
SELECT 
    TABLE_NAME = ''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''',
    COLUMN_NAME = ''' + QUOTENAME(COLUMN_NAME) + ''',
    example = (SELECT TOP(1) ' + QUOTENAME(COLUMN_NAME) + ' FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL)
UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS;

-- 移除末尾多余的UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

-- 执行动态SQL
EXEC sp_executesql @SQL;

关键说明 #

• QUOTENAME()：用来处理包含特殊字符（如空格、关键字）的表/列名，同时避免SQL注入风险。
• 如果某列全为NULL，对应的example字段会返回NULL，符合需求逻辑。

方案二：游标遍历+临时表存储结果 #

如果需要分步处理或对结果做后续操作，可以用游标遍历每个列，将结果存入临时表后再查询：

-- 创建临时表存储最终结果
CREATE TABLE #Results (
    TABLE_NAME NVARCHAR(128),
    COLUMN_NAME NVARCHAR(128),
    example SQL_VARIANT -- 兼容不同数据类型的列值
);

DECLARE 
    @SchemaName NVARCHAR(128),
    @TableName NVARCHAR(128),
    @ColumnName NVARCHAR(128),
    @SQL NVARCHAR(MAX);

-- 声明游标遍历所有列
DECLARE ColumnCursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS;

OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @SchemaName, @TableName, @ColumnName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 动态生成插入语句
    SET @SQL = N'
    INSERT INTO #Results (TABLE_NAME, COLUMN_NAME, example)
    SELECT 
        ''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ''',
        ''' + QUOTENAME(@ColumnName) + ''',
        (SELECT TOP(1) ' + QUOTENAME(@ColumnName) + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NOT NULL)';
    
    EXEC sp_executesql @SQL;

    FETCH NEXT FROM ColumnCursor INTO @SchemaName, @TableName, @ColumnName;
END;

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

-- 查询结果
SELECT * FROM #Results;

-- 清理临时表
DROP TABLE #Results;

关键说明 #

• SQL_VARIANT类型：用来存储不同数据类型的列值（如int、varchar、datetime等），确保所有类型的列值都能存入临时表。
• 游标方式适合需要对单个表列做额外处理的场景，但性能略低于方案一。

注意事项 #

1. 执行脚本需要具备对应表的SELECT权限，否则会出现权限不足的错误。
2. 如果数据库中存在大量表和列，两种方案都会有一定性能开销，可通过WHERE子句过滤不需要的表（如系统表）：

   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')
   

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