SQL Server 中统计各用户表记录条数 的两种方法
最近做数据监控遇到这么个查询需求，就从系统存储过程[sys].[sp_tables]中征用了遍历用户表的代码，组织一下，配合以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
(
[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
写完之后，感觉毕竟使用到了游标和表变量，性能不太理想，应该还有更好的方法，便google了一下，发现也可以从系统视图SYS.SYSOBJECTS中查出用户表名，并使用主键ID连接视图SYS.SYSINDEXES，根据索引的相关数据来获得表的记录条数:
方法二：
DECLARE @RESULT_TABLE TABLE
(
[TableName] VARCHAR(32),
[RowCount] INT
)
@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
(
[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个时间片，性能大为胜出。
参考资料：
http://msdn.microsoft.com/en-us/library/ms190324.aspx
http://msdn.microsoft.com/en-us/library/ms178618.aspx
http://msdn.microsoft.com/en-us/library/ms190283.aspx
/****** Object: StoredProcedure [sys].[sp_tables] Script Date: 10/27/2010 14:06:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO ALTER procedure [sys].[sp_tables]
(
@table_name nvarchar(384) = null,
@table_owner nvarchar(384) = null,
@table_qualifier sysname = null,
@table_type varchar(100) = null,
@fUsePattern bit = 1 — To allow users to explicitly disable all pattern matching.
)
as
declare @type1 varchar(3)
declare @qual_name nvarchar(517) — [schema].[table]
declare @table_id int
if @table_qualifier = \’%\’ and @table_owner = \’\’ and @table_name = \’\’
begin
— Debug output, do not remove it.
— print \’Special feature #1: enumerate databases when owner and name are blank but qualifier is explicitly “%”.\’
select
TABLE_QUALIFIER = convert(sysname,d.name),
TABLE_OWNER = convert(sysname,null),
TABLE_NAME = convert(sysname,null),
TABLE_TYPE = convert(varchar(32),null),
REMARKS = convert(varchar(254),null) — Remarks are NULL.
from
sys.databases d
where
d.name <> \’model\’ — eliminate MODEL database
order by 1
return
end
if @table_qualifier = \’\’ and @table_owner = \’%\’ and @table_name = \’\’
begin
— Debug output, do not remove it.
— print \’Special feature #2: enumerate owners when qualifier and name are blank but owner is explicitly “%”.
select distinct
TABLE_QUALIFIER = convert(sysname,null),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,null),
TABLE_TYPE = convert(varchar(32),null),
REMARKS = convert(varchar(254),null) — Remarks are NULL.
from
sys.all_objects o
where
o.type in (\’S\’,\’U\’,\’V\’) — limit columns to tables and views only
order by 2
return
end
if @table_qualifier = \’\’ and @table_owner = \’\’ and @table_name = \’\’ and @table_type = \’%\’
begin
— Debug output, do not remove it.
— print \’Special feature #3: enumerate table types when qualifier, owner and name are blank but table type is explicitly “%”.\’
select
TABLE_QUALIFIER = convert(sysname,null),
TABLE_OWNER = convert(sysname,null),
TABLE_NAME = convert(sysname,null),
TABLE_TYPE = convert(varchar(32),
rtrim(substring(\’SYSTEM TABLETABLE VIEW\’,(c.column_id–1)*12+1,12))),
REMARKS = convert(varchar(254),null) — Remarks are NULL.
from
sys.all_objects o,
sys.all_columns c
where
o.object_id = c.object_id and o.object_id = object_id(\’sysusers\’) and
c.column_id <= 3 — ISSUE – what is this for ???
return
end
—
— End of special features – do normal processing.
—
if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin
if @table_qualifier = \’\’
begin — If empty qualifier supplied, force an empty result set.
select @table_name = \’\’
select @table_owner = \’\’
end
else
begin — If qualifier doesn\’t match current database.
raiserror (15250, –1,–1)
return
end
end
end
select @table_qualifier = null — it\’s not needed anymore
if @table_type is null
begin — Select all ODBC supported table types.
select @type1 = \’SUV\’
end
else
begin
— TableType is case sensitive if CS server.
if (charindex(\’\’\’SYSTEM TABLE\’\’\’,@table_type) <> 0)
select @type1 = \’S\’ — Add System Tables.
else
select @type1 = \’\’
if (charindex(\’\’\’TABLE\’\’\’,@table_type) <> 0)
select @type1 = @type1 + \’U\’ — Add User Tables.
if (charindex(\’\’\’VIEW\’\’\’,@table_type) <> 0)
select @type1 = @type1 + \’V\’ — Add Views.
end
if @table_name is not null
begin
if (@table_owner is null) and (charindex(\’%\’, @table_name) = 0)
begin — If owner not specified and table contains wildchar.
if exists
(
select
*
from
sys.all_objects o
where
o.schema_id = schema_id() and
o.object_id = object_id(@table_name) and
o.type in (\’U\’,\’V\’,\’S\’)
)
begin — Override supplied owner w/owner of table.
select @table_owner = schema_name()
end
end
end
select @qual_name = isnull(quotename(@table_owner), \’\’) + \’.\’ + quotename(@table_name)
select @table_id = object_id(@qual_name)
if (@fUsePattern = 1) — Does the user want it?
begin
if ((isnull(charindex(\’%\’, @table_name),0) = 0) and
(isnull(charindex(\’_\’, @table_name),0) = 0) and
(isnull(charindex(\’%\’, @table_owner),0) = 0) and
(isnull(charindex(\’_\’, @table_owner),0) = 0) and
(@table_id is not null))
begin
select @fUsePattern = 0 — not a single wild char, so go the fast way.
end
end
if @fUsePattern = 0
begin
/* — Debug output, do not remove it.
print \’*************\’
print \’There is NO pattern matching.\’
print @fUsePattern
print isnull(@table_name, \’@table_name = null\’)
print isnull(@table_owner, \’@table_owner = null\’)
print isnull(@table_type, \’@table_type = null\’)
print isnull(@type1, \’@type1 = null\’)
print \’*************\’
*/
select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,o.name),
TABLE_TYPE = convert(varchar(32),
rtrim(substring(\’SYSTEM TABLE TABLE VIEW \’,
(ascii(o.type)–83)*12+1,12)) — \’S\’=0,\’U\’=2,\’V\’=3
),
REMARKS = convert(varchar(254),null) — Remarks are NULL.
from
sys.all_objects o
where
o.object_id = @table_id and
o.type in (\’S\’,\’U\’,\’V\’) and
has_perms_by_name(@qual_name, \’object\’, \’select\’) = 1 and
charindex(substring(o.type,1,1),@type1) <> 0 — Only desired types.
order by 4, 1, 2, 3
end
else
begin
/* — Debug output, do not remove it.
print \’*************\’
print \’THERE IS pattern matching!\’
print @fUsePattern
print isnull(@table_name, \’@table_name = null\’)
print isnull(@table_owner, \’@table_owner = null\’)
print isnull(@table_type, \’@table_type = null\’)
print isnull(@type1, \’@type1 = null\’)
print \’*************\’
*/
select
TABLE_QUALIFIER = convert(sysname,db_name()),
TABLE_OWNER = convert(sysname,schema_name(o.schema_id)),
TABLE_NAME = convert(sysname,o.name),
TABLE_TYPE = convert(varchar(32),
rtrim(substring(\’SYSTEM TABLE TABLE VIEW \’,
(ascii(o.type)–83)*12+1,
12)) — \’S\’=0,\’U\’=2,\’V\’=3
),
REMARKS = convert(varchar(254),null) — Remarks are NULL.
from
sys.all_objects o
where
o.type in (\’S\’,\’U\’,\’V\’) and
has_perms_by_name(quotename(schema_name(o.schema_id)) + \’.\’ + quotename(o.name),
\’object\’,
\’select\’) = 1 and
charindex(substring(o.type,1,1),@type1) <> 0 and — Only desired types.
(@table_name is NULL or o.name like @table_name) and
(@table_owner is NULL or schema_name(o.schema_id) like @table_owner)
order by 4, 1, 2, 3
end
版权声明：本文为blodfox777原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。