I am working in a SQL Server 2014 environment where there are multiple databases where the table structure (column names, data types, sizes) matches. However, the indexes do not necessarily match. For instance one database might have a clustered index on table X, but the next database does not, there may be clustered indexes in one and not in the other, non-clustered indexes may exist in one place where all columns are part of the index key columns while other cases have a mix with included columns, etc.

What I am in need of is a script which will allow me to level set indexes, without removing indexes which exist on the "target" database but not in the "template", while minimizing duplicate and overlapping indexes. In lieu of such a script, some suggestions or guidance in creating one would be a viable option.


  • 3
    Have you looked at Red Gate's SQL Compare tool? May 23 '17 at 19:35
  • You could use one script for tables an another one for index.
    – McNets
    May 23 '17 at 19:36
  • For clarity sake, I know the tables match. The question is specific to the indexes.
    – pshore73
    May 23 '17 at 19:42
  • Erik, SQL Compare would be great, but not an option in this case. <sad trombone>
    – pshore73
    May 23 '17 at 19:43
  • 2
    You could also use Visual Studio and use SSDT. Can then compare databases (and filter it down to just look at indexes) May 23 '17 at 21:19

Use Visual Studio Sql Server Data Tools. It has a schema compare tool that you can use to view differences between any two databases. I have used this tool to do things very similar to what you are asking.

Instructions for Using the Schema Compare Tool General Instructions are

  1. Create Database Project

  2. Connect Databases to Project

  3. Click "Schema Compare" in SQL Menu

  4. Select Source and Target Database

  5. Hit Compare Button

  6. Select What you want to keep/get rid off

  7. Click on Generate Script

  • Excellent, I will take a look at that. Thank you.
    – pshore73
    May 23 '17 at 23:27

It turns out the client did have RedGate SQL Compare and we were able to address things with that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.