查询非聚集索引的空间使用-CSDN博客

本文链接：https://blog.csdn.net/sfrankzhang/article/details/7253556

本文提供了一段SQL脚本，用于查询指定数据库中所有非聚集索引的记录数及所使用的空间大小，包括已分配但未使用的空间。通过创建临时表收集信息并返回汇总数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

非聚集索引查询