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

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

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



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值