如何列出数据库中每个表的行数。相当于
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);
其他回答
我在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;
首先想到的是使用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
以下是我对这个问题的看法。它包含所有模式，只列出带行的表。YMMV
select distinct schema_name(t.schema_id) as schema_name, t.name as
table_name, p.[Rows]
from sys.tables as t
INNER JOIN sys.indexes as i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id =
p.index_id
where p.[Rows] > 0
order by schema_name;
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;
Shnugo的答案是唯一一个工作在Azure的Externa表。(1) Azure SQL根本不支持sp_MSforeachtable;External表的partitions总是0。
