统计数据库中每张表的记录数统计

统计数据库中每张表的记录数统计，

SQL如下：

GO

USE eehu

GO

CREATE TABLE #TABLE
(
TNAME VARCHAR(20),
COUNTS INT
)

GO

DECLARE @SQL NVARCHAR(MAX)
SELECT  
@SQL=ISNULL(@SQL+'','')+'select [name],(select count(1) from '+[name]+' ) 
from sysobjects where type=''u'' AND NAME='''+[name]+'''  UNION ALL '
FROM sysobjects where type='u'
SET @SQL =@SQL+'SELECT TOP 1 NAME ,0 FROM sysobjects WHERE 1=2'
SET @SQL='INSERT INTO #TABLE '+@SQL
EXEC(@SQL)

GO

SELECT * FROM #TABLE ORDER BY COUNTS DESC

GO

DROP TABLE #TABLE

另一种方式：

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

sys.indexes (Transact-SQL)
http://technet.microsoft.com/zh-cn/office/ms173760

sys.dm_db_partition_stats

http://technet.microsoft.com/zh-cn/library/ms187737(SQL.90).aspx