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

Question

It is easy to check the storage sizes for tables and indexes, you can right-click the table object in the SSMS and voila explorer, details will appear in a nice pop-up window.

But since indexed views are displayed in the same way as Normal Views, there is no storage information in SSMS to show me the current size taken to disk.

Is there a way to calculate the size (for example, using a system SP or a similar method)?

Thanks.

Answer 1

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

Answer 2

You can use this query here to find your data for any specified 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 a result similar to

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