如果你使用的是SQL Server 2005及以上版本，你也可以使用这个:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes 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
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

在我看来，它比sp_msforeachtable输出更容易处理。

SELECT 
    T.NAME AS 'TABLE NAME',
    P.[ROWS] AS 'NO OF ROWS'
FROM SYS.TABLES T 
INNER JOIN  SYS.PARTITIONS P ON T.OBJECT_ID=P.OBJECT_ID;

我在http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21021上找到了一个帮助我的片段:

select t.name TableName, i.rows Records
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
order by TableName;

我认为最短、最快、最简单的方法是:

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

        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

幸运的是，SQL Server管理工作室会给你一个提示，告诉你如何做到这一点。 这样做,

启动SQL Server跟踪并打开您正在进行的活动(过滤器) 如果您不是一个人，请通过您的登录ID，并设置应用程序名称 到Microsoft SQL Server Management Studio)，暂停跟踪并丢弃迄今为止记录的任何结果; 然后，右键单击一个表，并从弹出菜单中选择属性; 再次启动跟踪; 现在在SQL Server Management studio中选择左侧的存储属性项;

暂停跟踪，看看microsoft生成了什么TSQL。

在可能是最后一个查询中，您将看到以exec sp_executesql N'SELECT开头的语句

当您将执行的代码复制到visual studio时，您将注意到这段代码生成了微软工程师用于填充属性窗口的所有数据。

当你对该查询进行适度修改时，你会得到这样的结果:

SELECT
SCHEMA_NAME(tbl.schema_id)+'.'+tbl.name as [table], --> something I added
p.partition_number AS [PartitionNumber],
prv.value AS [RightBoundaryValue],
 fg.name AS [FileGroupName],
CAST(pf.boundary_value_on_right AS int) AS [RangeType],
CAST(p.rows AS float) AS [RowCount],
p.data_compression AS [DataCompression]
FROM sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and idx.index_id < 2
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = idx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON fg.data_space_id = dds.data_space_id or fg.data_space_id = idx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON  pf.function_id = prv.function_id

现在的查询并不完美，你可以更新它来满足你可能遇到的其他问题，关键是，你可以使用微软的知识来得到你的大多数问题，通过执行你感兴趣的数据和跟踪使用分析器生成的TSQL。

我喜欢认为MS工程师知道SQL server是如何工作的，它会生成TSQL，适用于所有项目，你可以使用你正在使用的SSMS上的版本，所以它在以前、现在和未来的各种版本上都很好。

记住，不要只是照抄，要试着理解它，否则你可能会得到错误的答案。

沃尔特