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

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

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


当前回答

你可以试试这个:

SELECT  OBJECT_SCHEMA_NAME(ps.object_Id) AS [schemaname],
        OBJECT_NAME(ps.object_id) AS [tablename],
        row_count AS [rows]
FROM sys.dm_db_partition_stats ps
WHERE OBJECT_SCHEMA_NAME(ps.object_Id) <> 'sys' AND ps.index_id < 2
ORDER BY 
        OBJECT_SCHEMA_NAME(ps.object_Id),
        OBJECT_NAME(ps.object_id)

其他回答

        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

如图所示,这将返回正确的计数,而使用元数据表的方法将只返回估计值。

    CREATE PROCEDURE ListTableRowCounts 
    AS 
    BEGIN 
        SET NOCOUNT ON 

        CREATE TABLE #TableCounts
        ( 
            TableName VARCHAR(500), 
            CountOf INT 
        ) 

        INSERT #TableCounts
            EXEC sp_msForEachTable 
                'SELECT PARSENAME(''?'', 1), 
                COUNT(*) FROM ? WITH (NOLOCK)' 

        SELECT TableName , CountOf 
            FROM #TableCounts
            ORDER BY TableName 

        DROP TABLE #TableCounts
    END
    GO
USE DatabaseName
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

我在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;