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

How to fetch the row count for all tables in a SQL SERVER database

提问by vijaysylvester

I am searching for a SQL Script that can be used to determine if there is any data (i.e. row count) in any of the tables of a given database.

我正在搜索可用于确定给定数据库的任何表中是否有任何数据（即行数）的 SQL 脚本。

The idea is to re-incarnate the database in case there are any rows existing (in any of the database).

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

The database being spoken of is Microsoft SQL SERVER.

所说的数据库是Microsoft SQL SERVER.

Could someone suggest a sample script?

有人可以建议一个示例脚本吗？

回答by adrianbanks

The following SQL will get you the row count of all tables in a database:

以下 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

The output will be a list of tables and their row counts.

输出将是一个表列表及其行数。

If you just want the total row count across the whole database, appending:

如果您只想要整个数据库的总行数，请附加：

SELECT SUM(row_count) AS total_row_count FROM #counts

will get you a single value for the total number of rows in the whole database.

将为您提供整个数据库中总行数的单个值。

回答by Keng

If you want to by pass the time and resources it takes to count(*) your 3million row tables. Try this per SQL SERVER Central by Kendal Van Dyke.

如果您想省去计算 (*) 300 万个行表所需的时间和资源。根据 Kendal Van Dyke 的 SQL SERVER Central 尝试此操作。

---

---

Row Counts Using sysindexes If you're using SQL 2000 you'll need to use sysindexes like so:

使用 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

If you're using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice you should use the DMVs instead, like so:

如果您使用的是 SQL 2005 或 2008，查询 sysindexes 仍然有效，但 Microsoft 建议在未来版本的 SQL Server 中可能会删除 sysindexes，因此作为一种好的做法，您应该改用 DMV，如下所示：

-- 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 

回答by Adrian Hope-Bailie

Works on Azure, doesn't require stored procs.

适用于 Azure，不需要存储过程。

SELECT t.name, s.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%'
AND s.index_id IN (0,1)

Credit.

信用。

回答by ?smet Alkan

This one looks better than the others I think.

这个看起来比我认为的其他的要好。

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

回答by Rikin Patel

Short and sweet

又短又甜

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

Output:

输出：

回答by Peter Schofield

SQL Server 2005 or later gives quite a nice report showing table sizes - including row counts etc. It's in Standard Reports - and it is Disc Usage by Table.

SQL Server 2005 或更高版本提供了一个很好的报告，显示了表的大小——包括行计数等。它在标准报告中——它是表的磁盘使用情况。

Programmatically, there's a nice solution at: http://www.sqlservercentral.com/articles/T-SQL/67624/

以编程方式，有一个很好的解决方案：http: //www.sqlservercentral.com/articles/T-SQL/67624/

回答by Rikin Patel

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

回答by Ashish Kumar Mehta

Don't use SELECT COUNT(*) FROM TABLENAME, since that is a resource intensive operation. One should use SQL Server Dynamic Management Viewsor System Catalogsto get the row count information for all tables in a database.

不要使用SELECT COUNT(*) FROM TABLENAME，因为这是一个资源密集型操作。应该使用SQL Server 动态管理视图或系统目录来获取数据库中所有表的行数信息。

回答by Vod

I would make a minor change to Frederik's solution. I would use the sp_spaceused system stored procedure which will also include data and index sizes.

我会对 Frederik 的解决方案做一个小改动。我将使用 sp_spaceused 系统存储过程，它还将包括数据和索引大小。

declare c_tables cursor fast_forward for 
select table_name from information_schema.tables 

open c_tables 
declare @tablename varchar(255) 
declare @stmt nvarchar(2000) 
declare @rowcount int 
fetch next from c_tables into @tablename 

while @@fetch_status = 0 
begin 

    select @stmt = 'sp_spaceused ' + @tablename 

    exec sp_executesql @stmt

    fetch next from c_tables into @tablename 

end 

close c_tables 
deallocate c_tables 

回答by Frederik Gheysels

select all rows from the information_schema.tables view, and issue a count(*) statement for each entry that has been returned from that view.

从 information_schema.tables 视图中选择所有行，并为从该视图返回的每个条目发出 count(*) 语句。

declare c_tables cursor fast_forward for
select table_name from information_schema.tables

open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename

while @@fetch_status = 0
begin

    select @stmt = 'select @rowcount = count(*) from ' + @tablename

    exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT

    print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'

    fetch next from c_tables into @tablename

end

close c_tables
deallocate c_tables