How to retrieve size information for all the tables in a SQL Server Database

In an older post, I described how you could rebuild all the indexes of a database in SQL Server by making use of the undocumented stored procedure "sp_MSforeachtable".

Another common task is when you want to retrieve size information for all the tables in a database.

Again, by using "sp_MSforeachtable", you can easily do that in three simple steps:

--Step 1:
--Create temporaty table for the session
create table #tblInfo(
[name] nvarchar (255),
rows int,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)

--Step 2:
--Using the stored procedure sp_spaceused retrieve
--the size information for all tables and store it in the temporary table
EXEC sp_MSforeachtable @command1="INSERT #tblInfo EXEC sp_spaceused '?'";

--Step 3:
--Access the results
select * from #tblInfo;

Note: Because sp_spaceused returns the size information as a string (except the number of rows), you will have to manipulate the data in the temporary table using casting prior to run sorting operations etc.


--Sort the results by unused space (descending)
select * from #tblInfo
order by cast(substring(unused,0,charindex(' ',unused)) as int) desc;

--Sort the results by reserved space (descending)
select * from #tblInfo
order by cast(substring(reserved,0,charindex(' ',reserved)) as int) desc;

Additionally, you can create another temporary table which can contain the converted values (i.e. in MB instead of KB) of the first table.

For examnple:

select [name],
cast(substring(reserved,0,charindex(' ',reserved)) as int)/1024 as reserved_in_MB,
cast(substring(data,0,charindex(' ',data)) as int)/1024 as data_in_MB,
cast(substring(index_size,0,charindex(' ',index_size)) as int)/1024 as index_size_in_MB,
cast(substring(unused,0,charindex(' ',unused)) as int)/1024 as unused_in_MB
into #tblInfoConverted
from #tblInfo;

--and ... voila!
select *
from #tblInfoConverted
order by reserved_in_MB desc;

Until next time!

Labels: , , , , ,