分析MSSQL数据库的用户表数和记录数（转载）

最新推荐文章于 2021-08-27 10:35:55 发布

转载最新推荐文章于 2021-08-27 10:35:55 发布 · 50 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/starcrm/archive/2008/10/13/1309844.html

文章标签：

#数据库

本文介绍了一个SQL存储过程，用于创建新表并统计用户表的数量。该过程还会遍历所有用户表，将每个表的记录数量插入到新创建的表中。最后，根据参数决定是否返回新表的所有记录。

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)

转载于:https://www.cnblogs.com/starcrm/archive/2008/10/13/1309844.html