Find the size of Index in SQL Server

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.Thats where the DMF sys.dm_db_index_physical_stats comes handy.

This DMF will return lot of values but if the last parameter to the DMF is 'detailed' then you get two columns that can be used to find the size of each index.They  are avg_record_size_in_bytes and record_count.
If these columns are multiplied the resultant is the size of  that index.

The query given below returns the name of Database, ObjectId, Objectname, IndexId, IndexDescription, Size of Index in MB, Last Updated Statistics Date and Avg Fragmentation.

SELECT DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, CONVERT(DECIMAL(16,1), (SUM(avg_record_size_in_bytes * record_count)/ (1024.0 *1024))) AS  [Size of Index(MB)], last_updated AS [Statistic last Updated], Avg_Fragmentation_In_Percent FROM (SELECT DISTINCT DB_Name(Database_id) AS 'DatabaseName', OBJECT_ID AS ObjectId, Object_Name(Object_id) AS ObjectName, Index_ID AS IndexId, Index_Type_Desc AS Index_Description, avg_record_size_in_bytes , record_count, STATS_DATE(object_id,index_id) AS 'last_updated', Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) AS 'Avg_Fragmentation_In_Percent' FROM sys.dm_db_index_physical_stats(db_id('PM_Db'), NULL, NULL, NULL, 'detailed') Where Object_id IS NOT NULL AND Avg_Fragmentation_In_Percent <> 0) T GROUP BY DatabaseName, ObjectId, ObjectName, IndexId, Index_Description, last_updated, Avg_Fragmentation_In_Percent