用SQLScript取得Database中所有Table的記錄數(shù)及大小

主要用于需要知道Databases 中那些表占用太多的空間時(shí)， 可建一個(gè)SP 然后執(zhí)此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