IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_indexLookup_sp'), 
        N'IsProcedure') IS Null
BEGIN
    EXECUTE ('Create Procedure dbo.dba_indexLookup_sp 
        As Print ''Hello World!''')
    RAISERROR('Procedure dbo.dba_indexLookup_sp created.', 10, 1);
END;
Go
 
SET ANSI_Nulls ON;
SET Ansi_Padding ON;
SET Ansi_Warnings ON;
SET ArithAbort ON;
SET Concat_Null_Yields_Null ON;
SET NOCOUNT ON;
SET Numeric_RoundAbort OFF;
SET Quoted_Identifier ON;
Go
 
ALTER PROCEDURE dbo.dba_indexLookup_sp
 
        /* Declare Parameters */
        @tableName  VARCHAR(128)  =  Null
AS
/***********************************************************************
    Name:       dba_indexLookup_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves index information for the specified table.
 
    Notes:      If the tableName is left null, it will return index 
                information for all tables and indexes.
 
    Called by:  DBA
 
    Date        User   Description
    --------------------------------------------------------------------
    2008-10-28  MFU    Initial Release
************************************************************************
    Exec dbo.dba_indexLookup_sp
        @tableName = 'myTableName';
***********************************************************************/
 
SET NOCOUNT ON;
SET XACT_Abort ON;
 
BEGIN
 
    DECLARE @objectID INT;
 
    IF @tableName IS Not Null
        SET @objectID = OBJECT_ID(@tableName);
 
    WITH indexCTE(partition_scheme_name
                , partition_function_name
                , data_space_id)
    AS (
        SELECT sps.name
            , spf.name
            , sps.data_space_id
        FROM sys.partition_schemes AS sps
        Join sys.partition_functions AS spf
            ON sps.function_id = spf.function_id
    )
 
    SELECT st.name AS 'table_name'
        , IsNull(ix.name, '') AS 'index_name'
        , ix.OBJECT_ID
        , ix.index_id
		, CAST(
            CASE WHEN ix.index_id = 1 
                    THEN 'clustered' 
                WHEN ix.index_id =0
                    THEN 'heap'
                ELSE 'nonclustered' END
			+ CASE WHEN ix.ignore_dup_key <> 0 
                THEN ', ignore duplicate keys' 
                    ELSE '' END
			+ CASE WHEN ix.is_unique <> 0 
                THEN ', unique' 
                    ELSE '' END
			+ CASE WHEN ix.is_primary_key <> 0 
                THEN ', primary key' ELSE '' END AS VARCHAR(210)
            ) AS 'index_description'
        , IsNull(REPLACE( REPLACE( REPLACE(
            (   
                SELECT c.name AS 'columnName'
                FROM sys.index_columns AS sic
                Join sys.columns AS c 
                    ON c.column_id = sic.column_id 
                    And c.OBJECT_ID = sic.OBJECT_ID
                WHERE sic.OBJECT_ID = ix.OBJECT_ID
                    And sic.index_id = ix.index_id
                    And is_included_column = 0
                ORDER BY sic.index_column_id
                FOR XML Raw)
                , '"/><row columnName="', ', ')
                , '<row columnName="', '')
                , '"/>', ''), '')
            AS 'indexed_columns'
        , IsNull(REPLACE( REPLACE( REPLACE(
            (   
                SELECT c.name AS 'columnName'
                FROM sys.index_columns AS sic
                Join sys.columns AS c 
                    ON c.column_id = sic.column_id 
                    And c.OBJECT_ID = sic.OBJECT_ID
                WHERE sic.OBJECT_ID = ix.OBJECT_ID
                    And sic.index_id = ix.index_id
                    And is_included_column = 1
                ORDER BY sic.index_column_id
                FOR XML Raw)
                , '"/><row columnName="', ', ')
                , '<row columnName="', '')
                , '"/>', ''), '')
            AS 'included_columns'
        , IsNull(cte.partition_scheme_name, '') 
            AS 'partition_scheme_name'
        , COUNT(partition_number) AS 'partition_count'
        , SUM(ROWS) AS 'row_count'
    FROM sys.indexes AS ix
    Join sys.partitions AS sp
        ON ix.OBJECT_ID = sp.OBJECT_ID
        And ix.index_id = sp.index_id
    Join sys.tables AS st
        ON ix.OBJECT_ID = st.OBJECT_ID
    LEFT Join indexCTE AS cte
        ON ix.data_space_id = cte.data_space_id
    WHERE ix.OBJECT_ID = IsNull(@objectID, ix.OBJECT_ID)
    GROUP BY st.name
        , IsNull(ix.name, '')
        , ix.OBJECT_ID
        , ix.index_id
		, CAST(
            CASE WHEN ix.index_id = 1 
                    THEN 'clustered' 
                WHEN ix.index_id =0
                    THEN 'heap'
                ELSE 'nonclustered' END
			+ CASE WHEN ix.ignore_dup_key <> 0 
                THEN ', ignore duplicate keys' 
                    ELSE '' END
			+ CASE WHEN ix.is_unique <> 0 
                THEN ', unique' 
                    ELSE '' END
			+ CASE WHEN ix.is_primary_key <> 0 
                THEN ', primary key' ELSE '' END AS VARCHAR(210)
            )
        , IsNull(cte.partition_scheme_name, '')
        , IsNull(cte.partition_function_name, '')
    ORDER BY table_name
        , index_id;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go