如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
当前回答
首先想到的是使用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
其他回答
你可以试试这个:
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)
我在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;
查找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
这个sql脚本给出了所选数据库中每个表的模式、表名和行数:
SELECT SCHEMA_NAME(schema_id) AS [SchemaName],
[Tables].name AS [TableName],
SUM([Partitions].[rows]) AS [TotalRowCount]
FROM sys.tables AS [Tables]
JOIN sys.partitions AS [Partitions]
ON [Tables].[object_id] = [Partitions].[object_id]
AND [Partitions].index_id IN ( 0, 1 )
-- WHERE [Tables].name = N'name of the table'
GROUP BY SCHEMA_NAME(schema_id), [Tables].name
order by [TotalRowCount] desc
裁判:https://blog.sqlauthority.com/2017/05/24/sql-server-find-row-count-every-table-database-efficiently/
另一种表达方式:
SELECT o.NAME TABLENAME,
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 i.rowcnt 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;