MSSQL 2005: Calculate Indexes Size via dm_db_index_physical_stats

Sometimes we want to calculate how much space do table indexes get. We can use sp_spaceused system stored procedure to measure how much does specific object take physical space. In MSSQL 2005 we can also use system procedure sys.dm_db_index_physical_stats. via Davide Mauri:

"This little snip of code allows you to list how many indexes are present on a table, their type and how big (kb and mb) they are"

SELECT [name]
      ,type_desc
      ,space_used_in_kb = (page_count * 8.0)
      ,space_used_in_mb = (page_count * 8.0 / 1024.0) 
  FROM sys.indexes I
  JOIN sys.dm_db_index_physical_stats(db_id()
                                     ,object_id('.')
                                     ,null
                                     ,null
                                     ,null) P 
    ON I.[object_id] = P.[object_id] 
   AND I.[index_id] = P.[index_id]

Related Posts:

• T-SQL Query: Select Middle Record
• Yukon: New Features in T-SQL
• T-SQL: Leap Year Check
• HowTo Convert Table Values to Comma-Delimited String
• T-SQL: Generate Sequences in a Loop Query
• T-SQL: Optional Parameters in Select Query (Using ISNULL)
• MSSQL Tip: Get Primary Table in Relationship
• MSSQL Tip: Get Table Primary Key Column(s)
• ROWNUM [ PL/SQL ] Analog for T-SQL
• MSSQL Tip: Get Connected Columns in Related Tables