用SQLScript取得Database中所有Table的记录数及大小
主要用于需要知道Databases 中那些表占用太多的空间时， 可建一个SP 然后执此SP即可列出。
create procedure dbo.sp_ListAllTableSize
as
set nocount on
Declare @vSQLStatement varchar(100)
Declare @vTableName varchar(100)
Declare @vTableName1 varchar(100)
Declare @vCount int
Select @vCount=1
If Not Object_ID(N'tempdb.dbo.##tablesize') is Null
drop table ##TableSize
create table ##TableSize
(
TableName sysname,
Total_rows int,
reserved_size varchar(100) ,
data_size varchar(100) ,
index_size varchar(100) ,
unused_size varchar(100)
)
Declare @cursorAllTableName cursor
Set @cursorAllTableName = cursor for
select name from sysobjects where type='U' Order by Name Desc
Open @cursorAllTableName
Fetch next from @cursorAllTableName Into @vTableName
set nocount OFF
While @@Fetch_Status=0
Begin
Select @vTableName1 = Upper(Ltrim(Rtrim(@vTableName)))
select @vSQLStatement = ' Insert into ##TableSize exec sp_Spaceused ['+@vTableName1 + '] '
exec (@vSQLStatement)
Fetch next from @cursorAllTableName Into @vTableName
Select @vCount=@vCount+1
End
Close @cursorAllTableName
Deallocate @cursorAllTableName
Set NoCount OFF
select TableName , Total_rows ,
Reserved = convert(int , replace(Reserved_Size, 'KB' , '' ) ) ,
Data = convert(int , replace(data_size, 'KB' , '' ) ) ,
Indexes = convert(int , replace(Index_Size, 'KB' , '' ) ) ,
Unused = convert(int , replace(Unused_Size, 'KB' , '' ) ) ,
Unit = 'KB'
from ##TableSize
order by Reserved desc
3COME考试频道为您精心整理，希望对您有所帮助，更多信息在http://www.reader8.net/exam/
