A Script A Day - Day 8 - Non Clustered Index Information

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.co.uk

      -----------------------------------------------------------------

      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