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.
Examples:
--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],
rows,
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!