SQL Server: Compare Database Tables/Indexes/Views/SP’s etc. For Differences Across Instances

There are a bunch of tools in the market to do the database comparisons for differences but if you want to do it for free (and programmatically)  at a basic level, you could use the method outlined below. For me, this came about as a result of an user asking “So, what is different between DEV and PROD that is causing a performance difference?“. The ready-to-use market tools may still not give you configuration differences for example.

This is related to my previous post below but this post has a bit more

• How to compare the configuration of two instances/databases

It will use the basic concepts for comparing data as outlined in

• Find Row Level Data Differences Using MINUS/EXCEPT

On GitHub:

All of the code referenced in this post is available as a single script on my GitHub account so that you can run it as a whole to get all differences (and contribute to it 🙂 ).

Steps:

We will collect and compare data in a possibly 3rd instance by creating a linked server to the two instances to be compared.

1. Create a Linked Server to 1st and 2nd instance
2. Collect the data for both instances into a table
3. Compare to get the differences and/or report
4. Drop the temporarily created table
5. Drop the Linked Server

Examples of comparisons to be made for differences!

You could use this method to compare anything. Click on the links below to see the code Note: You do need to create the linked servers as noted in Step 1 before trying these examples

• Instance Configuration Differences
• Table/View List Differences
• Table Row Count Differences
• Tables with Column Differences
• Index Differences
• Indexes with Column Differences
• Views with Column Differences
• Stored Procedure List Differences
• Stored Procedure Content Differences
• etc….endless!

Step 1. Create a Linked Server to 1st and 2nd instance

On a third SQL instance that you use for “play”, create a temporary database to store the data to compare and find differences. We will create 2 linked servers on this instance to point to the source and target instances to compare.

Note: In the Script below, replace “1stInstanceName” and “2ndInstanceName” with the with the Linked Server names and remote Username/Password with appropriate values

--Create linked server to 1st instance
EXEC master.dbo.sp_addlinkedserver
		@server = N'1stInstanceName',
		@srvproduct=N'SQL Server'

--Supply login details - You could use any user with adequate permissions
EXEC master.dbo.sp_addlinkedsrvlogin
		@rmtsrvname=N'1stInstanceName',
		@useself=N'False',
		@locallogin=NULL,
		@rmtuser=N'SA',
		@rmtpassword='********'

--Create linked server to 2nd instance
EXEC master.dbo.sp_addlinkedserver
		@server = N'2ndInstanceName',
		@srvproduct=N'SQL Server'

--Supply login details - You could use any user with adequate permissions
EXEC master.dbo.sp_addlinkedsrvlogin
		@rmtsrvname=N'2ndInstanceName',
		@useself=N'False',
		@locallogin=NULL,
		@rmtuser=N'SA',
		@rmtpassword='********'
GO

2. Collect the data for both instances into a table

In this case we are going to compare the list of tables and views between two databases in 1st instance vs. 2nd instance and see if certain tables/views only exist in one instance and not the other. We collect data from both instances into a table. This could be a temporary table but I am going for a permanent table for ease of illustration/use.

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared, “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--Create a blank table to hold the data to compare/report
SELECT @@servername AS ServerName, *
INTO DBA_TablesViews
FROM INFORMATION_SCHEMA.TABLES
WHERE 0=1;

--Get 1st instance data into our table
INSERT INTO DBA_TablesViews
SELECT '1stInstanceName' as ServerName, *
FROM [1stInstanceName].[1stInstanceDBName].INFORMATION_SCHEMA.TABLES;

--Get 2nd instance data into our table
INSERT INTO DBA_TablesViews
SELECT '2ndInstanceName' as ServerName, *
FROM [2ndInstanceName].[2ndInstanceDBName].INFORMATION_SCHEMA.TABLES;

--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TablesViews
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TablesViews
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ServerName;

3. Compare to get the differences and/or report

Let us see the list of all tables and views that exists only in the 1st instance database and/or the 2nd instance database

Note: In the Script below, replace “2ndInstanceName” with the Linked Server name

--Data that exists only in first or second but not both
SELECT 'Only in first' AS Difference, a.*
FROM
(
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '1stInstanceName'
	EXCEPT
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '2ndInstanceName'
) AS a
UNION ALL
SELECT 'Only in second' AS Difference, b.*
FROM
(
	--SELECTs reversed
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '2ndInstanceName'
	EXCEPT
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '1stInstanceName'
) AS b;

