Home > Joins, SQL Queries, SQL Server, Storage Space, Stored Procedures, T-SQL > Get the consolidated size of all tables in a database.

Get the consolidated size of all tables in a database.

The first thing that goes through your mind might be ” why cant we SP_SPACEUSED instead ? “. You are right. However , it will result in a row by row operation rather than a set operation. This will be a major headache in the usual production environment with large number of tabls and huge data. The below query can give you the result in few seconds. The step 2 can be avoided by careful coding . I have included update statement for easier understanding of the code. 

SELECT OBJECT_ID
 , SUM (RESERVED_PAGE_COUNT) RESERVEDPAGES
 , SUM (USED_PAGE_COUNT) USEDPAGES
 , 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 )PAGES, SUM ( CASE WHEN (INDEX_ID < 2) THEN ROW_COUNT ELSE 0 END ) AS [ROWCOUNT]  
 INTO   #SIZE  
 FROM  SYS.DM_DB_PARTITION_STATS 
 GROUP BY OBJECT_ID     

UPDATE #SIZE  
SET RESERVEDPAGES= (RESERVEDPAGES + RESERVED_PAGE_COUNT)

 ,USEDPAGES = (USEDPAGES + USED_PAGE_COUNT)  
 FROM
#SIZE T
 ,SYS.DM_DB_PARTITION_STATS P
 ,SYS.INTERNAL_TABLES IT  
WHERE T.OBJECT_ID=IT.PARENT_ID AND IT.INTERNAL_TYPE IN (202,204) AND P.OBJECT_ID = IT.OBJECT_ID   
 
 
SELECT  DATASIZEINKB= SUM(PAGES) * 8 , INDEXSIZEINKB = SUM((CASE WHEN USEDPAGES > PAGES THEN (USEDPAGES PAGES) ELSE 0 END)) * 8, UNUSEDSIZEINKB = SUM((CASE WHEN RESERVEDPAGES > USEDPAGES THEN (RESERVEDPAGES USEDPAGES) ELSE 0 END)) * 8
FROM #SIZE  
 

DROP TABLE #SIZE 

 

 pardon me for the allignments, as i could not get it right :).

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: