如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
当前回答
从这个问题中: https://dba.stackexchange.com/questions/114958/list-all-tables-from-all-user-databases/230411#230411
我在@Aaron Bertrand提供的答案中添加了记录计数,该答案列出了所有数据库和所有表。
DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @sql = N'', @src = N' UNION ALL
SELECT ''$d'' as ''database'',
s.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''schema'',
t.name COLLATE SQL_Latin1_General_CP1_CI_AI as ''table'' ,
ind.rows as record_count
FROM [$d].sys.schemas AS s
INNER JOIN [$d].sys.tables AS t ON s.[schema_id] = t.[schema_id]
INNER JOIN [$d].sys.sysindexes AS ind ON t.[object_id] = ind.[id]
where ind.indid < 2';
SELECT @sql = @sql + REPLACE(@src, '$d', name)
FROM sys.databases
WHERE database_id > 4
AND [state] = 0
AND HAS_DBACCESS(name) = 1;
SET @sql = STUFF(@sql, 1, 10, CHAR(13) + CHAR(10));
PRINT @sql;
--EXEC sys.sp_executesql @sql;
其他回答
以下是我对这个问题的看法。它包含所有模式,只列出带行的表。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;
首先想到的是使用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
如图所示,这将返回正确的计数,而使用元数据表的方法将只返回估计值。
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
Shnugo的答案是唯一一个工作在Azure的Externa表。(1) Azure SQL根本不支持sp_MSforeachtable;External表的partitions总是0。
接受的答案在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