如何获取 SQL SERVER 数据库中所有表的行数

Question

我正在寻找一个可以用来确定给定数据库中是否有任何数据（即行数）的SQL脚本。

这个想法是在存在任何行（在任何数据库中）的情况下重新创建数据库。

所讨论的数据库是Microsoft SQL Server。

有人可以提供一个示例脚本吗？

- vijaysylvester

---

一篇很好的文章，讨论了不同的方法：https://www.brentozar.com/archive/2014/02/count-number-rows-table-sql-server/ - Rory

Answer 1

以下 SQL 将获取数据库中所有表的行数：

CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

输出将是一个表格列表以及它们各自的行数。

如果你只想要整个数据库中的总行数，请添加：

SELECT SUM(row_count) AS total_row_count FROM #counts

这将为您获取整个数据库中行的总数的单个值。

- adrianbanks

---

我已经点赞了这个帖子，但是我认为有必要提到sp_MSForEachTable是一个未公开的存储过程。据我所知，这意味着微软不支持它，并且可能在未来版本中没有通知地将其删除。 - MarkD

@MarkD 是的，那是正确的，虽然它已经存在于几个版本的SQL Server中而没有改变。 - adrianbanks

此操作将对数据库中的所有表进行完整的表扫描，因此在生产服务器上运行它会导致相当多的磁盘IO。 - Andrew Hill

不适用于2016，'sp_MSForEachTable'不存在。 - Alan B

尝试这个方法，你不需要创建临时表再将其删除。查询语句如下： SELECT T.name AS [表名], I.rows AS [行数] FROM sys.tables AS T INNER JOIN sys.sysindexes AS I ON T.object_id = I.id AND I.indid < 2 ORDER BY I.rows DESC - Muhammad Sohail

Answer 2

如果您想绕过计算3百万行表格的时间和资源，请尝试以下SQL SERVER Central by Kendal Van Dyke中提到的方法。

---

使用sysindexes进行行计数 如果您正在使用SQL 2000，则需要像这样使用sysindexes：

-- Shows all user tables and row counts for the current database 
-- Remove OBJECTPROPERTY function call to include system objects 
SELECT o.NAME,
  i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

如果你使用的是SQL 2005或者2008，查询sysindexes仍然可以工作，但是微软建议在未来的SQL Server版本中可能会删除sysindexes，所以作为良好的实践，你应该使用DMVs代替，像这样：

-- Shows all user tables and row counts for the current database 
-- Remove is_ms_shipped = 0 check to include system objects 
-- i.index_id < 2 indicates clustered index (1) or hash table (0) 
SELECT o.name,
  ddps.row_count 
FROM sys.indexes AS i
  INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
  INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
  AND i.index_id = ddps.index_id 
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME 

- Keng

---

好的，这个脚本可以工作但需要在开头加上"use DB"。因此，尽管它现在可以正常运行，但它是具有误导性的。 - djangofan

在我的数据库中，被接受的答案需要10秒钟。这个答案只需要0秒！Keng，你得到了+1的赞。虽然我添加了JOIN sys.schemas s ON s.schema_id = o.schema_id并包含了s.Name来查看有资格的表名。 - Bernhard Hofmann

请注意，使用sysindexes或dm_db_partition_stats将为您提供行数的近似值！ - krisku

确保使用正确的数据库 - 如果选择主数据库 05/08，查询将返回空白。 我建议在答案的顶部添加“USE [在此输入您的数据库名称]”来执行该查询。 - user1221784

row_count 值可能不总是反映准确的当前行数。例如，当事务回滚时，row_count 值可能不会立即更新。在这种情况下，您可能需要手动更新统计数据以反映准确的行数。 - Paul Maxwell

Answer 3

可以在Azure上使用，不需要存储过程。

SELECT t.name       AS table_name
       ,s.row_count AS row_count
FROM   sys.tables t
JOIN   sys.dm_db_partition_stats s
  ON t.OBJECT_ID = s.OBJECT_ID
 AND t.type_desc = 'USER_TABLE'
 AND t.name NOT LIKE '%dss%' --Exclude tables created by SQL Data Sync for Azure.
 AND s.index_id IN (0, 1)
ORDER  BY table_name;

Credit.

- Adrian Hope-Bailie

---

“AND t.name not like '%dss%'" 的作用是什么？ - quillbreaker

不包括由SQL Azure数据同步创建的表。 - Adam Szabo

这个很好用。我坐下来说：“这可能需要一段时间”，但只用了两秒钟就完成了。我正在处理一个包含528个表的数据库，而且我不知道它实际上有多大。 - trench

