Today's script comes to you from London where I am at my head office for my appraisal! Lets hope it goes well 😉
The below script will provide Index information for all non clustered indexes in a database including the number of records, reserved and used space. All you need to do is change the database context to suit.
/*
-----------------------------------------------------------------
Non Clustered Index Information
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Set database context
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
Enjoy!
Chris
