sql server 查询所有表的所有行数

原创

mob64ca12dd455e 2023-09-29 03:50:16 ©著作权

文章标签 SQL sql Server 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob64ca12dd455e的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 查询所有表的所有行数

在SQL Server数据库中，我们经常需要查询表的总行数，以便了解表中的数据量。本文将介绍如何使用SQL Server查询所有表的所有行数，并提供相关的代码示例。

1. 使用系统存储过程 `sp_MSforeachtable`

SQL Server提供了一个系统存储过程 sp_MSforeachtable，可以用来循环遍历数据库中的每个表。我们可以利用这个存储过程来查询每个表的行数。

下面是一个使用 sp_MSforeachtable 查询所有表行数的示例代码：

EXEC sp_MSforeachtable '
    SELECT ''?'' AS TableName, COUNT(*) AS RowCount FROM ?
'

在上述示例代码中，? 是一个占位符，表示表名。sp_MSforeachtable 会将每个表名替换到 ? 的位置上，并执行相应的查询。

2. 使用临时表和动态SQL语句

另一种查询所有表行数的方法是使用临时表和动态SQL语句。这种方法需要创建一个临时表来存储每个表的行数，并使用动态SQL语句来查询每个表的行数。

下面是一个使用临时表和动态SQL语句查询所有表行数的示例代码：

-- 创建临时表
CREATE TABLE #RowCounts (
    TableName NVARCHAR(128),
    RowCount INT
)

-- 创建动态SQL语句
DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'INSERT INTO #RowCounts (TableName, RowCount) SELECT ''' + name + ''', COUNT(*) FROM ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables

-- 执行动态SQL语句
EXEC sp_executesql @sql

-- 查询临时表中的数据
SELECT * FROM #RowCounts

-- 删除临时表
DROP TABLE #RowCounts

在上述示例代码中，我们首先创建了一个临时表 #RowCounts，用于存储每个表的行数。然后使用动态SQL语句生成了一段插入语句，将每个表的行数插入到临时表中。最后，查询临时表中的数据，并删除临时表。

3. 使用递归查询

还有一种方法是使用递归查询，通过查询系统表来获取数据库中所有表的行数。这种方法不需要创建临时表，直接查询系统表即可。

下面是一个使用递归查询查询所有表行数的示例代码：

WITH TableRowCounts AS (
    SELECT 
        QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
        SUM(p.rows) AS RowCount
    FROM sys.tables t
    INNER JOIN sys.partitions p ON p.object_id = t.object_id
    WHERE p.index_id < 2
    GROUP BY t.schema_id, t.name
)
SELECT * FROM TableRowCounts

在上述示例代码中，我们使用了 sys.tables 和 sys.partitions 系统表来获取每个表的行数。sys.tables 存储了数据库中所有表的信息，sys.partitions 存储了每个表的分区信息。我们使用 INNER JOIN 将这两个表关联起来，并根据 index_id 条件来获取表的行数。最后，使用 GROUP BY 和 SUM 函数计算每个表的总行数。