UmbraCare LOGO

How to Compare Indexes Between Two MSSQL Databases Easily

Learn how to compare MSSQL database indexes efficiently using a simple T-SQL script. Ensure database performance and consistency with our step-by-step guide

Published on: , by:
Piotr Bach picture
Piotr Bach
Wojciech Tengler picture
Wojciech Tengler
MS SQL

Jump to Section

Whether you're upgrading, migrating, or simply auditing your databases, having an efficient method to compare indexes can save time and avoid potential issues.

This article provides a straightforward T-SQL script to compare indexes between two MSSQL databases.

The script helps you identify missing indexes and differences that could impact performance.

Prerequisites#

  1. SQL Server Management Studio (SSMS) installed.
  2. Access to a SQL Server instance.

Step 1: Create Source and Target Databases#

First, let's create two sample databases named SourceDatabase and TargetDatabase.

-- Create Source Database
CREATE DATABASE SourceDatabase;
GO

-- Create Target Database
CREATE DATABASE TargetDatabase;
GO

Step 2: Create Tables and Indexes in Both Databases#

Next, we'll create dbo.Customers tables and indexes in both databases to simulate differences.

-- Create a table and indexes in SourceDatabase
USE SourceDatabase;
GO

CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100),
    Phone NVARCHAR(15)
);
GO

CREATE INDEX IX_Customers_Email ON dbo.Customers(Email);
CREATE INDEX IX_Customers_Phone ON dbo.Customers(Phone);
GO

-- Create a table and indexes in TargetDatabase
USE TargetDatabase;
GO

CREATE TABLE dbo.Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    Email NVARCHAR(100),
    Phone NVARCHAR(15)
);
GO

CREATE INDEX IX_Customers_Email ON dbo.Customers(Email);
-- Intentionally omitting IX_Customers_Phone to simulate a difference
CREATE INDEX IX_Customers_CustomerName ON dbo.Customers(CustomerName); -- New index not in SourceDatabase
GO
Screenshot of SSMS showing the table and index creation in both SourceDatabase and TargetDatabase

Screenshot of SSMS showing the table and index creation in both SourceDatabase and TargetDatabase

Step 3: Compare Indexes Between Source and Target Databases#

Now, we will compare the indexes between the two databases using the script:

-- Declare variables for database names
DECLARE @SourceDatabaseName NVARCHAR(128) = 'SourceDatabase';
DECLARE @TargetDatabaseName NVARCHAR(128) = 'TargetDatabase';

-- Drop temporary tables if they exist
IF OBJECT_ID('tempdb..#SourceDatabaseIndexes') IS NOT NULL
    DROP TABLE #SourceDatabaseIndexes;

IF OBJECT_ID('tempdb..#TargetDatabaseIndexes') IS NOT NULL
    DROP TABLE #TargetDatabaseIndexes;

-- Create temporary tables in the main session
CREATE TABLE #SourceDatabaseIndexes (
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    IndexID INT,
    IndexType NVARCHAR(60),
    ColumnName NVARCHAR(128),
    KeyOrdinal INT,
    IsIncludedColumn BIT
);

CREATE TABLE #TargetDatabaseIndexes (
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    IndexID INT,
    IndexType NVARCHAR(60),
    ColumnName NVARCHAR(128),
    KeyOrdinal INT,
    IsIncludedColumn BIT
);

-- Capture index information from the source database
PRINT 'Capturing index information from the source database...';
DECLARE @sql NVARCHAR(MAX);

SET @sql = '
USE [' + @SourceDatabaseName + '];

INSERT INTO #SourceDatabaseIndexes
SELECT 
    schema_name(t.schema_id) AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyOrdinal,
    ic.is_included_column AS IsIncludedColumn
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName, KeyOrdinal;
';

EXEC sp_executesql @sql;

-- Capture index information from the target database
PRINT 'Capturing index information from the target database...';

SET @sql = '
USE [' + @TargetDatabaseName + '];

INSERT INTO #TargetDatabaseIndexes
SELECT 
    schema_name(t.schema_id) AS SchemaName,
    t.name AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    i.type_desc AS IndexType,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyOrdinal,
    ic.is_included_column AS IsIncludedColumn
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.is_hypothetical = 0
ORDER BY SchemaName, TableName, IndexName, KeyOrdinal;
';

EXEC sp_executesql @sql;

