We all make mistakes at some point of time and we all change our opinion. There are quite a lot of people in the world who have changed their name after they have grown up. Some corrected their parent’s mistake and some create new mistake. Well, databases are not protected from such incidents. There are many reasons why developers may want to change the name of the column or table after it was initially created. The goal of this video is not to dwell on the reasons but to learn how we can rename the column and table.

Earlier I have written the article on this subject over here: SQL SERVER – How to Rename a Column Name or Table Name. I have revised the same article over here and created this video.

There is one very important point to remember that by changing the column name or table name one creates the possibility of errors in the application the columns and tables are used. When any column or table name is changed, the developer should go through every place in the code base, ad-hoc queries, stored procedures, views and any other place where there are possibility of their usage and change them to the new name. If this is one followed up religiously there are quite a lot of changes that application will stop working due to this name change.  One has to remember that changing column name does not change the name of the indexes, constraints etc and they will continue to reference the old name. Though this will not stop the show but will create visual un-comfort as well confusion in many cases.

Here is my question back to you – have you changed ever column name or table name in production database (after project going live)? If yes, what was the scenario and need of doing it. After all it is just a name.

Let me know what you think of this video. Here is the updated script.

USE tempdb
GO
CREATE TABLE TestTable (ID INT, OldName VARCHAR(20))
GO
INSERT INTO TestTable
VALUES (1, 'First')
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the ColumnName
sp_RENAME 'TestTable.OldName', 'NewName', 'Column'
GO
-- Check the Tabledata
SELECT *
FROM TestTable
GO
-- Rename the TableName
sp_RENAME 'TestTable', 'NewTable'
GO
-- Check the Tabledata - Error
SELECT *
FROM TestTable
GO
-- Check the Tabledata - New
SELECT *
FROM NewTable
GO
-- Cleanup
DROP TABLE NewTable
GO

Related Tips in SQL in Sixty Seconds:

• SQL SERVER – How to Rename a Column Name or Table Name

What would you like to see in the next SQL in Sixty Seconds video?

Reference: Pinal Dave (http://blog.sqlauthority.com)