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; |
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; |
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 |
I’ve just started a new series that explores the tuning and design changes required to support 27k transactions per second during the airing of Go Daddy’s Super Bowl commercials. Those who attended my 