How do I check the size of an indexed view in SQL Server?

Question

Its easy to check storage sizes for Tables and Indexes, you can right-click the table object on SSMS explorer and voila, the details appear in a nice popup.

But since Indexed Views are displayed the same as Normal Views, there is no storage information avaiable in SSMS to show me the current size taken up on disk.

Is there an alterate way to calculate the size (say via a system SP or similar method)?

Thanks.

Dissolve

Answer 1

EXEC sys.sp_spaceused @objname = N'dbo.YourView'

Weinman

Answer 2

You can use this query here to find your data for any given indexed view:

SELECT 
    v.NAME AS ViewName,
    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 v
INNER JOIN      
    sys.indexes i ON v.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    v.Name = 'YourViewNameHere' --View name only, not 'schema.viewname'
    AND
    i.index_id = 1   -- clustered index, remove this to see all indexes
GROUP BY 
    v.NAME, i.object_id, i.index_id, i.name, p.Rows

Gives an output something like

ViewName      IndexName     RowCounts  TotalSpaceKB  UsedSpaceKB  DataSpaceKB
YourViewName  IX_YourView     1771         592           552          536

Complaisance