List out and script all indexes in a database

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. 😥

After this incident, for every application patch release or change, we made a habit of keeping track of all the indexes in the database. We use below script to pull out the report before and after the release activity. The report would help us review, compare and recreate dropped indexes. The output the script is in such a way that you could save all the create statements in a text file and you could paste the list of indexes into an excel sheet for reporting.

Author: Sherbaz

Being a gadget enthusiast, He always felt good to try new programming languages and techniques. But never goes in-depth anywhere. www.SplitExpense.in was found when he tried php, html, javascript and mysql. Being an electronics engineer, he is also interested to build small gadgets and tools in embedded and digital electronics. As a profession, he handles microsoft sql server database and calls himself a database administrator

Leave a Reply