Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a group of about 30 tables, and I want to know the physical size on disk of all of these tables (plus indexes).

Is there an easier way of doing this than through the GUI in SQL server 2008 R2?

share|improve this question
up vote 9 down vote accepted

If you are looking to get the size information for all tables in the database you can use this query:

SELECT O.type_desc, 
       IndexSize.obj_name, AS index_name,
  FROM (
           SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
           SUM(DDPS.used_page_count)/128.0 AS used_MB,
           SUM(row_count) AS row_count,
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   JOIN sys.objects AS O
     ON IndexSize.object_id = O.object_id
   JOIN sys.indexes AS I
     ON IndexSize.object_id = I.object_id
    AND IndexSize.index_id = I.index_id;

It returns the reserved megabytes and the used megabytes as well as the row count for every index in the database. The first column tells you the object type. This includes system_tables as well as indexed views. If you want a subset, filter on that column.

If you do not need the per-index detail you can use this query instead:

SELECT O.type_desc,
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
           SUM(IndexSize.reserved_MB) AS reserved_MB, 
           SUM(IndexSize.used_MB) AS used_MB, 
           MAX(IndexSize.row_count) AS row_count, 
        SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
               SUM(DDPS.used_page_count)/128.0 AS used_MB,
               SUM(row_count) AS row_count,
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
     GROUP BY IndexSize.object_id
  JOIN sys.objects AS O
    ON ObjectSize.object_id = O.object_id

It gives the same information, but on an object basis. Again, filter on the object type if you are for example not interested in system_tables.

If you are just interested in normal tables, you can get the information in SSMS too by using the "Object Explorer Details" tab:

enter image description here

You can open it by selecting the table folder in the database in Object Explorer and then pressing F7.

You might have to add the size columns. For that just right-click on the column list and select the columns you want to see:

enter image description here

share|improve this answer

You can use sp_spaceused stored procedure:

For example:

EXECUTE sp_spaceused 'Person.Person' 
EXECUTE sp_spaceused 'Person.Address'

Gives you this:

enter image description here

Hope this is what you're looking for

share|improve this answer
This is better sp_spaceused Alternative (…) – Kin Nov 2 '13 at 19:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.