用SQLScript取得Database中所有Table的記錄數及大小
主要用於了解數據庫中哪些表佔用了太多空的空間,這樣就可以建立一個SP,然後持有這個SP來列出它們。
create procedure dbo . sp _ ListAllTableSize
as
set no count on
Declare @ vsql statement varchar(100)
Declare @ vTableName varchar(100)
Declare @ vTableName 1 varchar(100)
Declare @ vCount int
Select @ vCount = 1
If Not Object _ ID(N ' tempdb . dbo
unused _ size varchar(100)
)
Declare @ cursorall TableName cursor
Set @ cursorall TableName = cursor for
Select Name from sysobjects where type = ' U ' Order by Name desc
Open @ cursorall TableName
Fetch next from @ cursorall TableName Into @ vTableName
Set no count OFF
While @ @ Fetch _ Status = 0[/br 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保畱的desc
0條評論