非聚集索引查询

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

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