S

sp_spaceused gives the size of table and index but it gives the sum of size of all indexes on a table.
What if you need to capture size of individual index on a table?

It is very easy to find out some basic details of any table using the following Stored Procedure.

Above query will return following resultset

sql-server-index-size-01

 

The above Stored Procedure provides basic details such as rows, data size in table, and Index size of all the indexes on the table.

If we look at this carefully, a total of three indexes can be found on the table HumanResources.Shift.

The above query will give result with query listing all the index on the table.

sql-server-index-size-02

 

We need a query that will return an additional column in the above listed query and it should contain the size of the index.
we will have three different sizes, which should add up to a total of 40 KB as shown in earlier query, where the total size is displayed.

I have done some good amount of testing of both the script and they both are really giving accurate results 99.9% of the time. There were couple of instances where it provided incorrect results but for the most of the time, it just worked like charm. Again, it may be a bit of rounding up logic and I will not hold it against the script.

Only Index names and sizes for a table

The result is like this

sql-server-index-size-03

All Index information for a table

The result is like this

sql-server-index-size-04

All Indexes in database
Uses sys.indexes and sys.dm_db_partition_stats DMF to calculate the size of individual index on a table.

The result is like this

sql-server-index-size-06

All tables and all related Indexes in database

The result is like this

sql-server-index-size-05

All Object names, Index descriptions, Index sizes and fragmentation percentages in database
Uses sys.dm_db_index_physical_stats and sys.dm_db_partition_stats to calculate the size of individual index on a table. This query is more reliable as compared to first query because it uses DMFs

The result is like this

sql-server-index-size-07

 

I hope this information is useful for you