如何在SQL语句中正确构建循环遍历表变量以获取表列信息

你的代码核心问题在于：@myTableVariable 仅存储了数据库名称列表，不能直接通过它访问 information_schema.columns——这个视图属于每个独立的数据库，需要针对每个数据库单独查询。下面是用WHILE循环实现的可行方案：

完整代码 #

-- 创建带序号的表变量，用于遍历数据库
DECLARE @DBList TABLE (ID INT IDENTITY(1,1), DBName VARCHAR(30))
INSERT INTO @DBList SELECT [NAME] FROM SYS.DATABASES WHERE state_desc = 'ONLINE' -- 仅处理在线数据库

-- 初始化循环变量
DECLARE @CurrentID INT = 1
DECLARE @TotalDBs INT = (SELECT COUNT(*) FROM @DBList)
DECLARE @CurrentDB VARCHAR(30)
DECLARE @SQL NVARCHAR(MAX)

-- 创建临时表存储最终结果
CREATE TABLE #Result (
    [DATABASE] VARCHAR(30),
    TABLE_NAME VARCHAR(128),
    COLUMN_NAME VARCHAR(128),
    DATA_TYPE VARCHAR(128),
    CHARACTER_MAXIMUM_LENGTH INT
)

-- WHILE循环遍历每个数据库
WHILE @CurrentID <= @TotalDBs
BEGIN
    -- 获取当前要处理的数据库名
    SELECT @CurrentDB = DBName FROM @DBList WHERE ID = @CurrentID

    -- 构造动态SQL，查询目标数据库的列信息
    SET @SQL = N'
        INSERT INTO #Result
        SELECT
            ''' + @CurrentDB + ''' AS [DATABASE],
            TABLE_NAME,
            COLUMN_NAME,
            DATA_TYPE,
            CHARACTER_MAXIMUM_LENGTH
        FROM ' + QUOTENAME(@CurrentDB) + '.INFORMATION_SCHEMA.COLUMNS'

    -- 执行动态SQL
    EXEC sp_executesql @SQL

    -- 自增循环变量
    SET @CurrentID = @CurrentID + 1
END

-- 查询最终结果
SELECT * FROM #Result

-- 清理临时表
DROP TABLE #Result

关键说明 #

• 用QUOTENAME(@CurrentDB)处理数据库名（比如包含特殊字符或空格的情况），避免SQL注入风险
• 新增临时表#Result统一存储所有数据库的列信息，方便最终输出
• 过滤了state_desc = 'ONLINE'的数据库，避免访问离线库报错
• 动态SQL是唯一能跨数据库查询系统视图的方式，因为information_schema是数据库级别的对象

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