Index Definition Audit Script

Recently, I needed to audit indexes on two different servers. I wanted to compare things like index keys and included columns, partitioning keys, unique constraints, clustered indexes and primary keys, and filter index definitions. Basically, I wanted to make sure that the indexing of two databases on two different servers were completely in sync. To do this, I wrote the following scripts.



Single-Database Version
WITH indexCTE AS
(
    SELECT st.object_id                                                                         AS objectID
        , st.name                                                                               AS tableName
        , si.index_id                                                                           AS indexID
        , si.name                                                                               AS indexName
        , si.type_desc                                                                          AS indexType
        , sc.column_id                                                                          AS columnID
        , sc.name + CASE WHEN sic.is_descending_key = 1 THEN ' DESC' ELSE '' END                AS columnName
        , sic.key_ordinal                                                                       AS ordinalPosition
        , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END   AS indexKeys
        , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END                       AS includedColumns
        , sic.partition_ordinal                                                                 AS partitionOrdinal
        , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END                        AS partitionColumns
        , si.is_primary_key                                                                     AS isPrimaryKey
        , si.is_unique                                                                          AS isUnique
        , si.is_unique_constraint                                                               AS isUniqueConstraint
        , si.has_filter                                                                         AS isFilteredIndex
        , COALESCE(si.filter_definition, '')                                                    AS filterDefinition
    FROM sys.tables                         AS st
    INNER JOIN sys.indexes                  AS si
        ON si.object_id =   st.object_id
    INNER JOIN sys.index_columns            AS sic
   ON sic.object_id=si.object_id
        AND sic.index_id=si.index_id
    INNER JOIN sys.columns                  AS sc
   ON sc.object_id = sic.object_id
   and sc.column_id = sic.column_id
)

SELECT DISTINCT
      @@SERVERNAME                                      AS ServerName
    , DB_NAME()                                         AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
            SELECT ', ' + indexKeys
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND indexKeys IS NOT NULL
            ORDER BY ordinalPosition
                FOR XML PATH(''),
      TYPE).value('.','varchar(max)'),1,1,'')           AS indexKeys
    , COALESCE(STUFF((
            SELECT ', ' + includedColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND includedColumns IS NOT NULL
            ORDER BY columnID
                FOR XML PATH(''),
      TYPE).value('.','varchar(max)'),1,1,''), '')      AS includedColumns
    , COALESCE(STUFF((
            SELECT ', ' + partitionColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND partitionColumns IS NOT NULL
            ORDER BY partitionOrdinal
                FOR XML PATH(''),
      TYPE).value('.','varchar(max)'),1,1,''), '')      AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , FilterDefinition
FROM indexCTE AS cte
--WHERE tableName = 'SalesOrderDetail'
ORDER BY tableName
    , indexName;


Multi-Database Version
IF OBJECT_ID('tempdb..#IndexAudit') IS NOT NULL
    DROP TABLE #IndexAudit;

CREATE TABLE #IndexAudit
(
      serverName                SYSNAME
    , databaseName              SYSNAME
    , tableName                 VARCHAR(128)
    , indexName                 VARCHAR(128)
    , indexType                 NVARCHAR(60)
    , indexKeys                 VARCHAR(8000)
    , includedColumns           VARCHAR(8000)
    , partitionColumns          VARCHAR(8000)
    , isPrimaryKey              BIT
    , isUnique                  BIT
    , isUniqueConstraint        BIT
    , isFilteredIndex           BIT
    , FilterDefinition          VARCHAR(8000)
);

EXECUTE sp_foreachdb 'USE ?;
WITH indexCTE AS
(
    SELECT st.object_id                                                                         AS objectID
        , st.name                                                                               AS tableName
        , si.index_id                                                                           AS indexID
        , si.type_desc                                                                          AS indexType
        , si.name                                                                               AS indexName
        , sc.column_id                                                                          AS columnID
        , sc.name + CASE WHEN sic.is_descending_key = 1 THEN '' DESC'' ELSE '''' END            AS columnName
        , sic.key_ordinal                                                                       AS ordinalPosition
        , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END   AS indexKeys
        , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END                       AS includedColumns
        , sic.partition_ordinal                                                                 AS partitionOrdinal
        , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END                        AS partitionColumns
        , si.is_primary_key                                                                     AS isPrimaryKey
        , si.is_unique                                                                          AS isUnique
        , si.is_unique_constraint                                                               AS isUniqueConstraint
        , si.has_filter                                                                         AS isFilteredIndex
        , COALESCE(si.filter_definition, '''')                                                  AS filterDefinition
    FROM sys.tables                         AS st
    INNER JOIN sys.indexes                  AS si
        ON si.object_id =   st.object_id
    INNER JOIN sys.index_columns            AS sic
   ON sic.object_id=si.object_id
        AND sic.index_id=si.index_id
    INNER JOIN sys.columns                  AS sc
   ON sc.object_id = sic.object_id
   and sc.column_id = sic.column_id
)

