Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

2 Answers 2

up vote 8 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,
       I.name AS index_name,
       IndexSize.reserved_MB,
       IndexSize.used_MB,
       IndexSize.row_count,
       IndexSize.object_id,
       IndexSize.index_id
  FROM (
    SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
           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,
           DDPS.object_id,
           DDPS.index_id
      FROM sys.dm_db_partition_stats AS DDPS
     GROUP BY DDPS.object_id,DDPS.index_id
   )IndexSize
   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,
       ObjectSize.obj_name,
       ObjectSize.reserved_MB,
       ObjectSize.used_MB,
       ObjectSize.row_count
  FROM( 
    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, 
           IndexSize.object_id
      FROM(
        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,
               DDPS.object_id
          FROM sys.dm_db_partition_stats AS DDPS
         GROUP BY DDPS.object_id,DDPS.index_id
      )IndexSize
     GROUP BY IndexSize.object_id
  )ObjectSize
  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
add comment

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 (sqlblog.com/blogs/andrew_kelly/archive/2013/05/17/…) –  Kin Nov 2 '13 at 19:32
add comment

Your Answer

 
discard

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.