非聚集索引查询

来源:互联网 发布:网络运营推广新存活 编辑:程序博客网 时间:2024/03/29 04:34

下面查询数据库的非聚集索引的所有记录数目,保留和使用空间:

 

USE DatabaseNameHere;GO-- Drop temporary table if existsIF OBJECT_ID('tempDB.dbo.#IndexInfo') IS NOT NULL     DROP TABLE #IndexInfo ;    -- Create temporary tableCREATE TABLE #IndexInfo(          ObjectName VARCHAR(250),        IndexName VARCHAR(250),        IndexID INT,        PartitionNumber INT,        [#Records] INT,        [Reserved(MB)] INT,        [Used(MB)] INT);-- Collect index infoINSERT INTO #IndexInfoSELECT  o.name AS ObjectName,        i.name AS IndexName,        i.index_id AS IndexID,        p.partition_number AS PartitionID,        p.[rows] AS [#Records],        a.total_pages * 8 / 1024 AS [Reserved(MB)],        a.used_pages * 8 / 1024 AS [Used(MB)]FROM    sys.indexes AS i        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        INNER JOIN sys.sysobjects o ON i.[object_id] = o.idWHERE   i.name NOT LIKE 'sys%'        AND o.name NOT LIKE 'sys%'        AND i.[type] <> 1ORDER BY a.total_pages DESC;-- Return index info with TOTAL SELECT  ObjectName,        IndexName,        IndexID,        PartitionNumber,        [#Records],        [Reserved(MB)],        [Used(MB)]FROM    #IndexInfoUNION ALLSELECT  'TOTAL',        NULL,        NULL,        NULL,        NULL,        SUM(a.total_pages * 8 / 1024) AS [Reserved(mb)],        SUM(a.used_pages * 8 / 1024) AS [Used(mb)]FROM    sys.indexes AS i        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        INNER JOIN sys.sysobjects o ON i.[object_id] = o.idWHERE   o.name NOT LIKE 'sys%'        AND i.[type] <> 1;GO        


 

原创粉丝点击