求SQL实例中含_BKP表的数据库及表信息查询SQL代码

以下SQL脚本可遍历你的SQL实例中所有在线数据库，找出所有以_BKP结尾的备份表，同时返回这些表的记录数、所属数据库的总大小（含数据和日志文件），帮你定位冗余备份表以释放磁盘空间：

SET NOCOUNT ON;

-- 临时表存储结果
CREATE TABLE #BackupTables (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128),
    RecordCount BIGINT,
    DatabaseSizeMB DECIMAL(18,2)
);

-- 动态SQL遍历所有在线数据库
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases 
WHERE state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成查询当前数据库中_BKP表及记录数的SQL
    SET @SQL = N'
    USE [' + @DBName + N'];
    INSERT INTO #BackupTables (DatabaseName, TableName, RecordCount)
    SELECT 
        ''' + @DBName + N''' AS DatabaseName,
        t.name AS TableName,
        SUM(p.rows) AS RecordCount
    FROM sys.tables t
    JOIN sys.indexes i ON t.object_id = i.object_id
    JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    WHERE t.name LIKE ''%[_]BKP''
        AND i.index_id IN (0, 1) -- 仅统计堆表或聚集索引，避免重复计数
    GROUP BY t.name;
    ';

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

-- 更新数据库大小信息
UPDATE #BackupTables
SET DatabaseSizeMB = ds.SizeMB
FROM #BackupTables bt
JOIN (
    SELECT 
        d.name AS DatabaseName,
        CAST(SUM(mf.size) * 8 / 1024.0 AS DECIMAL(18,2)) AS SizeMB
    FROM sys.databases d
    JOIN sys.master_files mf ON d.database_id = mf.database_id
    GROUP BY d.name
) ds ON bt.DatabaseName = ds.DatabaseName;

-- 输出最终结果
SELECT 
    DatabaseName,
    TableName,
    RecordCount,
    DatabaseSizeMB
FROM #BackupTables
ORDER BY DatabaseName, TableName;

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

关键说明 #

• 记录数计算：脚本通过系统视图获取近似记录数，执行效率较高；如果需要精确统计，可将SUM(p.rows)替换为(SELECT COUNT(*) FROM [' + @DBName + N'].[' + SCHEMA_NAME(t.schema_id) + N'].[' + t.name + N'])，但会显著增加执行时间（尤其是大表场景）。
• 权限要求：执行脚本需要VIEW SERVER STATE权限，以及对所有目标数据库的SELECT权限。
• 筛选逻辑：仅处理状态为ONLINE的数据库，避免访问离线或恢复中的库。

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