如何为跨库动态SQL查询创建临时表存储执行结果？

先修正原代码的基础错误 #

你的代码里@count变量仅声明未初始化，初始值为NULL，导致while (@count is not null and @count <= @maxx)条件直接不成立，循环根本不会执行。第一步要给@count赋初始值@minx。

创建临时表存储结果 #

提前创建与动态SQL返回结果结构匹配的临时表：

CREATE TABLE #UserTables (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128)
)

调整动态SQL的执行方式 #

不能直接用exec (@sql)，要改用INSERT INTO #UserTables EXEC (@sql)将动态SQL的结果插入临时表。同时注意字符串拼接的类型一致性，推荐用QUOTENAME()包裹数据库名，避免特殊字符导致语法错误。

修正后的完整代码 #

-- 创建存储结果的临时表
CREATE TABLE #UserTables (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128)
)

declare @minx int = 0
declare @maxx int = (select max(id) from #DBS)
declare @sql nvarchar(1000)
declare @dbname varchar (130)
declare @count int = @minx -- 初始化循环变量

-- 遍历#DBS中的数据库
while (@count <= @maxx)
begin
    select @dbname = dbname
    from #DBS where id = @count

    if @dbname is not null -- 规避空数据库名的情况
    begin
        print 'id = ' + convert (varchar, @count) + ' dbname = ' + @dbname
        -- 构造带插入逻辑的动态SQL
        set @sql = N'USE ' + QUOTENAME(@dbname) + N'; 
                    INSERT INTO #UserTables (DatabaseName, TableName)
                    SELECT DB_NAME(), name 
                    FROM sys.tables 
                    WHERE is_ms_shipped = 0 
                      AND type_desc = ''USER_TABLE'''
        exec sp_executesql @sql -- 用sp_executesql替代exec，更安全规范
    end

    set @count = @count + 1
end;

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

-- 可选：手动清理临时表（会话结束会自动删除）
DROP TABLE #UserTables

关键要点说明 #

• QUOTENAME(@dbname)：包裹数据库名，避免数据库名含空格、特殊符号时触发语法错误。
• sp_executesql：比直接exec更安全，支持参数化，是执行动态SQL的推荐方式。
• 新增if @dbname is not null判断：防止#DBS中存在空数据库名导致执行失败。
• 移除原代码的break：否则循环仅执行一次，无法遍历所有目标数据库。

内容的提问来源于stack exchange，提问作者Trying to be DBA