In this example, I get only the table that we created above as the difference but there could be a lot of other table/view differences:

4. Drop the temporarily created table

Do this only if you are done with reporting/comparing

DROP TABLE DBA_TablesViews;

5. Drop the Linked Servers

Keep the linked servers around if you want to play with the other examples but you can drop them if you no longer need them

USE [master]
GO
EXEC master.dbo.sp_dropserver
     @server=N'1stInstanceName',
     @droplogins='droplogins'
GO 

EXEC master.dbo.sp_dropserver
     @server=N'2ndInstanceName',
     @droplogins='droplogins'
GO

That’s it!

Examples!

Let us see multiple examples that you can run to compare different things

Instance Configuration Differences

This is a reproduction of my other blog post but it shows the configuration differences between two instances.

Note: In the Script below, replace “1stInstanceName” and “2ndInstanceName” with the Linked Server names

SELECT @@servername as ServerName, *
INTO DBA_Sys_Configurations
from sys.configurations
WHERE 0=1

--Get 1st instance sys.configurations data
INSERT INTO DBA_Sys_Configurations
SELECT '1stInstanceName' as ServerName, *
from 1stInstanceName.MASTER.sys.configurations

--Get 2nd instance sys.configurations data
INSERT INTO DBA_Sys_Configurations
SELECT '2ndInstanceName' as ServerName, *
from 2ndInstanceName.MASTER.sys.configurations

--Make sure we have data from both instances
SELECT * FROM DBA_Sys_Configurations
--Has both instance values

SELECT inst1.configuration_id,
    inst1.name,
    inst1.value AS Inst1Value,
    inst2.value AS Inst2Value
FROM
(
    SELECT *
    FROM DBA_Sys_Configurations
    WHERE ServerName = '1stInstanceName'
) inst1
INNER JOIN
(
    SELECT *
    FROM DBA_Sys_Configurations
    WHERE ServerName = '2ndInstanceName'
) inst2
ON inst1.configuration_id = inst2.configuration_id
    AND (inst1.value != inst2.value
         OR inst1.value_in_use != inst2.value_in_use)

The results show a couple of minor differences

Table/View List Differences

This is a reproduction of the example above but is here again! This lists the tables that are only in the first instance or the second instance but not in both.

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared and “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--DROP TABLE DBA_TablesViews;

--Create a blank table to hold the data to compare/report
SELECT @@servername AS ServerName, *
INTO DBA_TablesViews
FROM INFORMATION_SCHEMA.TABLES
WHERE 0=1;

--Truncate in case this already exists
TRUNCATE TABLE DBA_TablesViews;

--Get 1st instance data into our table
INSERT INTO DBA_TablesViews
SELECT '1stInstanceName' as ServerName, *
FROM [1stInstanceName].[1stInstanceDBName].INFORMATION_SCHEMA.TABLES;

--Get 2nd instance data into our table
INSERT INTO DBA_TablesViews
SELECT '2ndInstanceName' as ServerName, *
FROM [2ndInstanceName].[2ndInstanceDBName].INFORMATION_SCHEMA.TABLES;

--Uncomment if needed for review/reporting
/*
--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TablesViews
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TablesViews
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ServerName;
*/

--Data that exists only in first or second but not both
SELECT 'Only in first' AS Difference, a.*
FROM
(
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName  = '1stInstanceName'
	EXCEPT
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '2ndInstanceName'
) AS a
UNION ALL
SELECT 'Only in second' AS Difference, b.*
FROM
(
	--SELECTs reversed
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '2ndInstanceName'
	EXCEPT
	SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM DBA_TablesViews WHERE ServerName = '1stInstanceName'
) AS b;

The above code as we know would produce an output like:

Table Row Count Differences

This lists the tables that have row count differences between the first instance and the second instance databases (tables with same row count are not included)

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared and “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--DROP TABLE DBA_TableRowCounts

