set nocount on
DECLARE @tablename VARCHAR(250)
DECLARE db_cursor CURSOR FOR
select table_schema+'.'+table_name
from information_schema.tables
where table_type='BASE TABLE'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqltableusage
TABLE(name varchar(250),rows int,reserved varchar(20),
data varchar(20), index_used varchar(20), unused varchar(20))
insert into @sqltableusage
Exec sp_spaceused @tablename
FETCH NEXT FROM db_cursor INTO @tablename
END
CLOSE db_cursor
DEALLOCATE db_cursor
--select * from @sqltableusage
select name,rows,replace(reserved,'KB','') Reserved_KB,replace(data,'KB','') Data_KB,
replace(index_used,'KB','') Index_Used_KB,replace(unused,'KB','') Unused_KB,
convert(decimal(14,2),(replace(reserved,'KB','')))/1024 as Reserved_MB,
convert(decimal(14,2),(replace(data,'KB','')))/1024 as Data_MB,
convert(decimal(14,2),(replace(index_used,'KB','')))/1024 as Index_Used_MB,
convert(decimal(14,2),(replace(unused,'KB','')))/1024 as Unused_MB
from @sqltableusage
