sql - Renaming a column without breaking the scripts and stored procedures

Question

I want to modify a column name to new name present in a table

but here problem i want to manually modify the column name present in Triggers or SP's.

Is there a any better way of doing it.

To rename a column am using this

sp_RENAME 'Tablename.old_Column', 'new_column' , 'COLUMN';

similarly how can i do it for triggers or SP's.? without opening each script?

Answer 1

Well, there are a bunch of 3rd party tools that are promising this type of "safe rename", some for free and some are not:

• ApexSQL has a free tool for that, as MWillemse wrote in his answer,
• RedGate have a commercial tool called SQLPrompt that also have a safe renaming feture, However it is far from being free.
• Microsoft have a visual studio add-in called SQL Server Data Tools (or SSDT in the short version), as Dan Guzman wrote in his comment.

I have to say I've never tried any of these specific tools for that specific task, but I do have some experience with SSDT and some of RedGate's products and I consider them to be very good tools. I know nothing about ApexSQL.

Another option is to try and write the sql script yourself, However there are a couple of things to take into consideration before you start:

• Can your table be accessed directly from outside the sql server? I mean, is it possible that some software is executing sql statement directly on that table? If so, you might break it when you rename that column, and no sql tool will help in this situation.
• Are your sql scripting skills really that good? I consider myself to be fairly experienced with sql server, but I think writing a script like that is beyond my skills. Not that it's impossible for me, but it will probably take too much time and effort for something I can get for free.

Should you decide to write it yourself, there are a few articles that might help you in that task:

First, Microsoft official documentation of sys.sql_expression_dependencies.
Second, an article called Different Ways to Find SQL Server Object Dependencies that is written by a 13 years experience DBA, and last but not least, a related question on StackExchange's Database Administrator's website.

You could, of course, go with the safe way Gordon Linoff suggested in his comment, or use synonyms like destination-data suggested in his answer, but then you will have to manually modify all of the columns dependencies manually, and from what I understand, that is what you want to avoid.