Post reply

Capturing Index script programmatically

  • February 22, 2011 at 11:00 am

    #74629

    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

    #1289992

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 22, 2011 at 6:19 pm

    #1290198

    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