INSERT INTO #IndexAudit
SELECT DISTINCT
      @@SERVERNAME                                              AS ServerName
    , DB_NAME()                                                 AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
            SELECT '', '' + indexKeys
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND indexKeys IS NOT NULL
            ORDER BY ordinalPosition
                FOR XML PATH(''''),
      TYPE).value(''.'',''varchar(max)''),1,1,'''')             AS indexKeys
    , COALESCE(STUFF((
            SELECT '', '' + includedColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND includedColumns IS NOT NULL
            ORDER BY columnID
                FOR XML PATH(''''),
      TYPE).value(''.'',''varchar(max)''),1,1,''''), '''')      AS includedColumns
    , COALESCE(STUFF((
            SELECT '', '' + partitionColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND partitionColumns IS NOT NULL
            ORDER BY partitionOrdinal
                FOR XML PATH(''''),
      TYPE).value(''.'',''varchar(max)''),1,1,''''), '''')      AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , FilterDefinition
FROM indexCTE AS cte
ORDER BY tableName
    , indexName;
';

-- For multi-server testing, dump results to a temp table and compare tables
SELECT *
FROM #IndexAudit
WHERE databaseName NOT IN ('tempdb', 'master', 'msdb', 'model')
ORDER BY serverName
    , databaseName
    , tableName
    , indexName;

As noted by Kendra, the script does not work for 2005. However a minor change to use dynamic SQL (which you already did in the Multi-Database edition) can fix this.
eg
DECLARE
@sql NVARCHAR(MAX)
SET @sql =
‘WITH indexCTE AS
(
SELECT st.object_id AS objectID
, st.name AS tableName
, si.index_id AS indexID
, si.type_desc AS indexType
, si.name AS indexName
, sc.column_id AS columnID
, sc.name + CASE WHEN sic.is_descending_key = 1 THEN ” DESC” ELSE ”” END AS columnName
, sic.key_ordinal AS ordinalPosition
, CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END AS indexKeys
, CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END AS includedColumns
, sic.partition_ordinal AS partitionOrdinal
, CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END AS partitionColumns
, si.is_primary_key AS isPrimaryKey
, si.is_unique AS isUnique
, si.is_unique_constraint AS isUniqueConstraint
‘ +
CASE
WHEN (@@microsoftversion / 0×01000000) > 9 THEN
‘, si.has_filter AS isFilteredIndex
, COALESCE(si.filter_definition, ””) AS filterDefinition’
ELSE
‘, 0 AS isFilteredIndex
, ”” AS filterDefinition’
END + ‘
FROM sys.tables AS st
INNER JOIN sys.indexes AS si
ON si.object_id = st.object_id
INNER JOIN sys.index_columns AS sic
ON sic.object_id=si.object_id
AND sic.index_id=si.index_id
INNER JOIN sys.columns AS sc
ON sc.object_id = sic.object_id
and sc.column_id = sic.column_id
)
SELECT DISTINCT
@@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
, tableName
, indexName
, indexType
, STUFF((
SELECT ”, ” + indexKeys
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND indexKeys IS NOT NULL
ORDER BY ordinalPosition
FOR XML PATH(””),
TYPE).value(”.”,”varchar(max)”),1,1,””) AS indexKeys
, COALESCE(STUFF((
SELECT ”, ” + includedColumns
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND includedColumns IS NOT NULL
ORDER BY columnID
FOR XML PATH(””),
TYPE).value(”.”,”varchar(max)”),1,1,””), ””) AS includedColumns
, COALESCE(STUFF((
SELECT ”, ” + partitionColumns
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND partitionColumns IS NOT NULL
ORDER BY partitionOrdinal
FOR XML PATH(””),
TYPE).value(”.”,”varchar(max)”),1,1,””), ””) AS partitionKeys
, isPrimaryKey
, isUnique
, isUniqueConstraint
, isFilteredIndex
, FilterDefinition
FROM indexCTE AS cte
ORDER BY tableName
, indexName;
‘;
EXEC sp_executesql @SQL