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

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

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

从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

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

下面是一个动态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)是很简单的。