統計資料庫中表大小

qextoqoha發表於2019-09-19

use testdb
go
if object_id('tempdb.dbo.#tablespaceinfo','U') is not null
  drop table #tablespaceinfo
create table #tablespaceinfo (  
    nameinfo varchar(555),  
    rowsinfo bigint,  
    reserved varchar(255),  
    datainfo varchar(255),  
    index_size varchar(255),  
    unused varchar(255)  
)  
 
DECLARE @tablename varchar(255);  
 
DECLARE Info_cursor CURSOR FOR
    SELECT [name] FROM sys.tables WHERE type='U';  
 
OPEN Info_cursor  
FETCH NEXT FROM Info_cursor INTO @tablename  
 
WHILE @@FETCH_STATUS = 0  
BEGIN
    insert into #tablespaceinfo exec sp_spaceused @tablename  
    FETCH NEXT FROM Info_cursor  
    INTO @tablename  
END
 
CLOSE Info_cursor  
DEALLOCATE Info_cursor  
 
if object_id('tempdb.dbo.#tab','U') is not null
  drop table #tab
SELECT
 nameinfo
 ,rowsinfo
 ,cast(replace(reserved,' KB','') as bigint)/1024 "reserved(MB)"
 ,cast(replace(datainfo,' KB','') as bigint)/1024 "datainfo(MB)"
 ,cast(replace(index_size,' KB','') as bigint)/1024 "index_size(MB)"
 ,cast(replace(unused,' KB','') as bigint)/1024 "unused(MB)"
into #tab
FROM #tablespaceinfo  
ORDER BY Cast(Replace(reserved,'KB','') as INT) DESC

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31552801/viewspace-2657560/,如需轉載,請註明出處,否則將追究法律責任。

相關文章