存储过程 统计当前库中所有表的记录数

统计当前库中所有表的记录数量：

在 MSSQL2008 R2中运行

if exists (select Name from sysobjects where name='count_table_recorder' and type='p')  drop proc count_table_recorder

create proc count_table_recorder
    @DbName varchar(255),
    @TableType nvarchar(10),
    @OneResult nvarchar(4000) output
AS
    declare table_cursor cursor for
        select name from sys.all_objects where type=@TableType
    
    set @OneResult=''
    
    declare @tableName varchar(255)
    open table_cursor
    
    declare @i int
    set @i=1;
    
    fetch next from table_cursor into @tableName
        while(@@FETCH_STATUS=0)
            begin 
                --select @OneResult = @OneResult + @tableName + ':' + CONVERT(nvarchar(50),COUNT(*)) from @tableName
                declare @sql nvarchar(1000)
                declare @num int
                set @sql='select @tmp=count(id) from ['+@tableName+']'
                exec sp_executesql @sql,N'@tmp int output',@num output
                select @num as columnName
                set @OneResult =@OneResult + CONVERT(nvarchar(5),@i)+'--' +@tableName+' : ' + CONVERT(nvarchar(10),@num)+'\n'
                set @i+=1
                fetch next from table_cursor into @tableName
            end
    
    close table_cursor
    deallocate table_cursor
    
GO

                declare @result varchar(4000)
                execute count_table_recorder 'demo','u',@result output//第一个参数为database，可以不用，随便写个，当时没去掉
                select @result