基于通用ID的SQL Server数据库各表元数据统计需求咨询

针对SQL Server 2017及以上版本，要统计全库含customerId字段的表中，每个ID对应的行数和存储空间，并构建周/月趋势报表，以下是直接可行的实现方案：

一、遍历表统计核心数据 #

你可以通过系统视图+动态SQL实现自动遍历符合条件的表，同时完成行数和存储空间的统计，比手动编写单表查询高效得多。

1. 通用统计存储过程 #

创建一个存储过程，自动筛选含目标字段的表，并输出统计结果：

CREATE PROCEDURE dbo.GetCustomerIdStats
    @TargetColumn NVARCHAR(128) = 'customerId'
AS
BEGIN
    SET NOCOUNT ON;

    -- 临时表暂存统计结果
    CREATE TABLE #TempStats (
        SchemaName NVARCHAR(128),
        TableName NVARCHAR(128),
        CustomerId BIGINT,
        RowCount INT,
        TotalSpaceKB DECIMAL(18,2)
    );

    -- 拼接动态SQL，遍历所有含目标字段的表
    DECLARE @DynamicSQL NVARCHAR(MAX) = N'';
    SELECT @DynamicSQL += N'
    INSERT INTO #TempStats
    SELECT 
        ''' + s.name + ''',
        ''' + t.name + ''',
        ' + QUOTENAME(c.name) + ',
        COUNT(*),
        -- 按行计算近似存储空间（含所有字段+行开销）
        SUM(DATALENGTH(' + QUOTENAME(c.name) + ') + (SELECT SUM(DATALENGTH(col)) FROM sys.columns col WHERE col.object_id = t.object_id AND col.name != ''' + c.name + ''') + 4) / 1024.0
    FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
    GROUP BY ' + QUOTENAME(c.name) + ';'
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    JOIN sys.columns c ON t.object_id = c.object_id
    WHERE c.name = @TargetColumn;

    -- 执行动态SQL
    EXEC sp_executesql @DynamicSQL;

    -- 返回最终统计结果
    SELECT * FROM #TempStats;

    DROP TABLE #TempStats;
END;

2. 存储空间统计的两种方式 #

• 近似统计：上面的存储过程用DATALENGTH()逐行计算每个字段的字节数，加上4字节的行头开销，得到单条记录的近似空间，再按customerId求和。这种方式适合快速获取相对准确的数据。
• 精确统计（含索引）：如果需要包含索引在内的总占用空间，可以结合sys.dm_db_partition_stats调整动态SQL片段：

SELECT 
    ''' + s.name + ''',
    ''' + t.name + ''',
    ' + QUOTENAME(c.name) + ',
    COUNT(*),
    SUM(ps.reserved_page_count) * 8 -- 每页8KB，转换为KB
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
JOIN sys.dm_db_partition_stats ps ON t.object_id = ps.object_id AND ps.index_id IN (0,1) -- 仅统计堆或聚集索引
GROUP BY ' + QUOTENAME(c.name) + ';'

注意：sys.dm_db_partition_stats统计的是整个数据页/索引页的空间，按customerId分组的结果是近似值，因为一个页可能包含多个客户的数据。如果要绝对精确到单客户，还是用逐行计算的方式。

二、构建周期性趋势报表 #

1. 创建报表存储表 #

先建一张表来保存周/月的统计历史：

CREATE TABLE dbo.CustomerUsageTrends (
    TrendDate DATE NOT NULL,
    SchemaName NVARCHAR(128) NOT NULL,
    TableName NVARCHAR(128) NOT NULL,
    CustomerId BIGINT NOT NULL,
    RowCount INT NOT NULL,
    TotalSpaceKB DECIMAL(18,2) NOT NULL,
    StatPeriod NVARCHAR(10) NOT NULL CHECK (StatPeriod IN ('Week', 'Month')),
    PRIMARY KEY (TrendDate, SchemaName, TableName, CustomerId, StatPeriod)
);

2. 定期执行统计（用SQL Server代理） #

创建SQL Server代理作业，在每周/每月的业务低峰期执行以下脚本，把统计结果写入报表表：

-- 每周统计（以本周一为统计日期）
INSERT INTO dbo.CustomerUsageTrends
SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0),
    SchemaName,
    TableName,
    CustomerId,
    RowCount,
    TotalSpaceKB,
    'Week'
FROM dbo.GetCustomerIdStats('customerId');

-- 每月统计（以当月第一天为统计日期）
INSERT INTO dbo.CustomerUsageTrends
SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
    SchemaName,
    TableName,
    CustomerId,
    RowCount,
    TotalSpaceKB,
    'Month'
FROM dbo.GetCustomerIdStats('customerId');

三、注意事项 #

• 执行存储过程需要对所有目标表有SELECT权限，以及执行动态SQL的权限。
• 大表统计会消耗服务器资源，务必在业务低峰期运行。
• 存储过程会自动识别新增的含customerId的表，无需修改代码。

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