The undocumented stored procedure sp_msforeachtable is here to help you (along with the stored procedure sp_spaceused)!
–Select the database to be scanned for table disk usage
–Create temporary table 1 – Sizes will be in strings/KB
create table #tmpSizes(
–Create temporary table 1 – Sizes will be in KB
create table #tmpSizesFinalKB(
–Get the disk usage per table and store in temp table 1
INSERT INTO #tmpSizes
EXEC sp_MSforeachtable @command1=”sp_spaceused ‘?'”
–Indirect casting and copying of the information in temp table 2
–This is only when you want to store the disk usage statistics
–in a form that allows sorting operations etc.
insert into #tmpSizesFinalKB
–Access the disk usage results (in KB)
order by reserved desc
Now you can easily manipulate the disk usage statistics in temp table #tmpSizesFinalKB in order to represent them in the form you may like!
Useful? Please comment/like/tweet!
My Latest Projects:
- DBA Security Advisor: Secure your SQL Server instances against security risks.
- In-Memory OLTP Simulator: Easily benchmark SQL Server’s In-Memory OLTP Engine against your custom workload.
- Artemiou SQL Books: Download my latest free books on SQL Server.
- Artemiou Data Tools: See my latest software projects.