如何检查SQL Server中索引视图的大小？

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.

它很容易检查表和索引的存储大小，您可以右键单击SSMS资源管理器上的表对象，然后将详细信息显示在一个漂亮的弹出窗口中。

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.

但由于索引视图显示与普通视图相同，因此SSMS中没有可用的存储信息来向我显示磁盘上占用的当前大小。

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

有没有改变的方法来计算尺寸（比如通过系统SP或类似的方法）？

Thanks.

谢谢。

2 个解决方案

#1

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

#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