--Create a blank table to hold the data to compare/report
SELECT *
INTO DBA_TableRowCounts
FROM
(
	SELECT
	   @@servername as ServerName,
	   SCHEMA_NAME(schema_id) AS [SchemaName],
	   t.name AS [TableName],
	   SUM(p.[rows]) AS [TotalRowCount]
	FROM
	   sys.tables AS t
	   JOIN
		  sys.partitions AS p
		  ON t.[object_id] = p.[object_id]
		      AND p.index_id IN (0,1)
	WHERE 0=1
	GROUP BY
	   SCHEMA_NAME(schema_id),
	   t.name
) a;

--Truncate in case this already exists
TRUNCATE TABLE DBA_TableRowCounts;

--Get 1st instance data into our table
INSERT INTO DBA_TableRowCounts
SELECT
   '1stInstanceName' as ServerName,
   s.name AS [SchemaName],
   t.name AS [TableName],
   SUM(p.[rows]) AS [TotalRowCount]
FROM
   [1stInstanceName].[1stInstanceDBName].sys.tables AS t
   INNER JOIN
		[1stInstanceName].[1stInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
   INNER JOIN
      [1stInstanceName].[1stInstanceDBName].sys.partitions AS p
      ON t.[object_id] = p.[object_id]
      AND p.index_id IN (0,1)
GROUP BY
   s.name,
   t.name;

--Get 2nd instance data into our table
INSERT INTO DBA_TableRowCounts
SELECT
   '2ndInstanceName' as ServerName,
   s.name AS [SchemaName],
   t.name AS [TableName],
   SUM(p.[rows]) AS [TotalRowCount]
FROM
   [2ndInstanceName].[2ndInstanceDBName].sys.tables AS t
   INNER JOIN
		[2ndInstanceName].[2ndInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
   INNER JOIN
      [2ndInstanceName].[2ndInstanceDBName].sys.partitions AS p
      ON t.[object_id] = p.[object_id]
      AND p.index_id IN (0,1)
GROUP BY
   s.name,
   t.name;

--Uncomment if needed for review/reporting
/*
--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TableRowCounts
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TableRowCounts
ORDER BY [SchemaName], [TableName], ServerName, [TotalRowCount];
*/

--Tables with row count differences
SELECT a.[SchemaName], a.[TableName],
		a.TotalRowCount AS DB1RowCount, b.TotalRowCount AS DB2RowCount,
		a.TotalRowCount-b.TotalRowCount AS RowCountDifference
FROM
	(
		SELECT * FROM DBA_TableRowCounts WHERE ServerName = '1stInstanceName'
	) a
	INNER JOIN
	(
		SELECT * FROM DBA_TableRowCounts WHERE ServerName = '2ndInstanceName'
	) b
	ON a.[SchemaName] = b.[SchemaName]
		AND a.[TableName] = b.[TableName]
WHERE
	ABS(a.TotalRowCount-b.TotalRowCount) > 0
ORDER BY
	ABS(a.TotalRowCount-b.TotalRowCount) DESC;

The above code will produce an output like this:

Table Column Differences

This lists the tables that have column differences (number of columns, column names) between the first instance and the second instance databases

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared and “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--DROP TABLE DBA_TableColumns

--Create a blank table to hold the data to compare/report
SELECT *
INTO DBA_TableColumns
FROM
(
	SELECT
		'1stInstanceName' as ServerName,
		s.name AS SchemaName,
		o.name AS TableName,
		(
		   SELECT c.name + ', '
		   FROM [1stInstanceName].[1stInstanceDBName].sys.columns c
		   WHERE t.object_id = c.object_id
		   ORDER BY c.column_id
		   FOR XML PATH('')
		) AS Columns,
		t.create_date,
		t.modify_date,
		t.type_desc
	FROM [1stInstanceName].[1stInstanceDBName].sys.tables t
		INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.objects o
			ON t.object_id = o.object_id
		INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
			ON s.[schema_id] = t.[schema_id]
	WHERE
		o.is_ms_shipped = 0
		AND 0=1
) a;

--Get 1st instance data into our table
INSERT INTO DBA_TableColumns
SELECT
	'1stInstanceName' as ServerName,
	s.name AS SchemaName,
	o.name AS TableName,
	(
		SELECT c.name + ', '
		FROM [1stInstanceName].[1stInstanceDBName].sys.columns c
		WHERE t.object_id = c.object_id
		ORDER BY c.column_id
		FOR XML PATH('')
	) AS Columns,
	t.create_date,
	t.modify_date,
	t.type_desc
FROM [1stInstanceName].[1stInstanceDBName].sys.tables t
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.objects o
		ON t.object_id = o.object_id
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
WHERE
	o.is_ms_shipped = 0;

--Get 2nd instance data into our table
INSERT INTO DBA_TableColumns
SELECT
	'2ndInstanceName' as ServerName,
	s.name AS SchemaName,
	o.name AS TableName,
	(
		SELECT c.name + ', '
		FROM [2ndInstanceName].[2ndInstanceDBName].sys.columns c
		WHERE t.object_id = c.object_id
		ORDER BY c.column_id
		FOR XML PATH('')
	) AS Columns,
	t.create_date,
	t.modify_date,
	t.type_desc
FROM [2ndInstanceName].[2ndInstanceDBName].sys.tables t
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.objects o
		ON t.object_id = o.object_id
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
WHERE
	o.is_ms_shipped = 0;

--Uncomment if needed for review/reporting
/*
--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TableColumns
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TableColumns
ORDER BY [SchemaName], [TableName], ServerName;
*/

--Tables with column differences
SELECT a.[SchemaName], a.[TableName],
		a.Columns AS DB1Columns, b.Columns AS DB2Columns,
		ABS(LEN(a.Columns)-LEN(b.Columns)) AS HasDifference
FROM
	(
		SELECT * FROM DBA_TableColumns WHERE ServerName = '1stInstanceName'
	) a
	INNER JOIN
	(
		SELECT * FROM DBA_TableColumns WHERE ServerName = '2ndInstanceName'
	) b
	ON a.[SchemaName] = b.[SchemaName]
		AND a.[TableName] = b.[TableName]
WHERE
	ABS(LEN(a.Columns)-LEN(b.Columns))  > 0
ORDER BY
	ABS(LEN(a.Columns)-LEN(b.Columns)) DESC;

The results would look somewhat like this (last column would be a 1 if there were differences in the column names or order)

Index Differences

This lists the tables that have index differences between the first instance and the second instance databases

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared and “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--DROP TABLE DBA_TableIndexes

--Create a blank table to hold the data to compare/report
SELECT  *
INTO DBA_TableIndexes
FROM
	(
		SELECT
			@@servername AS ServerName,
			SchemaName = s.name,
			TableName = t.name,
			IndexName = ind.name,
			IndexId = ind.index_id,
			IndexType= ind.type_desc,
			ind.*
		FROM
			 [1stInstanceName].[1stInstanceDBName].sys.indexes ind
		INNER JOIN
			[1stInstanceName].[1stInstanceDBName].sys.tables t ON ind.object_id = t.object_id
			INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
				ON s.[schema_id] = t.[schema_id]
		WHERE
			t.is_ms_shipped = 0
		--ORDER BY
		--	 t.name, ind.name, ind.index_id
	 ) a
WHERE 0=1;

--Truncate in case this already exists
TRUNCATE TABLE DBA_TableIndexes;

--Get 1st instance data into our table
INSERT INTO DBA_TableIndexes
SELECT
	'1stInstanceName' AS ServerName,
	SchemaName = s.name,
	TableName = t.name,
	IndexName = ind.name,
	IndexId = ind.index_id,
	IndexType= ind.type_desc,
	ind.*
FROM
	[1stInstanceName].[1stInstanceDBName].sys.indexes ind
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.tables t
		ON ind.object_id = t.object_id
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
WHERE
	t.is_ms_shipped = 0
ORDER BY
	 t.name, ind.name, ind.index_id;

--Get 2nd instance data into our table
INSERT INTO DBA_TableIndexes
SELECT
	'2ndInstanceName' AS ServerName,
	SchemaName = s.name,
	TableName = t.name,
	IndexName = ind.name,
	IndexId = ind.index_id,
	IndexType= ind.type_desc,
	ind.*
FROM
	[2ndInstanceName].[2ndInstanceDBName].sys.indexes ind
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.tables t
		ON ind.object_id = t.object_id
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.schemas AS s
		ON s.[schema_id] = t.[schema_id]
WHERE
	t.is_ms_shipped = 0
ORDER BY
	 t.name, ind.name, ind.index_id;

--Uncomment if needed for review/reporting
/*
--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TableIndexes
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TableIndexes
ORDER BY TableName, IndexName, IndexType, ServerName;
*/

--Data that exists only in first or second but not both
SELECT 'Only in first' AS Difference, a.*
FROM
(
	SELECT TableName, IndexName, IndexType FROM DBA_TableIndexes WHERE ServerName  = '1stInstanceName'
	EXCEPT
	SELECT TableName, IndexName, IndexType FROM DBA_TableIndexes WHERE ServerName = '2ndInstanceName'
) AS a
UNION ALL
SELECT 'Only in second' AS Difference, b.*
FROM
(
	--SELECTs reversed
	SELECT TableName, IndexName, IndexType FROM DBA_TableIndexes WHERE ServerName = '2ndInstanceName'
	EXCEPT
	SELECT TableName, IndexName, IndexType FROM DBA_TableIndexes WHERE ServerName = '1stInstanceName'
) AS b

Although there were no differences in my case, you would see something like below, if there were differences

Index Column Differences

This lists the tables that have index column differences (index columns, order etc) between the first instance and the second instance databases. This is in addition to the differences due to indexes being in only one or the other (thanks to FULL OUTER JOIN).

Note: In the Script below, replace “1stInstanceDBName” and “2ndInstanceDBName” with the database names whose tables are to be compared and “1stInstanceName” and “2ndInstanceName” with the Linked Server names

--DROP TABLE DBA_TableIndexColumns

--Create a blank table to hold the data to compare/report
SELECT  *
INTO DBA_TableIndexColumns
FROM
	(
		SELECT
			'2ndInstanceName' as ServerName,
			s.name as SchemaName,
			o.name as TableName,
			i.name as IndexName,
			(
			   SELECT c.name + ', '
			   FROM [1stInstanceName].[1stInstanceDBName].sys.index_columns ic
				INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.columns c
					ON ic.column_id = c.column_id
						AND ic.object_id = c.object_id
			   WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
				 AND ic.is_included_column = 0
			   ORDER BY ic.index_column_id
			   FOR XML PATH('')
			) AS Key_Columns,
			(
			   SELECT c.name + ', '
			   FROM [1stInstanceName].[1stInstanceDBName].sys.index_columns ic
				INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.columns c
					ON ic.column_id = c.column_id
						AND ic.object_id = c.object_id
			   WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
				 AND ic.is_included_column = 1
			   ORDER BY ic.index_column_id
			   FOR XML PATH('')
			) AS IncludedColumns,
			i.type_desc as IndexType,
			i.is_unique as IsUnique,
			i.is_primary_key as IsPrimaryKey
		FROM [1stInstanceName].[1stInstanceDBName].sys.indexes i
			INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.objects o
				ON i.object_id = o.object_id
			INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
					ON o.[schema_id] = s.[schema_id]
		WHERE
			o.is_ms_shipped = 0
			AND 0=1
	 ) a
WHERE 0=1;

--Truncate in case this already exists
TRUNCATE TABLE DBA_TableIndexColumns;

--Get 1st instance data into our table
INSERT INTO DBA_TableIndexColumns
SELECT
	'1stInstanceName' as ServerName,
	s.name as SchemaName,
	o.name as TableName,
	i.name as IndexName,
	(
		SELECT c.name + ', '
		FROM [1stInstanceName].[1stInstanceDBName].sys.index_columns ic
		INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.columns c
			ON ic.column_id = c.column_id
				AND ic.object_id = c.object_id
		WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
			AND ic.is_included_column = 0
		ORDER BY ic.index_column_id
		FOR XML PATH('')
	) AS Key_Columns,
	(
		SELECT c.name + ', '
		FROM [1stInstanceName].[1stInstanceDBName].sys.index_columns ic
		INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.columns c
			ON ic.column_id = c.column_id
				AND ic.object_id = c.object_id
		WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
			AND ic.is_included_column = 1
		ORDER BY ic.index_column_id
		FOR XML PATH('')
	) AS IncludedColumns,
	i.type_desc as IndexType,
	i.is_unique as IsUnique,
	i.is_primary_key as IsPrimaryKey
FROM [1stInstanceName].[1stInstanceDBName].sys.indexes i
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.objects o
		ON i.object_id = o.object_id
	INNER JOIN [1stInstanceName].[1stInstanceDBName].sys.schemas AS s
			ON o.[schema_id] = s.[schema_id]
WHERE
	o.is_ms_shipped = 0;

--Get 2nd instance data into our table
INSERT INTO DBA_TableIndexColumns
SELECT
	'2ndInstanceName' as ServerName,
	s.name as SchemaName,
	o.name as TableName,
	i.name as IndexName,
	(
		SELECT c.name + ', '
		FROM [2ndInstanceName].[2ndInstanceDBName].sys.index_columns ic
		INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.columns c
			ON ic.column_id = c.column_id
				AND ic.object_id = c.object_id
		WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
			AND ic.is_included_column = 0
		ORDER BY ic.index_column_id
		FOR XML PATH('')
	) AS Key_Columns,
	(
		SELECT c.name + ', '
		FROM [2ndInstanceName].[2ndInstanceDBName].sys.index_columns ic
		INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.columns c
			ON ic.column_id = c.column_id
				AND ic.object_id = c.object_id
		WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id
			AND ic.is_included_column = 1
		ORDER BY ic.index_column_id
		FOR XML PATH('')
	) AS IncludedColumns,
	i.type_desc as IndexType,
	i.is_unique as IsUnique,
	i.is_primary_key as IsPrimaryKey
FROM [2ndInstanceName].[2ndInstanceDBName].sys.indexes i
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.objects o
		ON i.object_id = o.object_id
	INNER JOIN [2ndInstanceName].[2ndInstanceDBName].sys.schemas AS s
			ON o.[schema_id] = s.[schema_id]
WHERE
	o.is_ms_shipped = 0;

--Uncomment if needed for review/reporting
/*
--This is all the collected data - summary
SELECT ServerName, COUNT(1) AS RowCnt
FROM DBA_TableIndexColumns
GROUP BY ServerName;

--This is all the collected data - details
SELECT *
FROM DBA_TableIndexColumns
ORDER BY SchemaName, TableName, IndexName, Key_Columns, IncludedColumns, ServerName;
*/

--Tables with index column differences
SELECT COALESCE(a.[SchemaName],b.[SchemaName]) AS SchemaName,
		COALESCE(a.[TableName],b.[TableName]) AS TableName,
		CASE WHEN a.TableName IS NULL
			THEN 'Index is only in DB2'
			WHEN b.TableName IS NULL
			THEN 'Index is only in DB1'
			ELSE 'Index is in both with differences'
		END AS Diff,
		a.Key_Columns AS DB1KeyColumns,
		b.Key_Columns AS DB2KeyColumns,
		a.IncludedColumns AS DB1IncludedColumns, b.IncludedColumns AS DB2IncludedColumns,
		a.IndexType AS DB1IndexType, b.IndexType AS DB2IndexType,
		a.IsUnique AS DB1IsUnique, b.IsUnique AS DB2IsUnique,
		a.IsPrimaryKey AS DB1IsPrimaryKey, b.IsPrimaryKey AS DB2IsPrimaryKey
FROM
	(
		SELECT * FROM DBA_TableIndexColumns WHERE ServerName = '1stInstanceName'
	) a
	FULL JOIN
	(
		SELECT * FROM DBA_TableIndexColumns WHERE ServerName = '2ndInstanceName'
	) b
	ON a.[SchemaName] = b.[SchemaName]
		AND a.[TableName] = b.[TableName]
		AND a.[IndexName] = b.[IndexName]
WHERE
	a.IndexType  b.IndexType
	OR COALESCE(a.Key_Columns,'~')  COALESCE(b.Key_Columns,'~')
	OR COALESCE(a.IncludedColumns,'~')  COALESCE(b.IncludedColumns,'~')
	OR a.IsUnique  b.IsUnique
	OR a.IsPrimaryKey  b.IsPrimaryKey
ORDER BY
	a.[SchemaName], a.[TableName], a.IndexName;

The differences would look something like below:

All scripts packaged into one script on GitHub

I have posted all the scripts above as a single script on my GitHub account so that you can run it as a single script. It will give you all the results in one shot. A partial screenshot is shown below

Conclusion:

Although the above list of examples is not exhaustive, it should given a general sense of the differences quickly. Also, it is not too difficult to follow the examples and create more difference queries. Please post anything you come-up with in the comments for the benefit of other readers (or make a pull request on the GitHub code and update there!).

Advertisement