Return Index name

Hello,

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)

Thanks

Kelvin

Kelvin
LVL 23
Kelvin SparksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel WilsonCommented:
this should do it:
select ' Drop Index ' + t.Name + '.' + I.Name from sys.indexes I Inner Join sys.tables T on I.object_id = T.object_id
where I.name is not null

Open in new window

Kelvin SparksAuthor Commented:
That part I can manage. I have to leave client created ones there, but take out any that we have created. Problem is, we have found by prior experience that some clients have renamed our indexes and we don't know the new name - all we know is the table and field(s) that we would have our index, so have to determine the index name dynamically before we can construct the DROP statement.

Kelvin
jogosCommented:
When renamed should you still consider that index to be your index? How do you check this, all other things are same unique/columns/allocation specifications...  if clients are allowed to change?

In general before upgrade you must consider the drop for indexes with the specifications off the old version off your database, the recreation after upgrade must be with the specifications off the new version off your database.  
But again, when in the client's configuration there was a modification for reason off performance tuning this will be gone after upgrade.

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kelvin SparksAuthor Commented:
Yes, if we create an index on a field (or fields) then we manage that - given we will allow other indexes to assist performance, we only want to drop those that we created (irrespective of what the client may have renamed it). We will be adding them back in after the upgrade. WE have run into issues where we want a schema change and have to drop the index to achieve this and the script cannot find the index as it has ben renamed.

Kelvin
jogosCommented:
Point I make is. You will come into different kind of indexes
- created by you and untouched
- created by client : you will let them untouched but they also can harm an upgrade
- created by you and only renamed by client: why and should you still drop it and recreate it with your name again? Client can have name used in a dbreindex?
- created by you and some change by client (name, or column more/less or uniqueness on/off ....)  name is yours but why where the changes? Do you check if there are changes? If there was a good reason for the change why shouldn't it be kept (or recreated) in the client-version? If you allow the change but don't support that change being kept for the future your kind off harassing your client.  
Kelvin SparksAuthor Commented:
We ship the product to just over 100 large sites all over the world (both SQL Server and Oracle databases). Warranty is  valid only if they don't touch schema (we will turn a blind eye to additional indexes here and there) but no changes to our stuff. WE do find a few dba's who think they don't like our naming conventions (don't really blame them there) and who do change things - as part of an upgrade we want our stuff back to stock standard. WE cannot pop down the road a have a look and some client site dbs are huge, so they can't be sent back to us. I guess I can devise a pre-upgrade script to test for our indexes and list the missing ones and say "put em back - or no upgrade"
jogosCommented:
I understand that naming convention-like thing. But I won't let them for their 'like' change a name (or anything else). How do you do support when they mention an error on index IX_tablename when you know it as IX_x1?

For that additional/changed indexes (and other objects) there should also be a rule about it: have create scripts availlable. It can be as simple as allow them to create a stored procedure with a specified name. Your upgrade drops all indexes and at the end checks if that client-procedure exists execute it. The procedure must then recreate the client-indexes, if parameterised it also can drop indexes before upgrade.

And naturaly be sure if any error occurs during upgrade (or operation) caused by a client-index be sure it's not included in any 'fixed price' contract or SLA.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel WilsonCommented:
I agree w/ jogos.  If you let them rename your stuff, upgrades become really tough.

I've got a situation in which some customers will *add* a stored procedure or view or index.  Doing so in a manner that marks it as theirs instead of ours is their problem.  (Of course one of them did expect me to debug their views as part of regular maintenance ... but that's another story.)
Kelvin SparksAuthor Commented:
This discussion is fine for what "should be the case". Unfortunately I have to deal with what IS the case and am not in a position at this point in time to change that. So that brings me back to how do I retrieve an index name if I know the table and field(s) that I expect to be indexed - I need  either a message saying it does not exist, or I need the name so I can pass to dynamic T-SQL to drop it. I can populate a temp table with all the tables and fields.

Kelvin
Daniel WilsonCommented:
yeah ... what is the case ... I hate those things :)

OK, the metadata in SQL 2005 & 2008 involve:

sys.indexes
sys.index_columns
sys.columns
sys.tables

I would model my table listing the indexes & their columns after it, but simplify a little:
MyIndexes
  IndexName
  TableName
  IndexID -- PK

MyIndexColumns
  IndexColID -- PK
  IndexID -- FK
  ColName
  Sequence


Now, a listing in sequence, of a given tables' indexes along with their columns looks like:

Select T.name as TableName, I.name as IndexName, C.name as ColumnName
from sys.tables T Inner Join 
	sys.indexes I on T.object_id = I.object_id INNER JOIN
	sys.index_columns IC on IC.object_id = I.object_id and IC.index_id = I.index_id Inner Join
	sys.columns C on T.object_id = C.object_id and IC.column_id = C.column_id
Order By T.name, I.name, IC.key_ordinal

Open in new window

Daniel WilsonCommented:
Does that get you there ... or should we work through it  a little further?
Kelvin SparksAuthor Commented:
Helped me alomng the way
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.