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
(128),
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]
from
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
begin
fetch
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)
end;
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.

2 comments:

Catherine said...

Alternatively

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

and grab the first column result set

Diego said...

Alternatively:
sp_MSforeachtable 'sp_spaceused ''?'''