如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案，但其他方法是受欢迎的
当前回答
要在SQL Management Studio中获取该信息，右键单击数据库，然后选择Reports—> Standard Reports—> Disk Usage by Table。
其他回答
要在SQL Management Studio中获取该信息，右键单击数据库，然后选择Reports—> Standard Reports—> Disk Usage by Table。
你可以试试这个:
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 T.object_id, T.name, I.indid, I.rows
from Sys.tables T
left join Sys.sysindexes I
on (I.id = T.object_id and (indid =1 or indid =0 ))
where T.type='U'
这里indid=1表示群集索引，而indid=0表示堆索引
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
sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'
输出:
