Table and Index size in SQL Server

Question

Can we have a SQL query which will basically help in viewing table and index sizes in SQl Server.

How SQL server maintains memory usage for tables/indexes?

Answer 1

sp_spaceused gives you the size of all the indexes combined.

If you want the size of each index for a table, use one of these two queries:

SELECT     i.name                  AS IndexName,     SUM(s.used_page_count) * 8   AS IndexSizeKB FROM sys.dm_db_partition_stats  AS s  JOIN sys.indexes                AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.TableName') GROUP BY i.name ORDER BY i.name  SELECT     i.name              AS IndexName,     SUM(page_count * 8) AS IndexSizeKB FROM sys.dm_db_index_physical_stats(     db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id GROUP BY i.name ORDER BY i.name 

The results are usually slightly different but within 1%.