-- Compare indexes between the source and target databases
PRINT 'Comparing indexes between the source and target databases...';
PRINT 'Comparison of Indexes:';
SELECT 
    COALESCE(s.SchemaName, t.SchemaName) AS SchemaName,
    COALESCE(s.TableName, t.TableName) AS TableName,
    COALESCE(s.IndexName, t.IndexName) AS IndexName,
    COALESCE(s.IndexID, t.IndexID) AS IndexID,
    s.IndexType AS SourceIndexType,
    t.IndexType AS TargetIndexType,
    s.ColumnName AS SourceColumnName,
    t.ColumnName AS TargetColumnName,
    s.KeyOrdinal AS SourceKeyOrdinal,
    t.KeyOrdinal AS TargetKeyOrdinal,
    s.IsIncludedColumn AS SourceIsIncludedColumn,
    t.IsIncludedColumn AS TargetIsIncludedColumn
FROM #SourceDatabaseIndexes s
FULL OUTER JOIN #TargetDatabaseIndexes t 
ON s.SchemaName = t.SchemaName
AND s.TableName = t.TableName
AND s.IndexName = t.IndexName
AND s.IndexID = t.IndexID
AND s.ColumnName = t.ColumnName
ORDER BY SchemaName, TableName, IndexName;

-- Identify missing indexes in the source database
PRINT 'Identifying missing indexes in the source database...';
PRINT 'Indexes present in the target database but missing in the source database:';
SELECT 
    t.SchemaName,
    t.TableName,
    t.IndexName,
    t.IndexID,
    t.IndexType,
    t.ColumnName,
    t.KeyOrdinal,
    t.IsIncludedColumn
FROM #TargetDatabaseIndexes t
LEFT JOIN #SourceDatabaseIndexes s
ON t.SchemaName = s.SchemaName
AND t.TableName = s.TableName
AND t.IndexName = s.IndexName
AND t.IndexID = s.IndexID
AND t.ColumnName = s.ColumnName
WHERE s.IndexName IS NULL
ORDER BY t.SchemaName, t.TableName, t.IndexName;

-- Identify missing indexes in the target database
PRINT 'Identifying missing indexes in the target database...';
PRINT 'Indexes present in the source database but missing in the target database:';
SELECT 
    s.SchemaName,
    s.TableName,
    s.IndexName,
    s.IndexID,
    s.IndexType,
    s.ColumnName,
    s.KeyOrdinal,
    s.IsIncludedColumn
FROM #SourceDatabaseIndexes s
LEFT JOIN #TargetDatabaseIndexes t
ON s.SchemaName = t.SchemaName
AND s.TableName = t.TableName
AND s.IndexName = t.IndexName
AND s.IndexID = t.IndexID
AND s.ColumnName = t.ColumnName
WHERE t.IndexName IS NULL
ORDER BY s.SchemaName, s.TableName, s.IndexName;
T-SQL script output

Output Explanation and Analysis#

After running the provided T-SQL script, you will see several result sets in SQL Server Management Studio (SSMS).

Let's walk through each result set and understand what it means.

1. Capturing Index Information from Source and Target Databases

When you run the script, the first two parts capture index information from the SourceDatabase and TargetDatabase.

The PRINT statements indicate these actions:

  • "Capturing index information from the source database..."
  • "Capturing index information from the target database..."

2. Comparison of Indexes

The script performs a full outer join on the indexes from both databases, showing you a detailed comparison.

Analysis:

  • The IX_Customers_Email index exists in both databases with identical definitions.
  • The IX_Customers_Phone index exists in the SourceDatabase but is missing in the TargetDatabase.
  • The IX_Customers_Name index exists in the TargetDatabase but is missing in the SourceDatabase.

3. Identifying Missing Indexes in the Source Database

This result set identifies indexes that are present in the TargetDatabase but missing in the SourceDatabase.

The IX_Customers_Name index is missing in the SourceDatabase. 

4. Identifying Missing Indexes in the Target Database

This result set identifies indexes that are present in the SourceDatabase but missing in the TargetDatabase.

The IX_Customers_Phone index is missing in the TargetDatabase. 

Step 4: Drop the sample databases (only for tutorial)#

Finally, clean up by dropping the SourceDatabase and TargetDatabase.

-- Drop Source Database
DROP DATABASE SourceDatabase;
GO

-- Drop Target Database
DROP DATABASE TargetDatabase;
GO

Dropping databases in a production environment can result in data loss and service interruption. The steps to drop the databases are included here for tutorial purposes only. Ensure you have proper backups and authorization before performing such operations in a live environment.

Keep Your Databases Running Smoothly#

This simple T-SQL script makes comparing indexes between two databases easy.

It helps you spot discrepancies and address them quickly. 

Hopefully, this automated approach saves time and ensures your databases are always in top shape.

👉You can find the code on GitHub.

What's next?#

🌐 Explore More: Interested in learning about MSSQL, .NET, and other web development insights?

Check out our blog for a wealth of information and expert advice.

EDITOR'S PICK

↑ Top ↑