基于SSMS 18修改SQL查询，获取服务器库表视图及统计信息

需求说明 #

需要修改现有SQL查询，使其返回服务器上每个数据库的所有表和视图，同时包含每个对象的行数、列数，并新增列标识对象类型（表/视图）。期望返回列如下：

ServerName ; DBName ; SchemaName ; Object (View or Table) ; ObjectName ; RowCount ; ColumnCount

现有代码仅能查询表，未包含视图，也无行数、列数统计。

修改后的完整SQL脚本 #

SET NOCOUNT ON

DECLARE @AllObjects TABLE
(
    ServerName NVARCHAR(200),
    DBName NVARCHAR(200),
    SchemaName NVARCHAR(200),
    ObjectType NVARCHAR(20), -- 标识是表还是视图
    ObjectName NVARCHAR(200),
    RowCount BIGINT,
    ColumnCount INT
)
DECLARE @SearchSvr NVARCHAR(200),
        @SearchDB NVARCHAR(200),
        @SearchSchema NVARCHAR(200),
        @SearchObj NVARCHAR(200),
        @SQL NVARCHAR(MAX)

-- 筛选条件：NULL表示查询全部
SET @SearchSvr = NULL      -- 筛选服务器名，NULL为所有服务器
SET @SearchDB = NULL       -- 筛选数据库名，NULL为所有数据库
SET @SearchSchema = NULL   -- 筛选架构名，NULL为所有架构
SET @SearchObj = NULL      -- 筛选对象名（表/视图），NULL为所有对象

SET @SQL = '
SELECT 
    @@SERVERNAME AS ServerName,
    ''?'' AS DBName,
    s.name AS SchemaName,
    CASE 
        WHEN t.object_id IS NOT NULL THEN ''Table'' 
        ELSE ''View'' 
    END AS ObjectType,
    COALESCE(t.name, v.name) AS ObjectName,
    -- 统计行数：仅表有实际行数，视图返回NULL
    CASE 
        WHEN t.object_id IS NOT NULL THEN p.rows 
        ELSE NULL 
    END AS RowCount,
    -- 统计列数
    c.ColumnCount
FROM 
    -- 联合表和视图的查询
    (SELECT object_id, name FROM [?].sys.tables 
     UNION ALL 
     SELECT object_id, name FROM [?].sys.views) AS obj
LEFT JOIN [?].sys.tables t ON obj.object_id = t.object_id
LEFT JOIN [?].sys.views v ON obj.object_id = v.object_id
JOIN [?].sys.schemas s ON obj.object_id = s.schema_id
-- 关联分区统计获取行数
LEFT JOIN (
    SELECT object_id, SUM(rows) AS rows 
    FROM [?].sys.dm_db_partition_stats 
    WHERE index_id IN (0,1) -- 堆或聚集索引的行数
    GROUP BY object_id
) p ON obj.object_id = p.object_id
-- 关联列统计
JOIN (
    SELECT object_id, COUNT(*) AS ColumnCount 
    FROM [?].sys.columns 
    GROUP BY object_id
) c ON obj.object_id = c.object_id
WHERE 
    @@SERVERNAME LIKE ''%' + ISNULL(@SearchSvr, '') + '%''
    AND ''?'' LIKE ''%' + ISNULL(@SearchDB, '') + '%''
    AND s.name LIKE ''%' + ISNULL(@SearchSchema, '') + '%''
    AND COALESCE(t.name, v.name) LIKE ''%' + ISNULL(@SearchObj, '') + '%''
    -- 取消注释下方行以排除系统数据库
    -- AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'',''SSISDB'')
'

INSERT INTO @AllObjects
(
    ServerName,
    DBName,
    SchemaName,
    ObjectType,
    ObjectName,
    RowCount,
    ColumnCount
)
EXEC sp_MSforeachdb @SQL

SET NOCOUNT OFF
SELECT 
    ServerName,
    DBName,
    SchemaName,
    ObjectType AS [Object (View or Table)],
    ObjectName,
    RowCount,
    ColumnCount
FROM @AllObjects
ORDER BY ServerName, DBName, SchemaName, ObjectType, ObjectName

关键修改说明 #

• 合并表与视图查询：通过UNION ALL联合sys.tables和sys.views，用CASE生成ObjectType列区分表和视图
• 行数统计：利用sys.dm_db_partition_stats汇总表的堆或聚集索引行数，视图因不存储数据返回NULL
• 列数统计：通过sys.columns按对象分组统计列数
• 保留筛选逻辑：原有的服务器、数据库、架构、对象名模糊筛选规则完全保留，新增排除系统库的可选配置
• 临时表结构适配：扩展临时表字段，新增对象类型、行数、列数字段，匹配最终输出需求

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