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(1FROM LDMMessage;
    
INSERT INTO @RESULT_TABLE SELECT 'DCSFile',COUNT(1FROM DCSFile;
    
INSERT INTO @RESULT_TABLE SELECT 'SSRCode',COUNT(1FROM SSRCode;
    
INSERT INTO @RESULT_TABLE SELECT 'PRLMessage',COUNT(1FROM 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.SYSOBJECTSSYS.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 

 

[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 nulland (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_nameand
                        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= 0and
            (
isnull(charindex('_'@table_name),0= 0and
            (
isnull(charindex('%'@table_owner),0= 0and
            (
isnull(charindex('_'@table_owner),0= 0and
            (
@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 4123
    
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_nameand
            (
@table_owner is NULL or schema_name(o.schema_id) like @table_owner)
        
order by 4123
    
end

 

 

 

posted @ 2010-10-27 14:11 LanceZhang 阅读(...) 评论(...) 编辑 收藏