Generate scripts in 2005 for indexes not the same as in 2000

Question

Generate scripts in 2005 for indexes not the same as in 2000

tleal

Grasshopper

Points: 14
More actions
January 9, 2007 at 7:09 am

#115650

In sql 2000 I could generate scripts for indexes and the primary indexes were scripted seperately with ALTER TABLE. This worked perfect for me. In 2005 the primary index is scriped in the CREATE TABLE, not as a seperate ALTER TABLE. I need to generate script for all indexes in a DB. Does anyone know how I can do this? The reason I'm doing this is that I have many Databases with the same table structure, however over the years the indexes have not been kept in sync. I want to clean this up. I now have a Database that has the correct indexes, and want duplicate these indexes on all my other databases. Is there a better way to do this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

There are some third party comparision tools out there you can use them if it is one time process by downloading trial version...

Google "Shcema comparision tools for sql server" or something like...

MohammedU
Microsoft SQL Server MVP

smk-127973 SSC-Addicted Points: 434 More actions · Answer 2

I don't know why in SQL 2005 they removed all the cool features that were there in SQL 2K.

In 2K, it was easy to generate scripts only for indexes on all tables using the Script generator( uncheck everything except for indexes and PK/FK). But anybody know how to do that in SQL 2005?

I have a db that somebody created using SQL 2005 script generator's script, and apparantly, he forgot to select the 'include index' option while doing so. This was noticed at a later point of time and now, it seems, I have to take the job of Script generator and write a script to generate 'only indexes'.

Any help is appreciated.

--Smk

MohammedU SSCoach Points: 19644 More actions · Answer 3

At this time you will get table script also along with indexes in SSMS.

If you want to only index script you have to write your code using DMO..

http://www.karaszi.com/SQLServer/info_generate_script.asp

MohammedU
Microsoft SQL Server MVP

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

this free tool will script out all objects in any 2000 or 2005 database, using SMO. source is available so you can modify it as you please.

it's very useful for getting all your objects under source control.

http://www.elsasoft.org/tools.htm

---------------------------------------
elsasoft.org