SQL to check if an index exists across multiple databases

PublishedFebruary 20, 2025

•2 min read•View as Markdown

SQL to check if an index exists across multiple databases

Tim Hilton

Part of seriesCode & technical writing

Comments

Join the discussion

No comments yet. Be the first to comment.

Code & technical writing

Part 11 of 16

This series groups articles which discuss specific code or technical concepts (as opposed to the process of working, etc.) If the blog consisted of only these articles, it would be a technical blog.

Up next

Installing a `.crt` file as a Trusted Root Certificate in Windows 11 while Kleopatra is installed

I was recently setting up a project which requires the installation of a .crt file as a Trusted Root Certificate for the Current User. The standard way of doing this is to right-click the file and select “Install Certificate”. However, I have Kleopat...

More from this blog

Giving Claude Code a /goal

I recently use the /goal command in Claude Code for the first time. I was struggling to reproduce a bug that I already had a theoretical understanding of from investigating the codebase, but had previ

Jul 30, 20262 min read

Month in review: June 2026

Things that happened I received a happy birthday message from our HR system. It gave me the same vibes as the AI-written compliments I mentioned in Month in review: May 2026. Microsoft ran an "AI sk

Jul 3, 20263 min read

TIL capitalising letters with regex

Fixing a code analysis auto-fix which made acronyms less clear

Jun 3, 20262 min read

Month in review: May 2026

Things I learnt A HashSet is for storing unique elements. The point is that it has O(1) lookups like a Dictionary does. It uses the same basic mechanism as a Dictionary does (hashing objects then usi

Jun 1, 20264 min read

TIL how to find latitude and longitude of a geolocation stored in SQL Server

I was recently working with an EF Core entity which contained a C# Point property representing a geolocation. This was stored in a single column in a SQL Server database. (I think this uses NetTopolog

May 7, 20262 min read

TIL how to find latitude and longitude of a geolocation stored in SQL Server

Tim talks tech

82 posts

Background

I recently wrote an EF Core migration which included dropping an index. This index was somewhat unusual, as it didn’t follow our naming conventions and we didn’t know how it had been added. The disparity in naming suggested it may have been manually added on a database, and might not necessarily be on all databases which are in use across all environments.

Prompted by a very helpful comment in a code review, I wanted to check a number of test databases to see which of them had the index in question. Given the number of databases which needed checking, I wanted to avoid manually checking them all. The obvious solution was to have a SQL script do the work for me.

SQL Script

I asked GitHub Copilot to create a script for me, then modified it myself until I was happy with it. This is the SQL script I used. Although I was happy with how it ran, if you plan to use this then please do check this SQL does what you expect before running it on your own databases.

-- Set these when running the script DECLARE @SchemaName NVARCHAR(255) = 'TODO' DECLARE @TableName NVARCHAR(255) = 'TODO' DECLARE @IndexName NVARCHAR(255) = 'TODO' -- Leave these for the script to use DECLARE @DatabaseName NVARCHAR(255) DECLARE @SQL NVARCHAR(MAX) -- Create a table to store the results CREATE TABLE #IndexCheckResults ( DatabaseName NVARCHAR(255), IndexExists BIT ) -- Cursor to iterate through all databases DECLARE db_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN -- Dynamic SQL to check for the index in the current database SET @SQL = ' IF EXISTS ( SELECT 1 FROM [' + @DatabaseName + '].sys.indexes WHERE name = ''' + @IndexName + ''' AND object_id = OBJECT_ID(''[' + @DatabaseName + '].' + @SchemaName + '.' + @TableName + ''') ) BEGIN INSERT INTO #IndexCheckResults (DatabaseName, IndexExists) VALUES (''' + @DatabaseName + ''', 1) END ELSE BEGIN INSERT INTO #IndexCheckResults (DatabaseName, IndexExists) VALUES (''' + @DatabaseName + ''', 0) END' EXEC sp_executesql @SQL FETCH NEXT FROM db_cursor INTO @DatabaseName END CLOSE db_cursor DEALLOCATE db_cursor -- Select the results SELECT * FROM #IndexCheckResults -- Drop the temporary table DROP TABLE #IndexCheckResults

Output

The output is a table containing a list of databases, with a boolean column indicating whether or not the index exists in each database. It ran near-enough instantaneously and gave me exactly the information I needed to have confidence that my migration would run without problems on all of our environments.

Command Palette