Size and row count of tables in a SQL server

The code creates a temp tables with the total number of rows and size in KB for all the tables in the current SQL server catalog.


declare @TableName nvarchar(1000)

begin try
drop table #TblSpace
end try
begin catch
end catch

create table #TblSpace
(
[name] nvarchar(1000),
[rows] bigint,
reserved nvarchar(1000),
data nvarchar(1000),
index_size nvarchar(1000),
unused nvarchar(1000)
)

declare cur cursor for
select
cast('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' as nvarchar(1000)) as TableName
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'

open cur

fetch next from cur into @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

insert into #TblSpace
EXEC sp_spaceused @TableName

fetch next from cur into @TableName
end

close cur
deallocate cur

select * from #TblSpace order by [rows] desc