分析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 oclearcase/" target="_blank" >ccupied by TableName_Cursor --------
close TableName_Cursor
deallocate TableName_Cursor

--------deal with the @isSet param-----------------
if @isSet<>0
exec('select * from '+@newTable)