我会对弗雷德里克的方案做些小改动。我将使用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 

我会对弗雷德里克的方案做些小改动。我将使用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 

这是我最喜欢的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%')

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

在编程上，有一个很好的解决方案: http://www.sqlservercentral.com/articles/T-SQL/67624/

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

如果您希望通过传递时间和资源来计数(*)您的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