SQL Server中统计各用户表记录条数的两种方法

日期： 2010-10-28 作者：blodfox777 来源：TechTarget中国 英文

　　最近做数据监控遇到这么个查询需求，就从系统存储过程[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

　　写完之后，感觉毕竟使用到了游标和表变量，性能不太理想，应该还有更好的方法，便google了一下，发现也可以从系统视图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个时间片，性能大为胜出。

　　[sys].[sp_tables]
　　/****** 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

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

blodfox777
blodfox777

相关推荐