分析ms数据库的用户表数和记录数

近来一个项目要求做几个数据库的比较，其中有几项指标和数据库的表有关：

用户表个数

最大表记录数

记录总条数

如果靠手工一个表一个表的去查看统计，不仅枯燥费时，而且灵活性和扩展都不是很好，可能主要还是想偷懒的原因吧，今天花了点时间写了个简单的存储过程，只是为了快点完成任务，还没得及考虑方便性和处理错误，下面是源代码，原理很简单，参看代码中注释。

create procedure sp_tableCount

@newTable varchar(50),--new create table name

@isSet int --whether return new table recordset,non 0--return

as

declare @TableName nvarchar(100);

declare @sql nvarchar(800)

SET NOCOUNT ON

----create a target table named @newTable param value--------

IF EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES

WHERE table_name = @newTable)

exec('DROP TABLE '+@newTable)

-----create target table------------

set @sql='create table ' + @newTable + '

(

Categary nvarchar(100) not null,

Value int

)'

exec(@sql)

----------add user tables count into target table----------------

set @sql='insert into '+@newTable+' select ''User Tables'',count(*)-1 from sysobjects where type=''U'''

exec(@sql)

--------define a cursor pointing the user tablename recordset--------

declare TableName_Cursor CURSOR FOR

select name from sysobjects where type='U'and name<>@newTable

open TableName_Cursor

fetch next from TableName_Cursor into @TableName

-------append every user table recordcount to target table----------------

while @@Fetch_Status=0

begin

set @sql='insert into '+@newTable+' select N'''+@TableName+''',count(*) from ' + @TableName

exec(@sql)

fetch next from TableName_Cursor into @TableName

end

-------release resource occupied by TableName_Cursor --------

close TableName_Cursor

deallocate TableName_Cursor

--------deal with the @isSet param-----------------

if @isSet<>0

exec('select * from '+@newTable)