SQL Server - T-SQL to get space usage details for all the tables in a database

In continuation to my previous blog, which was for database space usage, today I'll be writing about another most seeked similar information, which is table space usage details within a given database.

SQL Server by default has provided a very good stored procedure named "sp_spaceused". This SP provides details like number of rows currently in a table along with space usage. The only problem with it is that it shows value for only one object at a time. In today's script we will see how by adding just few more statements we can use the same SP to collect information for all the tables.

--===========================================================

create table #temp(table_name varchar(500), no_of_rows varchar(max), reserved varchar(max), data_pages varchar(max),

index_size varchar(max), unused_pages varchar(max))

insert into #temp

exec sp_msforeachtable 'exec sp_spaceused''?'''

select * from #temp

select table_name,CONVERT(bigint,no_of_rows) "no_of_rows", CONVERT(bigint,substring(reserved,1,LEN(reserved)-3)) "reserved",

CONVERT(bigint,substring(data_pages,1,LEN(data_pages)-3)) "data_pages", CONVERT(bigint,substring(index_size,1,LEN(index_size)-3)) "index_size",

CONVERT(bigint,substring(unused_pages,1,LEN(unused_pages)-3)) "unused_pages"

 into #temp2 from #temp

select * from #temp2 --all information as is

select table_name, (data_pages + index_size + unused_pages)/1024 "table_size_in_MB" from #temp2 order by 2 desc --total table space in descending order of size

drop table #temp

drop table #temp2

--===========================================================

"sp_spaceused" stored procedure have one more parameter "@updateusage", which updates the usage stats of the object as well to show the current usage values. This parameter takes values as "TRUE" or "FALSE". We can also add this parameter in our script to have more precise usage values but it will make the things bit slower.