如何在SQL SERVER数据库中获取所有表的行数

SQL Server 2005或更高版本提供了一个相当不错的报告，显示表大小-包括行数等。这是在标准报告-它是光盘使用表。

从information_schema中选择所有行。表视图，并为从该视图返回的每个条目发出count(*)语句。

declare c_tables cursor fast_forward for
select table_name from information_schema.tables


open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename


while @@fetch_status = 0
begin


select @stmt = 'select @rowcount = count(*) from ' + @tablename


exec sp_executesql @stmt, N'@rowcount int output', @rowcount=@rowcount OUTPUT


print N'table: ' + @tablename + ' has ' + convert(nvarchar(1000),@rowcount) + ' rows'


fetch next from c_tables into @tablename


end


close c_tables
deallocate c_tables

下面的SQL语句将得到数据库中所有表的行数:

CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)


EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts

输出将是一个表列表及其行数。

如果你只想要整个数据库的总行数，追加:

SELECT SUM(row_count) AS total_row_count FROM #counts

将获得整个数据库中总行数的单个值。

我会对弗雷德里克的方案做些小改动。我将使用sp_spaceused系统存储过程，它还将包括数据和索引大小。

declare c_tables cursor fast_forward for
select table_name from information_schema.tables


open c_tables
declare @tablename varchar(255)
declare @stmt nvarchar(2000)
declare @rowcount int
fetch next from c_tables into @tablename


while @@fetch_status = 0
begin


select @stmt = 'sp_spaceused ' + @tablename


exec sp_executesql @stmt


fetch next from c_tables into @tablename


end


close c_tables
deallocate c_tables

如果您希望通过传递时间和资源来计数(*)您的300万行表。试试Kendal Van Dyke的SQL SERVER Central。

行计数使用sysindexes 如果你在使用SQL 2000，你需要像这样使用sysindexes:

-- Shows all user tables and row counts for the current database
-- Remove OBJECTPROPERTY function call to include system objects
SELECT o.NAME,
i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

如果你正在使用SQL 2005或2008查询sysindexes仍然可以工作，但微软建议sysindexes可能会在SQL Server的未来版本中被删除，因此作为一个良好的实践，你应该使用dmv，如下所示:

-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2  AND o.is_ms_shipped = 0 ORDER BY o.NAME

下面是一个动态SQL方法，它也提供了模式:

DECLARE @sql nvarchar(MAX)


SELECT
@sql = COALESCE(@sql + ' UNION ALL ', '') +
'SELECT
''' + s.name + ''' AS ''Schema'',
''' + t.name + ''' AS ''Table'',
COUNT(*) AS Count
FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.schemas s
INNER JOIN sys.tables t ON t.schema_id = s.schema_id
ORDER BY
s.name,
t.name


EXEC(@sql)

如果需要，扩展它以遍历实例中的所有数据库(连接到sys.databases)是很简单的。

这是我最喜欢的SQL 2008解决方案，它将结果放入一个“TEST”临时表，我可以使用它来排序并获得我需要的结果:

SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
DROP TABLE #t;
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
) ;
INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
SELECT * INTO TEST FROM #t;
DROP TABLE #t;
SELECT  name, [rows], reserved, data, index_size, unused FROM TEST \
WHERE ([rows] > 0) AND (name LIKE 'XXX%')

不要使用SELECT COUNT(*) FROM TABLENAME，因为这是一个资源密集型操作。应该使用 . SQL Server Dynamic Management Views . SQL Server Dynamic Management Views或< >强系统目录< / >强来获取数据库中所有表的行计数信息。

我觉得这个看起来比其他的好。

USE  [enter your db name here]
GO


SELECT      SCHEMA_NAME(A.schema_id) + '.' +
--A.Name, SUM(B.rows) AS 'RowCount'  Use AVG instead of SUM
A.Name, AVG(B.rows) AS 'RowCount'
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
GO

SELECT
sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM
sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

短而甜

sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);
SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))
+ CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'

输出:

    SELECT
SUM(sdmvPTNS.row_count) AS [DBRows]
FROM
sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0
AND sdmvPTNS.index_id < 2
GO

在Azure上工作，不需要存储过程。

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 IN (0,1)

信贷。