Find Size of the Indexes for Indexed Views

Hello all,

I have several scripts in my tool box to look into the indexes for tables, but did not have one for indexed views.  I wanted to share a script that return the size of clustered and non clustered indexes for indexed views if exist in the context of the database where the script is executed.

HTH,
Bulent

SELECT
      OBJECT_SCHEMA_NAME(v.object_id) AS 'SchemaName'
    , v.NAME AS 'ViewName'
    , i.index_id
    , i.name AS 'IndexName'
    , p.rows AS 'RowCounts'
    , SUM(a.total_pages) * 8 AS 'TotalSpaceKB'
    , SUM(a.used_pages) * 8 AS 'UsedSpaceKB'
    , SUM(a.data_pages) * 8 AS 'DataSpaceKB'
FROM    sys.views AS v
    INNER JOIN sys.indexes AS i ON v.OBJECT_ID = i.object_id
    INNER JOIN sys.partitions AS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
GROUP BY v.object_id, v.NAME, i.object_id, i.index_id, i.name, p.Rows;