SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script out/store all indexes on a server

By MyDoggieJessie,

This nifty little script has bailed me out on a couple of occasions. It's really quite simple and I will definitely admit it probably could be "fixed" up a whole lot, but it does work as-is (but you will need to change the send mail section at the end).

At our company we tend to work a lot with Transaction Replication and often when we've had to take new snapshots of our tables, we lose all of the non clustered indexes that were specifically used for reporting on the tables that were previously replicated. I wrote these scripts (partial snippets taken from various sites across the net) to assist in recreating those indexes, either from issues with replication, accidental deletion/change, or just to feel safe knowing I had their definitions stored somehwere!  

The last script will basically loop through the entire server/database your specify and create the indexes for you, sending you an email for each failed index that fails to get created.

The first script creates a table to store the indexes.

The second script creates the SQL Agent Job.  You can eitherpaste the main segment of code directly into the job step where it says "INSERT CODE FROM ABOVE INTO THIS JOB STEP", or create a stored-procedure, and use that instead.

Deploy the job/procedure to any server you wish to keep back up your index definitions, setting an appropriate schedule for it to run.

Use the final portion of code to loop through the table created in step 1, to automagically create the indexes on the target server.

Total article views: 2067 | Views in the last 30 days: 4
Related Articles

Missing Index Script with Create Statement

standard missing index script but has a create statement to copy and past


script to create indexes existing in a database

script to create indexes existing in a database


Scripting CREATE INDEX Statements Automatically From Your Tables

Stored proc for scripting out full CREATE INDEX statements from your table indexes with all filegrou...


Replication Indexes missing

Replication Indexes missing


Script to Compress Tables and Indexes

This script creates scripts to compress all tables and indexes in a database.