使用sp_spaceused查询临时表空间并插入另一临时表报错如何解决

错误根因 #

sp_spaceused 默认查找当前业务数据库（示例报错中为d1库）下的对象，而#开头的本地临时表实际存储在系统库tempdb中，直接传入临时表名作为参数时，存储过程会在当前业务库检索对象，找不到对应表就会抛出15009错误。

解决方法 #

调用sp_spaceused时，明确指定@objname参数，并给临时表名加上tempdb..前缀，标识该对象在tempdb库中即可，修正后的插入语句如下：

INSERT INTO #FileSize EXEC sp_spaceused @objname = N'tempdb..#Found_Subscriber'
INSERT INTO #FileSize EXEC sp_spaceused @objname = N'tempdb..#Found_SubscriberInfo'
-- 其余需要统计的临时表都按上述格式编写即可

批量统计优化方案 #

因为你需要统计100张临时表，手动写100条插入语句效率太低，可以用游标循环批量处理：

-- 1. 先维护需要统计的临时表名清单
DECLARE @TempTableList TABLE (TableName NVARCHAR(128))
INSERT INTO @TempTableList (TableName)
VALUES 
(N'#Found_Subscriber'),
(N'#Found_SubscriberInfo')
-- 剩余98张临时表名按上述格式追加即可

-- 2. 循环执行统计插入
DECLARE @CurrentTableName NVARCHAR(128), @ExecSql NVARCHAR(MAX)
DECLARE stat_cursor CURSOR FOR 
SELECT TableName FROM @TempTableList

OPEN stat_cursor
FETCH NEXT FROM stat_cursor INTO @CurrentTableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @ExecSql = N'INSERT INTO #FileSize EXEC sp_spaceused @objname = N''tempdb..' + @CurrentTableName + ''''
    EXEC sp_executesql @ExecSql
    FETCH NEXT FROM stat_cursor INTO @CurrentTableName
END

CLOSE stat_cursor
DEALLOCATE stat_cursor

验证 #

所有表统计完成后，直接查询#FileSize表即可看到所有临时表的空间占用、行数等数据：

SELECT * FROM #FileSize

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