非聚集索引查询

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

 

USE DatabaseNameHere;
GO


-- Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#IndexInfo') IS NOT NULL 
    DROP TABLE #IndexInfo ;

-- Create temporary table
CREATE TABLE #IndexInfo
(
          ObjectName VARCHAR(250),
        IndexName VARCHAR(250),
        IndexID INT,
        PartitionNumber INT,
        [#Records] INT,
        [Reserved(MB)] INT,
        [Used(MB)] INT
);


-- Collect index info
INSERT INTO #IndexInfo
SELECT  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.id
WHERE   i.name NOT LIKE 'sys%'
        AND o.name NOT LIKE 'sys%'
        AND i.[type] <> 1
ORDER BY a.total_pages DESC;


-- Return index info with TOTAL 
SELECT  ObjectName,
        IndexName,
        IndexID,
        PartitionNumber,
        [#Records],
        [Reserved(MB)],
        [Used(MB)]
FROM    #IndexInfo
UNION ALL
SELECT  '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.id
WHERE   o.name NOT LIKE 'sys%'
        AND i.[type] <> 1;
GO