Re-Scripting Indexes

[lilsamp]

I have been doing some index tuning on a database for a application that is an upgraded version of an application that we are using currently. The database that I'm tuning is currently in Development and will be going into Production soon. I recently found out that in order to upgrade the application into Production I'm going to have to restore the old database over the upgraded database (basically get all of the current data from the old system) then run a series of scripts to get the database in its upgraded form( add all the tables, views, etc.. that don't exist in the old version of this application).

Problem is all of the indexes that I have added in order to tune the application are not in the series of scripts that I will have to run. Since I didn't document all of the indexes I added, I have no way of knowing which indexes to re-create. I don't want to go in and script out every table in the Development environment to capture all of the indexes.

Do you all know a way that I could get all of the script that creates the indexes? (Something similar to syscomments table that stores the code that creates the views, stored procs, etc...) I'm also open for other suggestions. I just don't want to go through every table and script out the table to get the code for the indexes

 

[tlbroadbent]

You can easily script all the indexes and keys on all tables in a database from Enterprise Manager.

1) Right-click the DB.
2) Select All Tasks | Generate SQL Script
3) On the General tab clcik on the Show All button and select all tables.
4) On the Formatting tab unselect Create and Drop
5) On the Options tab select Scrip Indexes and Script Primary Keys, etc.

You can also use SQL DMO to script the indexes. See faq183-1637 for a sample.

You can also use DMO from a stored proc. See

http://www.databasejournal.com/features/mssql/article.php/2205291.

You can use the utility program scptxfr. It is not well documented but you can find some explanation at

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q220163.

I have a stored procedure that generates the scripts for indexes and keys. I obtained the original on another website. I can't find the orginal source site but will make the code available if wanted.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm