用SQL Script 取得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