A Useful Script to Get The Disk Space Used by Every Table

January 12th, 2008 by Andrew Chen

Disk space arrangement is something that a database administrator needs to deal with when maintaining large database

archiving historical data. Historical data consume huge amount of disk space. Every database table takes up different amount of disk space and sometimes the indexes of a table consume more disk space then its data. Not every database table that stores historical data are frequently query on. So for those tables that will be query on occasionally it is better to take out its indexes to save space. The indexes are created at times when a specific report is run. Once a while I have to find out which table consumes the most disk space so I wrote the following script to display the disk space usage for every table in descending order. I think it is very useful sometime.


set nocount on
create table #tem1 ( seq int identity(1,1),

		     [rows] int,

		     DataSpaceUsed float,

		     IndexSpaceUsed float,

	             TotalSpaceUsed  float

		    )  
create table #tem2 ( seq int identity(1,1),

		     tablename varchar(50)

		    )
declare @tablename varchar(50)

declare tablecr cursor for

	select 	user_name(uid)+'.'+name

	from 	sysobjects

	where 	objectproperty(id,'isTable')=1

		and objectproperty(id,'isUserTable')=1

open tablecr

fetch tablecr into @tablename

while @@fetch_status=0

begin

       insert into #tem2 (tablename) values (@tablename)

       insert into #tem1 ([rows], DataSpaceUsed, IndexSpaceUsed)

		exec sp_mstablespace @tablename

       fetch next from tablecr into @tablename

end

close tablecr

deallocate tablecr
select  b.TableName,

        str((a.DataSpaceUsed)/1024.0/1024.0,6,6) as [DataSpaceUsed G],

        str((a.IndexSpaceUsed)/1024.0/1024.0,6,6) as [IndexSpaceUsed G],

        str((a.DataSpaceUsed+a.IndexSpaceUsed)/1024.0/1024.0,6,6) as [TotalSpaceUsed G]

into    #tem3

from    #tem1 a, #tem2 b where a.seq=b.seq

union

select 'Total',

       str(sum(a.DataSpaceUsed)/1024.0/1024.0,6,6) as [DataSpaceUsed G],

       str(sum(a.IndexSpaceUsed)/1024.0/1024.0,6,6) as [IndexSpaceUsed G],

       str(sum(a.DataSpaceUsed+a.IndexSpaceUsed)/1024.0/1024.0,6,6) as [TotalSpaceUsed G]

from   #tem1 a, #tem2 b where a.seq=b.seq 
select * from #tem3 order by convert(float,[TotalSpaceUsed G]) desc
drop table #tem1

drop table #tem2

drop table #tem3