Renaming a Column Without Breaking the Scripts and Stored Procedures

Renaming a column without breaking the scripts and stored procedures

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.

Renaming an existing column without downtime

First confirm there are no references to the column from outside the database like application code directly querying the column without going through stored procedures.

Here is how I renamed the column without causing the downtime -

1. Add a new column besides the existing column. The new column has same data type as the old column but the new name.Also create indexes, modify replication etc. on the new column based on the use cases.
2. Modify all stored procedures writing (insert/update operations) to the old column to also insert/update in the new column.
3. Copy over the data from old column to the new column for the existing records. Step 2 and 3, together, now ensure that the new column will remain in sync with the old column.
4. Modify all stored procedures reading from the old column to now read from the new column.

Now that all code has transitioned to use new column, we need to clean up -

1. Modify stored procedures from earlier Step#2 to stop referring to old column.
2. Drop the old column.

How should I rename many Stored Procedures without breaking stuff?

You could make the change in stages:

1. Copy of the stored procedures to the new stored procedures under their new name.
2. Alter the old stored procedures to call the new ones.
3. Add logging to the old stored procedures when you've changed all the code in the website.
4. After a while when you're not seeing any calls to the old stored procedures and you're happy you've found all the calls in the web site, you can remove the old stored procedures and logging.

How to hide Caution: Changing any part of an object name could break scripts and stored procedures

Unfortunately, this message cannot be suppressed.*

There was a Microsoft Connect ticket submitted by our very own Aaron Bertrand, but it was closed as won't fix.

Keep in mind that this message isn't an error - it's not even a warning - it's just a cautionary message saying, "Be careful."

---

^{*Okay, it can be suppressed, but it involves modifying the source code of sp_rename, which is not recommended.}

Mass Renaming of Tables and Stored Procedures

SQLRefactor from RedGate has a smart rename feature. It updates all the references to the renamed objects!

See here:http://www.red-gate.com/products/SQL_Refactor/features.htm

Can I rename a MySQL Column without knowing the existing column name?

I've found a workaround that works for me. I don't know how applicable it is in general, but for my application, it works like a charm.

Since I know the order of the columns in the table from the external source, I simply use a UNION ALL, as follows:

Select 'Id', 'Name', 'Date', 'Title', 0.00 AS 'Value' LIMIT 0
UNION ALL
Select * from original_table

MySQL uses the values as column names except where explicitly aliased.
Note that the LIMIT 0 is required so that you don't have a dummy row.

Proper rename table in sql server

No there's not. You can create a synonym or a view in order to keep backwards compatibility.

Rename column SQL Server 2008

Use sp_rename

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

See: SQL SERVER – How to Rename a Column Name or Table Name

Documentation: sp_rename (Transact-SQL)

For your case it would be:

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'

Remember to use single quotes to enclose your values.

---

Related Topics