SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video

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:

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

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

3 thoughts on “SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video

  1. What if there are some specific permission granted over that old table and we change the name or create a new table with same name and schema.
    I believe the permission would be lost on new table.
    Is there a secure way to change/create a new table of same schema and data as that of old table so that users of that table would have no impact in Live environment.

    Thanks in advance.

    Like

  2. Hi ,
    lets say we have
    create table (A int, B int, C int ) and we want to change name of column A to B and column B to A at the same time
    what would we do?
    Thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s