SQL Server中统计每个表行数的快速方法

在SQL Server中，使用系统视图和查询，如sys.partitions和COUNT_BIG(*)，可以快速统计每个表的行数，提高效率。

SQL Server高效统计每个表行数的实用指南

技术内容：

在SQL Server数据库管理过程中，我们经常需要了解每个表的行数，这有助于我们评估数据库性能、监控数据增长以及执行其他管理任务，本文将介绍几种在SQL Server中统计每个表行数的快速方法。

方法一：使用COUNT(*)和 INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.TABLES视图包含了关于数据库中所有表的信息，包括它们的行数，这个视图的TABLE_ROWS列可能不会实时反映实际行数，因为它是一个近似值，如果你需要更精确的行数，可以结合使用COUNT(*)和INFORMATION_SCHEMA.TABLES。

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    i.TABLE_ROWS AS ApproximateRowCount,
    (SELECT COUNT(*) FROM t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS ExactRowCount
FROM 
    INFORMATION_SCHEMA.TABLES t
INNER JOIN 
    sysindexes i ON t.TABLE_NAME = i.name
WHERE 
    t.TABLE_TYPE = 'BASE TABLE'
    AND i.id = OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME);

注意：这种方法在大型数据集上可能会非常慢，因为它对每个表执行了一个全表扫描。

方法二：使用系统视图sys.partitions

sys.partitions系统视图可以提供关于表分区的信息，包括每个分区的行数，如果表没有分区，该视图将显示整个表的行数。

SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS TotalRows
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
JOIN 
    sys.partitions p ON t.object_id = p.object_id
WHERE 
    t.type = 'U'
GROUP BY 
    s.name, t.name;

这种方法比使用COUNT(*)快得多，因为它不需要对表进行全表扫描。

方法三：使用sp_MSforeachtable

sp_MSforeachtable是SQL Server的一个系统存储过程，它可以遍历数据库中的所有表，并对每个表执行指定的查询。

DECLARE @Sql NVARCHAR(MAX) = N'';
SELECT @Sql = @Sql + N'SELECT ''' + s.name + N'.' + t.name + N''', COUNT(*) FROM ' + s.name + N'.' + t.name + N'; '
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.type = 'U';
EXEC sp_executesql @Sql;

这种方法可以将所有表的结果集合并到一个查询中，但在大型数据库上可能会遇到性能瓶颈。

方法四：动态SQL

你可以使用动态SQL来创建一个执行所有表计数并返回结果的脚本。

DECLARE @TableName NVARCHAR(256);
DECLARE @SchemaName NVARCHAR(256);
DECLARE @Sql NVARCHAR(MAX) = N'';
DECLARE @ParmDefinition NVARCHAR(255) = N'@TableName NVARCHAR(256), @SchemaName NVARCHAR(256)';
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT 
    t.name, s.name
FROM 
    sys.tables t
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.type = 'U';
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName, @SchemaName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Sql = N'SELECT @TableName AS TableName, @SchemaName AS SchemaName, COUNT(*) AS RowCount FROM ' + @SchemaName + N'.' + @TableName;
    EXEC sp_executesql 
        @Sql, 
        @ParmDefinition, 
        @TableName = @TableName, 
        @SchemaName = @SchemaName;
    FETCH NEXT FROM TableCursor INTO @TableName, @SchemaName;
END
CLOSE TableCursor;
DEALLOCATE TableCursor;

这种方法使用游标遍历所有表，并使用参数化查询来避免SQL注入。

性能考虑

1、在统计大型数据库时，请考虑索引维护、备份和其他操作的影响。

2、在生产环境中，应避免在高峰时段执行全表扫描。

3、对于包含大量数据的表，可以考虑使用SAMPLE子句进行近似计数。

4、定期更新统计信息可以提高查询计划的准确性和性能。

结论

在SQL Server中统计每个表的行数有多种方法，你可以根据具体需求和数据库环境选择最合适的方法，在执行这些操作时，请务必考虑性能和资源使用情况，避免对生产环境造成不良影响。