SpaceUsed All Tables

spaceused_all_tables_in_db.sql

SELECT   
	o.name as 'TableName' ,
	SUM (  
	CASE  
		WHEN (index_id < 2) THEN row_count  
		ELSE 0  
	END  
	) as 'RowCount',
	
	LTRIM (STR (SUM (reserved_page_count)/1024 * 8, 15, 0) + ' MB') as'Reserved MB',
	
	LTRIM (STR (SUM (CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END)
	 * 8 /1024, 15, 0)+ ' MB') as 'Data MB',
	
	
	LTRIM (STR (
		(CASE WHEN 
		SUM (used_page_count) > SUM (  
	CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END  
	) THEN (SUM (used_page_count) - SUM (  
	CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END  
	)) 
	ELSE 0 END) * 8 /1024, 15, 0) + ' MB') as	'IndexSize MB',
	
	
	
	LTRIM (STR ((CASE WHEN SUM (reserved_page_count) > SUM (used_page_count) THEN (SUM (reserved_page_count) - SUM (used_page_count)) ELSE 0 END) * 8/1024, 15, 0) + ' MB') as 
	 'UNUSED MB',
	
	LTRIM (STR (SUM (reserved_page_count) * 8, 15, 0) + ' KB') as'Reserved',
	
	LTRIM (STR (SUM (CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END)
	* 8, 15, 0) + ' KB') as 'Data',
		
	LTRIM (STR (
		(CASE WHEN 
		SUM (used_page_count) > SUM (  
	CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END  
	) THEN (SUM (used_page_count) - SUM (  
	CASE  
	    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)  
		ELSE lob_used_page_count + row_overflow_used_page_count  
	END  
	)) 
	ELSE 0 END) * 8, 15, 0) + ' KB') as	'IndexSize',
	
	
	LTRIM (STR ((CASE WHEN SUM (reserved_page_count) > SUM (used_page_count) THEN (SUM (reserved_page_count) - SUM (used_page_count)) ELSE 0 END) * 8, 15, 0) + ' KB') as 
	 'UNUSED'
	
		
 FROM sys.dm_db_partition_stats p
 inner join sysobjects o on  p.object_id=o.id
 WHERE object_id in (select ID  from sysobjects where xtype = 'u')
 group by  o.name
 order by STR (SUM (reserved_page_count)/1024 * 8, 15, 0)desc