如何按日期统计所有表列数及在SQL查询中新增近一年记录数统计列

首先得明确你要的「按日期统计」具体指什么——是按表的创建日期展示每个表的列数，还是按列的修改日期统计每天新增/修改的列数？我给你两种常见场景的实现：

场景1：按表的创建日期，统计每个表的列数 #

这个查询会关联系统视图sys.columns和sys.tables，统计每个表的列总数，同时带上表的创建日期：

SELECT 
    SCHEMA_NAME(t.schema_id) AS [Schema],
    OBJECT_NAME(c.object_id) AS [TableName],
    COUNT(c.column_id) AS [ColumnCount],
    t.create_date AS [TableCreateDate]
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
GROUP BY c.object_id, t.schema_id, t.create_date
ORDER BY t.create_date DESC;

场景2：按日期维度，统计每天新增/修改的列数 #

如果是想知道每天有多少列被添加或修改，我们可以用sys.columns的modify_date字段来分组统计：

SELECT 
    CONVERT(date, c.modify_date) AS [ModifyDate],
    COUNT(c.column_id) AS [AddedOrModifiedColumnCount]
FROM sys.columns c
GROUP BY CONVERT(date, c.modify_date)
ORDER BY [ModifyDate] DESC;

你的现有查询是通过sys.partitions快速获取表的总行数，但要统计近一年的记录数，这里有个关键问题：sys.partitions只存储了表的总行数统计，没有按DateInsert过滤的细分数据，所以必须实际查询每个表的记录来统计符合条件的数量。

我给你两种方案，分别适配不同情况：

方案1：假设所有表都包含DateInsert列 #

用动态SQL自动遍历所有表，生成统计语句：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
SELECT 
    SCHEMA_NAME(t.schema_id) AS [SCHEMA],
    OBJECT_NAME(t.object_id) AS [NOME_TABELA],
    (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.object_id = t.object_id AND p.index_id < 2) AS [ROW_COUNT],
    COUNT(*) AS [ROW_COUNT_LAST_YEAR]
FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)) + '
WHERE DateInsert >= DATEADD(year, -1, GETDATE())'
FROM sys.tables t;

-- 去掉开头多余的UNION ALL
SET @sql = STUFF(@sql, 1, 10, N'');

-- 添加排序逻辑
SET @sql += N' ORDER BY [ROW_COUNT] DESC';

EXEC sp_executesql @sql;

方案2：兼容部分表没有DateInsert列的情况 #

如果有些表没有DateInsert列，我们可以先检查表结构，对这类表返回NULL（你也可以改成0）：

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
UNION ALL
SELECT 
    SCHEMA_NAME(t.schema_id) AS [SCHEMA],
    OBJECT_NAME(t.object_id) AS [NOME_TABELA],
    (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.object_id = t.object_id AND p.index_id < 2) AS [ROW_COUNT],
    ' + CASE WHEN EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND c.name = 'DateInsert') 
             THEN N'COUNT(*) AS [ROW_COUNT_LAST_YEAR]'
             ELSE N'NULL AS [ROW_COUNT_LAST_YEAR]'
        END + N'
FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)) + '
' + CASE WHEN EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_id AND c.name = 'DateInsert') 
         THEN N'WHERE DateInsert >= DATEADD(year, -1, GETDATE())'
         ELSE N''
    END
FROM sys.tables t;

SET @sql = STUFF(@sql, 1, 10, N'');
SET @sql += N' ORDER BY [ROW_COUNT] DESC';

EXEC sp_executesql @sql;

简单说下原理：动态SQL会遍历所有用户表，为每个表生成一行统计数据，其中总行数还是用你原来的sys.partitions方式获取（效率高），近一年记录数则直接查询表中符合DateInsert条件的行数。

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