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.
