[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Return Index name

Posted on 2011-10-03
12
Medium Priority
?
307 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:Kelvin Sparks
  • 5
  • 4
  • 3
12 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36906313
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

0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 36906390
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 36908637
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.

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 36909020
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 36909215
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.  
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 36909266
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"
0
 
LVL 25

Accepted Solution

by:
jogos earned 1000 total points
ID: 36909355
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.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36909684
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.)
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 36913020
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
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 1000 total points
ID: 36924327
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

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 36924334
Does that get you there ... or should we work through it  a little further?
0
 
LVL 22

Author Closing Comment

by:Kelvin Sparks
ID: 37221878
Helped me alomng the way
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question