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

Author: Jack Vamvas