统计数据库中每张表的记录数统计,
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
http://technet.microsoft.com/zh-cn/office/ms173760
sys.dm_db_partition_stats
http://technet.microsoft.com/zh-cn/library/ms187737(SQL.90).aspx