如何遍历数据库列表执行表行数列数统计并合并结果？

现有SQL代码可统计单个数据库（如db1）中各用户表的行数与列数，但处理多个数据库时，手动修改USE语句效率极低。可通过游标遍历指定数据库列表（通过select name from master.dbo.sysdatabases where name like '%db%'获取），结合动态SQL实现批量统计并合并所有结果，具体方案如下：

完整实现代码 #

-- 创建临时表存储所有数据库的统计结果
CREATE TABLE #TableStats (
    DB_Name NVARCHAR(128),
    TableName NVARCHAR(256),
    RowCount BIGINT,
    ColumnCount INT
)

-- 声明游标遍历目标数据库
DECLARE @DBName NVARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name LIKE '%db%' -- 筛选需统计的数据库

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 动态拼接SQL，针对当前数据库执行统计逻辑
    DECLARE @DynamicSQL NVARCHAR(MAX) = N'
    USE [' + @DBName + N']
    ;WITH [rowCount] AS
    (
        SELECT  DB_NAME() AS [DB_Name],
                QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + N''.'' + QUOTENAME(sOBJ.name) AS [TableName],
                SUM(sPTN.Rows) AS [RowCount]
        FROM    SYS.OBJECTS AS sOBJ
        INNER JOIN SYS.PARTITIONS AS sPTN
            ON sOBJ.object_id = sPTN.object_id
        WHERE
            sOBJ.type = ''U''
            AND sOBJ.is_ms_shipped = 0x0
            AND index_id < 2 -- 0:堆表, 1:聚集索引表
        GROUP BY      
            sOBJ.schema_id
            ,sOBJ.name
    ),
    columnCount AS
    (
        SELECT 
            QUOTENAME(col.TABLE_SCHEMA) + N''.'' + QUOTENAME(col.TABLE_NAME) AS [TableName],
            COUNT(*) AS ColumnCount
        FROM INFORMATION_SCHEMA.COLUMNS col
        INNER JOIN INFORMATION_SCHEMA.TABLES tbl
            ON col.TABLE_SCHEMA = tbl.TABLE_SCHEMA
            AND col.TABLE_NAME = tbl.TABLE_NAME
            AND tbl.TABLE_TYPE <> ''VIEW''
        GROUP BY 
            QUOTENAME(col.TABLE_SCHEMA) + N''.'' + QUOTENAME(col.TABLE_NAME)
    )
    INSERT INTO #TableStats (DB_Name, TableName, RowCount, ColumnCount)
    SELECT r.[DB_Name], r.TableName, r.[RowCount], c.ColumnCount
    FROM [rowCount] r
    INNER JOIN columnCount c
        ON r.TableName = c.TableName
    '

    -- 执行动态SQL
    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM db_cursor INTO @DBName
END

-- 关闭并释放游标
CLOSE db_cursor
DEALLOCATE db_cursor

-- 查询所有数据库的统计结果
SELECT * FROM #TableStats ORDER BY DB_Name, TableName

-- 删除临时表
DROP TABLE #TableStats

关键逻辑说明 #

• 临时表#TableStats：统一存储所有数据库的统计数据，避免结果分散。
• 游标遍历：自动获取目标数据库列表，逐个处理无需手动修改USE语句。
• 动态SQL：根据当前数据库名称拼接执行逻辑，确保统计语句针对正确的数据库上下文。
• sp_executesql：安全执行动态生成的SQL，避免注入风险同时保证语法正确性。

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