SQLServer 2005 Generate all INDEX as CREATE statement?

Question

I have two database with same structures, but one of them is missing INDEXES (i think i've missed out), i mean the table didn't have any INDEXES yet.

I was plan to generate CREATE INDEX for the database which have indexes but can't found any method available in Management Studio, yes we can generate script for tables, view, etc, but i was only need to script the INDEXES not anything else.

I know if we generate a script using IF EXIST can be solution, so if object already exist it doesn't recreate, but again for simplicity shake, i just want script the INDEXES.

Answer 1

Check out http://samsudeenb.blogspot.com/2007/11/scripts-to-drop-and-recreate-indexes-in.html - I think that is what you're looking for.

Or - as another answer also suggests - you might want to invest in a tool like Redgate.

Answer 2

Try this:

Or just right-click on an index in Management Studio and select "Script Index as - Create To -" and then as appropriate.

Answer 3

Not specific to just your need for Indexes, but a good way to keep multiple DBs in sync structure wise is to use a tool such as Redgate's SQL Compare, or AdeptSQL Diff, or similar. These will show you the differences between 2 databases and generate and even run the required scripts to get them into sync. Much easier than trying to do this manually.