February 22, 2011 at 11:00 am
I have SS2K but am using SSMS. I am attempting to programmatically capture scripts for all Indexes in a development database.
My goal is to programmatically drop all indexes in the database (I have a script that interates all indexes), but I want to be able to easily recreate them as required. This is for a reporting database and the indexes are not keys.
In SSMS, I have tried the following:
- Selected the DB in the Object Explorer
- Right Clicked and selected "Tasks\Generate Scripts"
Unfortunately, it only allows you to script SPs, Tables, Users, and Views ... not indexes.
Would anyone know how to script a DROP and CREATE for all indexes in a database?
Where does SS store the script for Indexes? I have seen the script for SPs in syscomments. Is there an equivalent for Indexes?
Thanks ahead of time for your advice!
February 22, 2011 at 11:15 am
The scripts for an index, like the scripts for a table, don't actually get saved/stored the way a procedure or function's body does;
it goes straight into the metadata, so you can regenerate an equivalent command, but not the EXACT command (spacing, comments, etc are gone)
since you are using SQL2000, try this script i wrote way back in 2007 for SQL2000;
http://www.sqlservercentral.com/scripts/Index+Management/31652/
note that by default it's limited to the TOP 100, so change it after you have tested it and are comfortable with it's results.
the follow up discussion for that script, with versions for 2005+
:
Script all indexes as CREATE INDEX statements...
Lowell
February 22, 2011 at 6:19 pm
Thanks, Lowell! I will study your work tonight.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply