多数据库系统数据拉取：源表健康统计高效获取方案咨询

嘿，针对你这个用扩展属性记录存储过程源表、要批量获取表统计信息的需求，我整理了几个比动态SQL更优雅、高效的方案，能避免动态SQL的潜在注入风险和维护成本：

这个方案完全不用动态SQL，通过拆分扩展属性里的源表列表，直接关联SQL Server的系统视图来获取统计数据，性能和可维护性都很强。

假设你的扩展属性名称是SourceTables，值是逗号分隔的表名（比如dbo.Table1, Sales.Table2），可以用下面的SQL：

WITH ProcSourceTables AS (
    SELECT 
        OBJECT_NAME(ep.major_id) AS ProcedureName,
        -- 拆分扩展属性里的表名列表
        TRIM(st.value) AS SourceTableName,
        -- 提取schema和表名（支持带schema的表名）
        PARSENAME(TRIM(st.value), 2) AS SchemaName,
        PARSENAME(TRIM(st.value), 1) AS TableName
    FROM sys.extended_properties ep
    CROSS APPLY STRING_SPLIT(ep.value, ',') AS st
    WHERE ep.name = 'SourceTables' -- 替换成你的扩展属性名称
        AND ep.minor_id = 0 -- 存储过程的扩展属性minor_id通常为0
)
SELECT 
    pst.ProcedureName,
    pst.SourceTableName,
    t.create_date AS 表创建时间,
    t.modify_date AS 最后更新时间,
    SUM(ps.row_count) AS 总行数,
    -- 计算表占用空间（单位MB）
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS 表大小MB
FROM ProcSourceTables pst
JOIN sys.tables t 
    ON t.name = pst.TableName 
    AND (pst.SchemaName IS NULL OR t.schema_id = SCHEMA_ID(pst.SchemaName))
JOIN sys.dm_db_partition_stats ps 
    ON ps.object_id = t.object_id 
    AND ps.index_id IN (0,1) -- 只统计堆或聚集索引，避免重复计数
GROUP BY pst.ProcedureName, pst.SourceTableName, t.create_date, t.modify_date
ORDER BY pst.ProcedureName, pst.SourceTableName;

优势：纯集合操作，性能优于动态SQL，代码可读性高，不需要拼接字符串，完全规避动态SQL的风险。

如果后续需要重复使用表统计的逻辑，或者要调整统计指标（比如加索引大小），可以把统计逻辑封装成表值函数，让主查询更简洁：

先创建函数：

CREATE FUNCTION dbo.GetTableBasicStats(
    @SchemaName NVARCHAR(128) = 'dbo',
    @TableName NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN (
    SELECT 
        t.create_date AS 表创建时间,
        t.modify_date AS 最后更新时间,
        SUM(ps.row_count) AS 总行数,
        CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(10,2)) AS 表大小MB
    FROM sys.tables t
    JOIN sys.dm_db_partition_stats ps 
        ON ps.object_id = t.object_id 
        AND ps.index_id IN (0,1)
    WHERE t.name = @TableName 
        AND t.schema_id = SCHEMA_ID(@SchemaName)
    GROUP BY t.create_date, t.modify_date
);

然后调用函数获取数据：

SELECT 
    OBJECT_NAME(ep.major_id) AS ProcedureName,
    TRIM(st.value) AS SourceTableName,
    ts.*
FROM sys.extended_properties ep
CROSS APPLY STRING_SPLIT(ep.value, ',') AS st
CROSS APPLY dbo.GetTableBasicStats(
    ISNULL(PARSENAME(TRIM(st.value), 2), 'dbo'),
    PARSENAME(TRIM(st.value), 1)
) AS ts
WHERE ep.name = 'SourceTables'
    AND ep.minor_id = 0;

优势：统计逻辑模块化，后续修改指标只需要更新函数，主查询无需改动，代码复用性强。

如果你的源表格式不统一（比如带数据库名、链接服务器），或者需要定期生成健康报告文件，PowerShell是个不错的选择，灵活性更高：

# 配置数据库连接
$serverName = "你的数据库服务器名"
$dbName = "目标数据库名"
$connString = "Server=$serverName;Database=$dbName;Integrated Security=True;"
$conn = New-Object System.Data.SqlClient.SqlConnection($connString)
$conn.Open()

# 获取所有带源表扩展属性的存储过程
$procQuery = @"
SELECT OBJECT_NAME(major_id) AS ProcedureName, value AS SourceTables
FROM sys.extended_properties
WHERE name = 'SourceTables' AND minor_id = 0
"@
$procCmd = New-Object System.Data.SqlClient.SqlCommand($procQuery, $conn)
$procReader = $procCmd.ExecuteReader()

# 存储结果
$reportData = @()

while ($procReader.Read()) {
    $procName = $procReader["ProcedureName"]
    # 拆分源表列表并去除空格
    $sourceTables = $procReader["SourceTables"].Split(',') | ForEach-Object { $_.Trim() }
    
    foreach ($table in $sourceTables) {
        # 处理表名：拆分schema和表名，默认dbo
        if ($table.Contains('.')) {
            $schema, $tableName = $table.Split('.')
        } else {
            $schema = 'dbo'
            $tableName = $table
        }
        
        # 查询表统计信息
        $statsQuery = @"
        SELECT 
            '$procName' AS 存储过程名,
            '$table' AS 源表名,
            CONVERT(VARCHAR(20), create_date, 120) AS 表创建时间,
            CONVERT(VARCHAR(20), modify_date, 120) AS 最后更新时间,
            SUM(row_count) AS 总行数,
            CAST(SUM(reserved_page_count)*8.0/1024 AS DECIMAL(10,2)) AS 表大小MB
        FROM sys.tables t
        JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id AND ps.index_id IN (0,1)
        WHERE t.name = '$tableName' AND SCHEMA_NAME(t.schema_id) = '$schema'
        GROUP BY create_date, modify_date
"@
        $statsCmd = New-Object System.Data.SqlClient.SqlCommand($statsQuery, $conn)
        $statsReader = $statsCmd.ExecuteReader()
        
        if ($statsReader.Read()) {
            $reportRow = [PSCustomObject]@{
                存储过程名 = $statsReader["存储过程名"]
                源表名 = $statsReader["源表名"]
                表创建时间 = $statsReader["表创建时间"]
                最后更新时间 = $statsReader["最后更新时间"]
                总行数 = $statsReader["总行数"]
                表大小MB = $statsReader["表大小MB"]
            }
            $reportData += $reportRow
        }
        $statsReader.Close()
    }
}

# 关闭连接
$procReader.Close()
$conn.Close()

# 导出到CSV报告
$reportData | Export-Csv -Path "数据库健康报告_$(Get-Date -Format 'yyyyMMdd').csv" -NoTypeInformation -Encoding UTF8
Write-Host "报告已生成：$((Get-Location).Path)\数据库健康报告_$(Get-Date -Format 'yyyyMMdd').csv"

优势：能处理复杂的表名格式，支持自动化定时执行，直接生成可阅读的CSV报告，不需要在数据库中维护复杂SQL。

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