SQL Server - Table sizes and percentages

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

CREATE TABLE #temp(

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

SELECT *

FROM #temp

ORDER BY total_size DESC

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

DROP TABLE #temp

--------------------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