如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
当前回答
这种方法使用字符串连接来动态生成包含所有表及其计数的语句,就像原始问题中给出的示例:
SELECT COUNT(*) AS Count,'[dbo].[tbl1]' AS TableName FROM [dbo].[tbl1]
UNION ALL SELECT COUNT(*) AS Count,'[dbo].[tbl2]' AS TableName FROM [dbo].[tbl2]
UNION ALL SELECT...
最后用EXEC执行:
DECLARE @cmd VARCHAR(MAX)=STUFF(
(
SELECT 'UNION ALL SELECT COUNT(*) AS Count,'''
+ QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
+ ''' AS TableName FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE TABLE_TYPE='BASE TABLE'
FOR XML PATH('')
),1,10,'');
EXEC(@cmd);
其他回答
要在SQL Management Studio中获取该信息,右键单击数据库,然后选择Reports—> Standard Reports—> Disk Usage by Table。
sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'
输出:
如果您使用MySQL >4。X你可以用这个:
select TABLE_NAME, TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA="test";
请记住,对于某些存储引擎,TABLE_ROWS只是一个近似值。
接受的答案在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
查找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