如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
当前回答
要在SQL Management Studio中获取该信息,右键单击数据库,然后选择Reports—> Standard Reports—> Disk Usage by Table。
其他回答
如图所示,这将返回正确的计数,而使用元数据表的方法将只返回估计值。
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
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;
我想分享对我有效的方法
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND sdmvPTNS.index_id < 2
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
数据库托管在Azure中,最终结果是:
来源:https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
接受的答案在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
首先想到的是使用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