Index Definition Audit Script SQL Fool

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. The first script will audit a single database or even a single table. The second script once more makes use of Aaron Bertrand’s sp_foreachdb procedure to iterate through every database on a server.

To do a quick and easy compare, I dumped the results to a single table on each server and used Red Gate’s SQL Data Compare to find the differences.

This is another metadata script, so it should be fairly lightweight. That said, all of the usual disclaimers apply:

This script will only run on SQL 2008 or newer because of the filtered index component. See Patrick’s solution in the comments below for a mod that works in 2005 too.
It worked for me, but YMMV depending on editions, collations, creative settings, etc.
I don’t currently have access to any instances older than SQL Server 2012, so please respond with any backwards compatibility issues.
If you don’t have sp_foreachdb installed and don’t want to install it, you can replace it with sp_msforeachdb, but be aware databases may be skipped

Enjoy. 🙂

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;

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;

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;

Example Results

ServerName   DatabaseName       tableName            indexName                      indexType    indexKeys                                                    includedColumns                                    partitionKeys isPrimaryKey isUnique isUniqueConstraint isFilteredIndex FilterDefinition
------------ ------------------ -------------------- ------------------------------ ------------ ------------------------------------------------------------ -------------------------------------------------- ------------- ------------ -------- ------------------ --------------- --------------------------------
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     AK_SalesOrderDetail_rowguid    NONCLUSTERED  rowguid                                                                                                                      0            1        0                  0                                               
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     FIX_SalesOrderDetail_1         NONCLUSTERED  CarrierTrackingNumber, SalesOrderID, ProductID               OrderQty, SpecialOfferID, LineTotal, rowguid                    0            0        0                  1               ([ModifiedDate]>='2006-01-01')  
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     IX_SalesOrderDetail_ProductID  NONCLUSTERED  ProductID                                                                                                                    0            0        0                  0                                               
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     PK_SalesOrderDetail_SalesOrder CLUSTERED     SalesOrderID, SalesOrderDetailID                                                                                             1            1        0                  0

0saves

Save

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

9 Responses to Index Definition Audit Script

Kendra Little says:

October 15, 2012 at 9:46 am

This looks cool! I love how you provide such helpful scripts to the community, Michelle. So awesome.

Just one tiny note– I think this will work on just 2008 and newer because of the filtered index definition columns. I have a similar script and I just re-discovered that when testing against older indexes. 🙂
Patrick Flynn says:

October 15, 2012 at 10:09 pm

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 / 0x01000000) > 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

Thanks for the great work.
Mike says:

October 16, 2012 at 6:48 am

This is a good script, but it only works on SQL 2008 and above. I get errors when I run it on my SQL 2005 databases with the has_filter and filter_definition fields. Filtered indexes were introduced in SQL 2008. If I comment out those 2 fields it runs fine on my SQL 2005 databases.
Michelle Ufford says:

October 16, 2012 at 7:02 am

Doh! Thank you all for pointing out the issue with 2005. I forgot that filtered indexes were only added in 2008. I’ve updated the post accordingly and point to Patrick’s solution for 2005. Thank you! 🙂
ludwig Guevara says:

October 16, 2012 at 2:06 pm

Hi,

Please, could you give more detail about the version of SQL and if you have any service pack?

I tried to run in SQL 10.50.2500 – SQL 2008 R2 Service Pack 1 and I start to have issues with (because of space when I use sp_msforeachdb ):

, 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

Thanks.
Pingback: Index Comparison Made Slightly Easier « 36 Chambers – The Legendary Journeys: Execution to the max!
FishNChipPapers says:

October 21, 2012 at 9:45 am

Thanks for providing the script. You mention that you use SQL Data Compare to compare the results. I am not sure whether you also have access to SQL Compare but, if you do, I am wondering why you chose to build something yourself rather than use that product. I am in the process of auditing our development and production servers for differences and have been relying on SQL Compare, so your thoughts would be very welcome.
Nitin M says:

August 26, 2013 at 6:17 am

Thank u so much!!! Michelle!! I was just looking for this one
You are great in SQL!
Steve Sarre says:

May 29, 2014 at 2:02 am

Hi Michelle

Thanks for that.

May be worth including schema as well?
, SCHEMA_NAME(st.schema_id ) AS schemaName

Steve

Index Definition Audit Script

9 Responses to Index Definition Audit Script

Leave a Reply Cancel reply

Most Popular

Subscribe to my blog!

Contact Me on Twitter

Categories

Obligatory Disclaimer

Archives