--Main principle:
EXEC sp_spaceused ' table name '--get table occupy space
EXEC sp_spaceused '--all the space in the repository
CREATE TABLE #Data (name varchar), row varchar (+), reserved varchar (+), Data varchar (+), index_size varchar (100) , unused varchar (100))
DECLARE @name varchar (100)
DECLARE cur cursor FOR
Select name from sysobjects where xtype= ' u ' ORDER by name
Open cur
FETCH NEXT from cur to @name
While @ @fetch_status =0
Begin
INSERT INTO #data
EXEC sp_spaceused @name
Print @name
FETCH NEXT from cur to @name
End
Close cur
Deallocate cur
CREATE TABLE #DataNew (name varchar, row int,reserved int,data int,index_size int,unused int)
INSERT INTO #dataNew
Select Name,convert (Int,row) as Row,convert (Int,replace (reserved, ' KB ', ")) as Reserved,convert (int,replace (data, ' kb ', ') as data,
Convert (Int,replace (index_size, ' KB ', ')) as Index_size,convert (Int,replace (Unused, ' KB ', ")) as unused from #data
SELECT * from #dataNew ORDER BY data desc
SQL Server View all table sizes, occupied space