如何列出数据库中每个表的行数。相当于

select count(*) from table1
select count(*) from table2
...
select count(*) from tableN

我会张贴一个解决方案,但其他方法是受欢迎的


当前回答

您可以复制,过去和执行这段代码,以获得所有表记录计数到一个表。注意:代码带有注释

create procedure RowCountsPro
as
begin
--drop the table if exist on each exicution
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL 
DROP TABLE dbo.RowCounts;
-- creating new table
CREATE TABLE RowCounts 
( [TableName]            VARCHAR(150)
, [RowCount]               INT
, [Reserved]                 NVARCHAR(50)
, [Data]                        NVARCHAR(50)
, [Index_Size]               NVARCHAR(50)
, [UnUsed]                   NVARCHAR(50))
--inserting all records
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
--  "sp_MSforeachtable" System Procedure, 'sp_spaceused "?"' param to get records and resources used
EXEC sp_MSforeachtable 'sp_spaceused "?"' 
-- selecting data and returning a table of data
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM RowCounts
ORDER BY [TableName]
end

我已经测试了这段代码,它在SQL Server 2014上运行良好。

其他回答

接受的答案在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

您可以复制,过去和执行这段代码,以获得所有表记录计数到一个表。注意:代码带有注释

create procedure RowCountsPro
as
begin
--drop the table if exist on each exicution
IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL 
DROP TABLE dbo.RowCounts;
-- creating new table
CREATE TABLE RowCounts 
( [TableName]            VARCHAR(150)
, [RowCount]               INT
, [Reserved]                 NVARCHAR(50)
, [Data]                        NVARCHAR(50)
, [Index_Size]               NVARCHAR(50)
, [UnUsed]                   NVARCHAR(50))
--inserting all records
INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
--  "sp_MSforeachtable" System Procedure, 'sp_spaceused "?"' param to get records and resources used
EXEC sp_MSforeachtable 'sp_spaceused "?"' 
-- selecting data and returning a table of data
SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM RowCounts
ORDER BY [TableName]
end

我已经测试了这段代码,它在SQL Server 2014上运行良好。

以下是我对这个问题的看法。它包含所有模式,只列出带行的表。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 'DECLARE @t AS VARCHAR(MAX); 
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX)) 
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

输出:

从这个问题中: 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;