Friday, 26 January 2007

TSQL Script to get the space used by every table in a SQL Server 2005 database

Here is a TSQL script (developed, tested and used only on Microsoft SQL Server 2005) to get the space used by the tables of a database.
declare @sql nvarchar(MAX)
create table #usedSpace (
name nvarchar
rows varchar(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18))

declare @table nvarchar(MAX)
declare tabcur cursor for
select s.[name] +'.'+ t.[name] as [name]
sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id
order by
s.[name], t.[name];

open tabcur;
fetch next from tabcur into @table;
while @@FETCH_STATUS = 0
next from tabcur into @table;

select @sql = 'sp_executesql N''insert #usedSpace exec sp_spaceused ''''' + @table + ''''''''
-- print @sql -- uncomment if you wish to read the sql statements
exec (@sql)
close tabcur;
deallocate tabcur;
-- select * from #usedSpace order by cast((left(Data,len(Data)-3)) as int) desc -- uncomment if you wish to get a recordset
drop table #usedSpace

Use it at your own risk, uncomment the select (or substitute that with your more appropriate log strategy) and read about sp_spaceused and above all dbcc updateusage before bitching about the quality of the results.


Catherine said...


select 'exec sp_spaceused ' + table_name, table_name
from information_schema.tables
select 'go', table_name
from information_schema.tables
order by 2, 1

and grab the first column result set

Diego said...

sp_MSforeachtable 'sp_spaceused ''?'''