Renaming a column without breaking scripts and stored procedures

Question

I want to change the column name to a new name existing in the table

but here's the problem: I want to manually change the column name present in Triggers or SP's .

Is there a better way to do this.

In rename column am using this

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

similarly, how can I do this for Triggers or SP's .? without opening each script?

Answer 1

Well, there are tons of third-party tools that promise this type of “safe renaming”, some for free and some not:

• ApexSQL has a free tool for this, as MWillemse wrote in his answer
• RedGate has a commercial tool called SQLPrompt , which also has secure renaming, but it is far from free.
• Microsoft has a visual studio add-in called SQL Server Data Tools (or SSDT in short version), as Dan Guzman wrote in his comment.

I must say that I have never tried any of these specific tools for this specific task, but I have some experience with SSDT and some RedGate products, and I consider them to be very good tools. I don't know anything about ApexSQL.

Another option is to try writing a sql script yourself. However, before you begin, you need to take a few things:

• Is it possible to access your table directly from outside the sql server? I mean, is it possible that some software executes the sql statement directly in this table? If so, you can break it when you rename this column, and no sql utility will help in this situation.
• Are your sql script writing skills really that good? I consider myself quite experienced with sql server, but I think writing a script like this is beyond my skills. It’s not that it was impossible for me, but it will probably take too much time and energy for something that I can get for free.

If you decide to write it yourself, there are several articles that can help you in this task:

First, the official Microsoft documentation sys.sql_expression_dependencies .
Secondly, an article titled Different Ways to Search for Dependencies of SQL Server Objects , written by 13 years of DBA experience, and, just as important, a related question on the StackExchange database admin website.

You could, of course, go in the safe way suggested by Gordon Linoff in your comment, or use synonyms such as destination data in your answer, but then you have to manually modify all the column dependencies manually, and from what I understand, this is what do you want to avoid.

Answer 2

• Rename a table column
• Delete a table column
• Change Key Tables

The best way to use database projects in Visual Studio. See Links

reference 1

reference 2

Answer 3

• you can do what @GorDon suggested.

• In addition, you can also play with this request,

select o.name, sc. * from sys.syscomments sc internal join sys.objects o to sc.id = o.object_id, where sc.text, for example '% oldcolumnname%'

this will return a list of all proc and trigger. You can also change the filter to get an accurate list. Then you can easily change it manually.

But whatever you decide, don't just drop the old column. To be safe, even maintain a backup.

Answer 4

You might want to replace this text in the definition. However, you will need a dedicated admin connection on the sql server. Versions also vary when setting up a special administrator connection. Setting the startup parameter by adding: -T7806 in advanced mode. And adding Admin: in front of the server name at login. By then, you can change the value of the definition.