如何列出数据库中每个表的行数。相当于
select count(*) from table1
select count(*) from table2
...
select count(*) from tableN
我会张贴一个解决方案,但其他方法是受欢迎的
如何列出数据库中每个表的行数。相当于
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上运行良好。
其他回答
sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'
输出:
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表示堆索引
SELECT ( Schema_name(A.schema_id) + '.' + A.NAME ) AS TableName,
Sum(B.rows)AS RecordCount
FROM sys.objects A INNER JOIN sys.partitions B
ON A.object_id = B.object_id WHERE A.type = 'U'
GROUP BY A.schema_id,A.NAME ;
QUERY_PHOTO
QUERY_RESULT_PHOTO
您可以复制,过去和执行这段代码,以获得所有表记录计数到一个表。注意:代码带有注释
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上运行良好。
我认为最短、最快、最简单的方法是:
SELECT
object_name(object_id) AS [Table],
SUM(row_count) AS [Count]
FROM
sys.dm_db_partition_stats
WHERE
--object_schema_name(object_id) = 'dbo' AND
index_id < 2
GROUP BY
object_id