美妙啊，正如 @trench 所喊的那样，我有点害怕，在生产环境中运行和查询一些命令，每个表中都有数亿条记录...但是在 Azure 数据库上，这只是一瞬间的事情。非常方便，谢谢。 - Irf

我在每个表格中得到了多行数据，其中一些行的值为0。 - undefined

Answer 4

这一个我觉得比其他的看起来更好。

USE  [enter your db name here]
GO

SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        --A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
          A.Name, AVG(B.rows) AS 'RowCount'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
GO

- İsmet Alkan

---

这个查询只需要具有“数据阅读者”权限。 - user565869

我不知道为什么，但这个查询不能准确地统计所有表中的行数。一些表格的数量会翻倍。我发现Rikin Patel的查询是准确的。 - Dan

与Rikin Patel的代码不同之处在于，这个代码没有检查sys.partitions条目的index_id是否为0或1。因此，问题不仅在于某些行被重复，而是某些行被它们所拥有的索引数目所乘。所以，如果您有一个包含100行的表，并定义了3个索引，上面的查询将显示该表的3*100=300行。 - Anssssss

上面的评论是正确的 - 但我只需要了解哪些表中有“一些”行，哪些表中没有。我只有data_reader权限，因此其他解决方案对我无效 - 只有这个有效。 - Ed Graham

Answer 5

简短而简洁

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

输出：

更新： 如果数据库非常大，这个查询可能会消耗大量资源。（根据评论）

- Rikin Patel

---

多年以后，这个工具完美地运行在SQL Server 2017上，可以快速查看每个表有多少行。 - Dwight

多年过去了，这个命令花了五分钟以上的时间，当我试图终止它时，我的会话卡住了。我相信这是由Ashish Kumar Mehta在另一个答案中所写的SELECT COUNT(1) FROM ?是一个资源密集型的操作所导致的。 - undefined

Answer 6

SELECT 
    sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM 
    sys.tables ta
INNER JOIN sys.partitions pa
    ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
    ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

- Rikin Patel

---

只是想指出 "pa.index_id IN (1,0)" 条件的原因：它是为了限制一个表格的一组行数。更多信息请参见此处 - https://dev59.com/G0bRa4cB1Zd3GeqPykbD#456062 - Anssssss

认为这不包括视图。除此之外，它运行良好。 - Luiz Henrique Martins Lins Rol

Answer 7

SQL Server 2005或更高版本提供了一个很好的报告，显示表大小 - 包括行计数等。它在标准报告中 - 名为“按表格使用磁盘空间”。

通过编程实现，可以使用一个优雅的解决方案，详见： http://www.sqlservercentral.com/articles/T-SQL/67624/

- Peter Schofield

---

没有任何管理员权限，这段代码可以正常运行。选择 T.name 作为 [TABLE NAME]，I.rows 作为 [ROWCOUNT] 从 sys.tables AS T 和 sys.sysindexes AS I 进行内部连接 其中 T.object_id = I.id 并且 I.indid < 2 按照 I.rows 降序排序。 - ASH

多年后，这个答案和Ash的评论对我起了作用。Rikin Patel的解决方案占用了我的会话，而其他解决方案超出了我的权限。 - undefined

Answer 8

请勿使用 SELECT COUNT(*) FROM TABLENAME，因为这是一个资源密集型操作。我们可以使用 SQL Server Dynamic Management Views 或 System Catalogs 来获取数据库中所有表的行数信息。请注意优化查询性能。

- Ashish Kumar Mehta

Answer 9

这里有一种动态SQL的方法，同时还提供了模式：

DECLARE @sql nvarchar(MAX)

SELECT
    @sql = COALESCE(@sql + ' UNION ALL ', '') +
        'SELECT
            ''' + s.name + ''' AS ''Schema'',
            ''' + t.name + ''' AS ''Table'',
            COUNT(*) AS Count
            FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    FROM sys.schemas s
    INNER JOIN sys.tables t ON t.schema_id = s.schema_id
    ORDER BY
        s.name,
        t.name

EXEC(@sql)

如果需要的话，将其扩展到在实例中运行所有数据库将是微不足道的（加入到sys.databases）。

- Jon Seigel

Answer 10

我会对Frederik的解决方案进行轻微修改。我将使用sp_spaceused系统存储过程，该过程还将包括数据和索引大小。

声明c_tables游标快速向前
选择表名from information_schema.tables 

打开c_tables 
声明@tablename varchar(255) 
声明@stmt nvarchar(2000) 
声明@rowcount int 
获取下一个@c_tables到@tablename 

while @@fetch_status = 0 
begin 

    选择@stmt = 'sp_spaceused ' + @tablename 

    执行sp_executesql @stmt

    获取下一个@c_tables到@tablename 

end 

关闭c_tables 
取消分配c_tables 

- Vod