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 |
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.
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
Thanks for the great work.
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.
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!
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!
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.