如何列出数据库中每个表的行数。相当于

select count(*) from table1
select count(*) from table2
...
select count(*) from tableN

我会张贴一个解决方案,但其他方法是受欢迎的


当前回答

查找SQL reference (http://www.codeproject.com/Tips/811017/Fastest-way-to-find-row-count-of-all-tables-in-SQL)中所有表的行数的最快方法

SELECT T.name AS [TABLE NAME], I.rows AS [ROWCOUNT] 
    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

其他回答

        SELECT ( Schema_name(A.schema_id) + '.' + A.NAME ) AS TableName,
 Sum(B.rows)AS RecordCount 
    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 ;

QUERY_PHOTO

QUERY_RESULT_PHOTO

首先想到的是使用sp_msForEachTable

exec sp_msforeachtable 'select count(*) from ?'

但是它没有列出表名,所以可以扩展到

exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'

这里的问题是,如果数据库有超过100个表,你会得到以下错误消息:

查询已超过最大值 可以的结果集的数目 显示在结果网格中。只有 前100个结果集是 显示在网格中。

所以我最终使用表变量来存储结果

declare @stats table (n sysname, c int)
insert into @stats
    exec sp_msforeachtable 'select parsename(''?'', 1),  count(*) from ?'
select 
    * 
from @stats
order by c desc

我在http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021上找到了一个帮助我的片段:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
order by TableName;

接受的答案在Azure SQL上不适合我,这里有一个,它非常快,完全符合我的要求:

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 = 1
order by s.row_count desc
SELECT 
    T.NAME AS 'TABLE NAME',
    P.[ROWS] AS 'NO OF ROWS'
FROM SYS.TABLES T 
INNER JOIN  SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;