SQL, How to change column in SQL table without breaking other dependencies?

Question

I'm sure this might be quite common query but couldn't find good answer as for now.

Here is my question:

I've got a table named Contacts with varchar column Title. Now in the middle of development I want to replace field Title with TitleID which is foreign key to ContactTitles table. At the moment table Contacts has over 60 dependencies (other tables, views functions).

How can I do that the safest and easiest way?

We use: MSSQL 2005, data has already been migrated, just want to change schema.

Edit:

Thanks to All for quick replay.

Like it was mentioned Contacts table has over 60 dependents, but when following query was run, only 5 of them use Title column. Migration script was run, so no data changes required.

/*gets all objects which use specified column */

SELECT Name FROM syscomments sc JOIN sysobjects so ON sc.id = so.id WHERE TEXT LIKE '%Title%' AND TEXT LIKE '%TitleID%'

Then I went through those 5 views and updated them manually.

Answer 1

Use refactoring methods. Start off by creating a new field called TitleID, then copy all the titles into the ContactTitles table. Then, one by one, update each of the dependencies to use the TitleID field. Just make sure you've still got a working system after each step.

If the data is going to be changing, you'll have to be careful and make sure that any changes to the Title column also change the ContactTitles table. You'll only have to keep them in sync while you're doing the refactoring.

Edit: There's even a book about it! Refactoring Databases.

Answer 2

As others pointed out it depends on your RDBMS.

There are two approaches:

• make a change to the table and fix all dependencies
• make a view that you can use instead of direct access to the table (this can guard you against future changes in the underlying core table(s), but you might loose some update functionality, depending on your DBMS)

Answer 3

Use SP_Depend 'Table Name' to check the Dependencies of the table and then Use the SP_Rename to Rename the Column Name which is very useful. sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

and then start Updating the Procedure and Functions one by one there is no other good option for change like this if you found then tell me too.

Answer 4

For Microsoft SQL Server Redgate have a (not free) product that can help with this refactoring http://www.red-gate.com/products/sql_refactor/index.htm

In the past I have managed to do this quite easily (if primitively) by simply getting a list of things to review

SELECT * FROM sys.objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Contacts%' 

(and possibly taking dependencies information into account and filtering by object type)

Scripting all the ones of interest in Management Studio then simply going down the list and reviewing them all and changing the CREATE to ALTER. It should be quite a simple and repetitive change even for 60 possible dependencies. Additionally if you are referring to a non existent column you should get an error message when you run the script to ALTER.

If you use * in your queries or adhoc SQL in your applications obviously things may be a bit more difficult.