Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 1206 | Views in the last 30 days: 6
 
Related Articles
FORUM

script to create indexes existing in a database

script to create indexes existing in a database

SCRIPT

Scripting CREATE INDEX Statements Automatically From Your Tables

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

FORUM

Replication Indexes missing

Replication Indexes missing

SCRIPT

Script to Compress Tables and Indexes

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

SCRIPT

Script all Indexes

Generate scripts for all indexes in a database

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones