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)
Thank this author by sharing:
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.
script to create indexes existing in a database
Stored proc for scripting out full CREATE INDEX statements from your table indexes with all filegrou...
Replication Indexes missing
This script creates scripts to compress all tables and indexes in a database.
Generate scripts for all indexes in a database