SQL Server统计各用户数据表中记录条数的两种办法比较[MSSQL防范]

办法一:

DECLARE @NAME VARCHAR(50)
DECLARE @SQL VARCHAR(1000)

SET @SQL = '
DECLARE @RESULT_TABLE TABLE
(
[TableName] VARCHAR(32),
[RowCount] INT
)
DECLARE @TEMP_COUNT INT'

DECLARE TB_CURSOR CURSOR FOR

SELECT
TABLE_NAME = CONVERT(SYSNAME,O.NAME)
FROM
SYS.ALL_OBJECTS O
WHERE
O.TYPE = 'U' AND
HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(O.SCHEMA_ID)) + '.' + QUOTENAME(O.NAME),
'OBJECT',
'SELECT') = 1

OPEN TB_CURSOR
FETCH NEXT FROM TB_CURSOR INTO @NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @SQL + CHAR(10) + 'INSERT INTO @RESULT_TABLE SELECT ' + '''' + @NAME + '''' + ',COUNT(1) FROM ' + @NAME + ';'

FETCH NEXT FROM TB_CURSOR INTO @NAME
END

CLOSE TB_CURSOR
DEALLOCATE TB_CURSOR

SET @SQL = @SQL + CHAR(10) +'SELECT * FROM @RESULT_TABLE '
EXEC (@SQL)

这里利用表变量而非暂时表,是因为大大都数据库中表的数目不会太多,使得暂时表（或表变量）中的记录条数不会很多.如此一来,借以表变量,将数据暂时存放放在内存中要比存放在tempDB中越发高效.

基本思绪为:

1.从系统视图SYS.ALL_OBJECTS中取出全部用户表的表名.

2.用游标遍历全部表名,并利用select count(1)来统计该表行数,并拼接成呼应的暂存SQL代码.

3.履行生成的SQL代码,获得数据后果集.此中生成的SQL代码为:

DECLARE @RESULT_TABLE TABLE
(
[TableName] VARCHAR(32),
[RowCount] INT
)
DECLARE @TEMP_COUNT INT

-- each tables
INSERT INTO @RESULT_TABLE SELECT 'LDMMessage',COUNT(1) FROM LDMMessage;
INSERT INTO @RESULT_TABLE SELECT 'DCSFile',COUNT(1) FROM DCSFile;
INSERT INTO @RESULT_TABLE SELECT 'SSRCode',COUNT(1) FROM SSRCode;
INSERT INTO @RESULT_TABLE SELECT 'PRLMessage',COUNT(1) FROM PRLMessage;
...

SELECT * FROM @RESULT_TABLE

写完之后,感受毕竟利用到了游标和表变量,性能不太抱负,应当还有更好的办法,便谷歌了一下,发现也可以从系统视图SYS.SYSOBJECTS中查出用户表名,并利用主键ID衔接视图SYS.SYSINDEXES,按照索引的相关数据来得到表的记录条数:

办法二:

DECLARE @RESULT_TABLE TABLE
(
[TableName] VARCHAR(32),
[RowCount] INT
)

INSERT INTO
@RESULT_TABLE
SELECT
O.NAME, I.ROWCNT
FROM
SYS.SYSOBJECTS O, SYSINDEXES I
WHERE
O.ID = I.ID AND
O.XTYPE = 'U' AND
I.INDID < 2

SELECT * FROM @RESULT_TABLE

这里主要利用了SYS.SYSOBJECTS和SYS.SYSINDEXES的衔接,并通过 I.INDID < 2 条件找到表的堆积索引或堆记录（Heap:0, 堆积索引:1,非堆积索引>1）,由此得出Data级别的记录条数RowCnt.

性能比较:

利用sql server Profiler来检测两种办法的履行开销,后果以下:

办法一开销62个CPU时间片,而办法二之开销了2个时间片,性能大为胜出. 　　以上是“SQL Server统计各用户数据表中记录条数的两种办法比较[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：