Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

SQL Server - Table sizes and percentages

09 April,2010 by Tom Collins

SQL Script  script that returns all user tables in a database : sizes and percentages of totals

------------------SCRIPT ----------START-------------------------

-- Create the temp table for further querying


tbl_id int IDENTITY (1, 1),

tbl_name varchar(128),

rows_num int,

data_space decimal(15,2),

index_space decimal(15,2),

total_size decimal(15,2),

percent_of_db decimal(15,12),

db_size decimal(15,2))

-- Get all tables, names, and sizes

EXEC sp_msforeachtable @command1="insert into #temp(rows_num, data_space, index_space) exec sp_mstablespace '?'",

@command2="update #temp set tbl_name = '?' where tbl_id = (select max(tbl_id) from #temp)"

-- Set the total_size and total database size fields

UPDATE #temp

SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)

-- Set the percent of the total database size

UPDATE #temp

SET percent_of_db = (total_size/db_size) * 100

-- Get the data


FROM #temp

ORDER BY total_size DESC

-- Comment out the following line if you want to do further querying


--------------------SCRIPT FINISH----------------------------------------------

Related Posts

SQL Server – Last DML operation

SQL Server - Calculate table size with existing data

How to request SQL Server troubleshooting

SQL Server – default trace FAQ

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server - Table sizes and percentages | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer