Return Index name
Posted on 2011-10-03
I am preparing a script to clean up indexes on a database we ship to clients. I have a table that holds the schema, tablename, indexname, and column names of all indexes that we ship. We have found from experience when undertaking schema changes that clients may have either renamed indexes or added additonal ones for their own purposes.
What I want to do is to use my table to look at each table with our indexes, find the name that exists for any index the we ship, and dynamically write a DROP statement for that index. What is the T_SQL I need for that. We are about to perform a significant upgrade to the schema and I want all indexes out of the way (except their custom ones), and then recreate all after the upgrade using our own names again (that T-SQL I can manage). Code has added complexity of some indexes being on more than one field and table has a row for each field (if necessary, I could consend that to a comma separated list on a single row)