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?
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:
You can open it by selecting the table folder in the database in Object Explorer and then pressing
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:
You can use sp_spaceused stored procedure:
EXECUTE sp_spaceused 'Person.Person' EXECUTE sp_spaceused 'Person.Address'
Gives you this:
Hope this is what you're looking for