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

I often get requests from blog reader for T-SQL script to rename database table column name or rename table itself.

Here is a video demonstrating the discussion

The script for renaming any column :
sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

The script for renaming any object (table, sp etc) :
sp_RENAME '[OldTableName]' , '[NewTableName]'

This article demonstrates two examples of renaming database object.

  1. Renaming database table column to new name.
  2. Renaming database table to new name.

In both the cases we will first see existing table. Rename the object. Test object again with new name.

1. Renaming database table column to new name.

Example uses AdventureWorks database. A small table with name “Table_First” is created. Table has two fields ID and Name.

Now, to change the Column Name from “Name” to “NameChange” we can use command:

USE AdventureWorks
GO
sp_RENAME 'Table_First.Name', 'NameChange' , 'COLUMN'
GO

Following Fig. show use of SP_RENAME Command

You can see the column name “Name” is now changed to “NameChange“.

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

Following fig. verify that the column name has been changed.

2.Renaming database table to new name.

We can change the table name too with the same command.

sp_RENAME 'Table_First', 'Table_Last'
GO

Following fig. Shows how we can change Table Name.

Now, the table name “Table_First” is renamed as “Table_Last”.

“Table_First” will no longer be available in database. We can verify this by running script:

USE AdventureWorks
GO
SELECT *
FROM Table_First
GO

The Messages shows an error “Invalid object name ‘Table_First’.”

To check that the new renamed table exist in database run script:

USE AdventureWorks
GO
SELECT *
FROM Table_Last
GO

You can see the same data now available in new table named “Table_Last”

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

About these ads

203 thoughts on “SQL SERVER – How to Rename a Column Name or Table Name

  1. Hi Pinal,

    If we change the cloumn name, suppose if the column has a index on it, will the change of name effect the index already created.

      • If u r having constraint also, U can use the same syntax. It will work

        CREATE TABLE Employee
        (
        Empid int not null
        ,Empname varchar(20)
        )

        alter table Employee add constraint pk_simple primary Key(empid)

        sp_rename ‘employee.empname’,’EmployeeName’,’column’

  2. Pingback: SQL SERVER - Renaming SP is Not Good Idea - Renaming Stored Procedure Does Not Update sys.procedures Journey to SQL Authority with Pinal Dave

  3. I read your articles it is fabulous!!!!
    pls try to understand following coding in sql server 2005

    SELECT C.NAME,C.ADDRESS,SUM(O.QUANTITY*P.PRICE)
    FROM MYCUSTOMER C JOIN ORDERS O
    ON C.NAME=O.NAME JOIN PART P
    ON O.PARTNUM=P.PARTNUM

    Its giving error:–column ‘MYCUSTOMER.NAME’ is invalid in the select list because it is not contained in either an aggregate function or the group by clause

    I tried many thing but nothing works
    plz look into it…..
    thanks in advance!!

    • – You must add the “Group By” clause inorder to use the Sum Function

      SELECT C.NAME,C.ADDRESS,SUM(O.QUANTITY*P.PRICE)
      FROM MYCUSTOMER C JOIN ORDERS O
      ON C.NAME=O.NAME JOIN PART P
      ON O.PARTNUM=P.PARTNUM
      Group By C.NAME,C.ADDRESS

      Cheer,
      Momo
      Glory be to God!

    • if u r using aggreage function, then the columns other than the one u use the aggreagate function should be grouped used group by clause

      group by c.name, c.address resolve ur issue

  4. This is not supported going forward. Avoid using SP_RENAME where possible or you will have to edit your code when you upgrade to SQL 2008..

  5. Hi Pinal,
    I think there is no need to pass third parameter as ‘column’ for renaming the column.

    sp_rename ‘Table_First.Name’, NameChange

    i have just tried without mentioning ‘column’ and it works fine.
    May i know what is the importance of the third parameter ‘column’? Is it required or not?

  6. I have used the renaming mistakenly and my column name now looks like this “[column]” without the quotation mark but including the [] charecters. How can I reissue the command to rename the column? what escape sequence should I use.

  7. I have used the renaming mistakenly and my column name now looks like this “[column]” without the quotation mark but including the [] charecters. How can I reissue the command to rename the column? what escape sequence should I use.

    eg:: if coloumn name is txtname
    after renaming using sp_rename with new coloumn name it is showing [txtname_new].

    Now I cant even retrieve the values for that coloumn.
    Please help me guys on this…

    Thanks in advance.

  8. This is a great find, but it’s not exactly what I need.

    We’re looking at upgrading our servers from 2005 to 2008. This will also involve a name change for our servers. Is there an easy way we can update the cross server links in our views, sp etc withouth having to go into each view and sp?

    Thanks,

    Eloise

  9. Thanks pinaldave for a great blog! So much useful information you’ve gained through hard experience you share with us all!

    Here’s a note I would like to add to this article. Do not use square brackets in your target column name or it will put them in there literally! For example:

    sp_rename ‘[my table].[my col]‘, ‘[my col2]‘, ‘COLUMN’

    is WRONG! Instead say:

    sp_rename ‘[my table].[my col]‘, ‘my col2′, ‘COLUMN’

    And what to do if you have columns with brackets that need renaming? You have to use 2 brackets up front and 3 brackets behind!!

    sp_rename ‘[my table].[[my col2]]]’, ‘my col2′, ‘COLUMN’

    Another thing, I figured out this syntax by trial and error (on SQL Server 2008.) It also is needed when doing an aggregate on a column with brackets in its name:

    select SUM([[my col2]]])

    • Thank you very much for the “2 brackets up front and 3 brackets behind” discovery!
      The square brackets are actually not needed.
      Another example when google “sp_rename”:
      EXEC sp_rename ‘Sales.SalesTerritory.TerritoryID’, ‘TerrID’, ‘COLUMN’;

  10. Can we change the name and the data type of a column at the same time. I know that we can change the data type first and then rename the column. Can we club these 2 actions together in SQL Server 2005?

  11. sp_rename ‘Mkt_Mart_vw.flat_site1′,’@tablename’
    does not seem to work..
    the new tablename is basically a parametrized table name to be input by user

  12. Does sp_rename depend on how huge the table and data is? Would it take the same amount of time if I did the rename of a columns containing 1 row and 10 million rows?

  13. Hello srinivas,

    Your doing great job.It’s really very very useful for all the developers..

    Thanks a lot srinivas.

  14. I have 2 questions :

    1. Suppose we have 20 tables which all of them have got the same columns (the number of the columns and the name of the columns are all same ). How can I combine and convert all of them into a single Table.

    2. Imagine we have the same case as explained above( 20 tables with same columns ), so we want to change the name of each tables to the same name which is in first row of Third column of each table.

    any one have got some codes or solution for that ?

    Thx

  15. 1) use select into. you need to define the relation between all tables which you can use in your where condition.
    So lets assume you have 3 table with same columns and …….
    then select a.*(or any perticualr clolumn name), b.*,c.*
    where a.id=b.id(whatever matching column) and a.id = c.id(again matching column) into temp
    from table1 a, table2 b, table3 c

    you can modify matching and column name as per your requirement.

    2) Lets say you have third column name as ‘name’
    then
    declare @name as varchar(250)
    select top 1 @name = name from table 1
    sp_rename ‘table1′, ‘@name’

    Now again, how to get the requried top 1 is depend as per your requirement. I have just assumed that whatever comes in top without sorting or filtering

  16. Dear Ashish :

    Thank you so much . Im gonna try ur solution. If any question regarding this issue ,shall I write here or just mail to you ?
    thx

  17. nice to hear it worked.

    just post here because:-
    1) it will be have more visibility and more quick chance to reply
    2) I have configured this blog in my office mail so I will get it in my office mail box automatically whenever there is new item added.

  18. Hi Pinal,
    I’m new to SQl, and am trying to convert all the column names of a table to UPPERCASE. There are around 80 columns. What is the code for that?
    Thanks in advance!

  19. Dave, your site has been a great help over the past few months as I’m learning new things about Sql Server. I’ve learned many things from you. Thank you very much! I appreciate the easy access to knowledge and hope to contribute back down the road a bit when I’m able.

  20. Hi

    When i am trying to change the column name of the table containing some data its giving me the below caution

    Query: sp_rename ‘products1.prodname’,’productsname’

    Caution: Changing any part of an object name could break scripts and stored procedures.

    what does this caution mean.

    Will be waiting for reply.

    Thanks in advance

      • Madhivanan

        Thanks for the reply. But names used in the example ie “products1.prodname’,’productsname’” are not any procedures.
        But still its giving me problem.
        Please let me know how to change the column name

  21. Hi Sir,

    I understood abt how to rename a column. But I want to know, how can i rename the data type.. Has it to be done before renaming the column or can it be done even after that?
    I need to know the query of renaming the data type.

    Thanks & Regards,
    Ramya

  22. I renamed a table with sp_rename. It changed the table name but not constraints. The constraints remain with the old table name eg.PK_OldName. Is it can effect the other things.

  23. when i m changing the column name in hash table it gives me error like this..is it possible to change column name by using hash table values??

    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

    • Hi! Don’t use hash table to rename, try temp schema if you have like TEMP. then use SP_RENAME, this could help you

  24. getting error when i execute this;

    EXEC sp_rename’[#Result].[Normalized_Domain]‘,’Domain’,’COLUMN’

    error:
    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

  25. Hi Dear,

    sp_RENAME ‘TableName.[OldColumnName]‘ , ‘[NewColumnName]‘, ‘COLUMN’

    sp_RENAME ‘[OldTableName]‘ , ‘[NewTableName]‘

    both query will run.

    Md Jiaul Islam

  26. Hi,

    I am trying to write a code to update a column of the table using the results of an inner join… can anybody help me out??

    vihar

  27. Hi I have renamed the column with following command.

    sp_RENAME ‘dbo.tCust.CustomerID’ , ‘[CustID]‘, ‘COLUMN’

    now it sets the column name [CustID] with braces, now I am unable to remove the braces. Please help me out.

    Thanks

  28. Its been solved with following command :)

    sp_RENAME ‘dbo.tCust.[[CustID]]]’ , ‘CustID‘, ‘COLUMN’

    Thanks for great article

  29. Hi Pinal,

    Could you please help me in below scenario-
    I need to modify a column length in a big table (300,000,000) records.
    What approach is good-
    1. Drop and recreate the table
    2. ALTER the column with the new length?

  30. I ran this command and it changed the column name fine. But then when I tried to change the column reference in one of my stored procedures, I would get an error that it couldn’t find the column when I ran the ALTER. I even closed the connection and opened it again it still had the same problem. It was really weird, I ended up just recreating the table

  31. Hi Pinal,
    Could you please help me with the following?
    I’m trying to rename all the column names of my date dimension, from lower case to Title case. There’re around 110 columns, and I don’t want to manually rename them.
    Could you please offer the code to do this?

    Thanks in advance!

  32. Hi Sir,

    Which one is faster? Truncating a table and doing a fresh insert of nearly 2million rows OR inserting the 2million into a new table, delete the original table and rename the new table to the original table.
    Oh den do an incremental population of the fulltext index afterwards.

  33. Dear Sir,
    Firstly Thanks a lot for your guidance. I am able to write query in sql just because of your notes. Your tutorials are very helpfull for me. I want to go for DBA, but i am not aware how i can achieve it. Please guide me for it and for the oracle certification. So please guide me for it.

  34. Hi,

    What all can we include in SSAS Cube Design documentation

    I have included the data model,facts,dimensions,surrogate keys,Hierarchies and levels within hierarchies.
    Reports that we can pull from the cube,Proactive caching.

    What else can we include.
    I’m confused.Please help me out.

    Thanks in Advance,
    Samyuktha

  35. hi,
    I had backup using select * into table tablename_bak from tablename

    after that i rename table using your script

    sp_RENAME ‘tablename’, ‘Ttablename_old’
    GO

    sp_RENAME ‘tablename_bak’, ‘Ttablename’
    GO

    when I open from web application the page is white blank,
    I i rename back to original

    sp_RENAME ‘tablename’, ‘Ttablename_bak’
    GO

    sp_RENAME ‘tablename_old’, ‘Ttablename’
    GO

    The page displayed data correctly,

    please advice how this can happen.

    thanks before.
    Liliek

  36. Hi Pinal Dave,
    Is there a way to alter columns’ name which contain a certain characters (i.e. ‘asset’) in all tables , instead of altering it manually one by one?

    Thank you.

  37. Hi , I renamed a table by mistake using the F2 key in SSMS and forgot the old table name :(. Is there any way I could get the old name or undo the rename?
    I would really really appreciate any help.

  38. Any idea how can I run it for multiple column name updates … I was trying to use excel to update the SQL, and then run it at once, but it gives an error (they work fine separately) :(..
    Trying something like
    sp_RENAME ‘[IRIOnly].Geography’ , ‘Location’, ‘COLUMN’
    sp_RENAME ‘[IRIOnly].PriceCat’ , ‘SKU’, ‘COLUMN’
    sp_RENAME ‘[IRIOnly].Brand-adj’ , ‘Brand’, ‘COLUMN’
    sp_RENAME ‘[NetList].PriceCat’ , ‘SKU’, ‘COLUMN’
    Any help will be appreciated..

    • When you execute multiple statements you need to use EXEC command

      EXEC sp_RENAME ‘[IRIOnly].Geography’ , ‘Location’, ‘COLUMN’
      EXEC sp_RENAME ‘[IRIOnly].PriceCat’ , ‘SKU’, ‘COLUMN’
      EXEC sp_RENAME ‘[IRIOnly].Brand-adj’ , ‘Brand’, ‘COLUMN’
      EXEC sp_RENAME ‘[NetList].PriceCat’ , ‘SKU’, ‘COLUMN’

  39. Brother, help my in script !!!

    Locate error the script, please …

    select ‘exec sp_rename “‘+table_name+’”,”‘+substring(table_name,1,3)+’990′ +’”‘ from information_schema.tables

    Tank’s

  40. Hi Pinal,
    This is my first comment on your blog so let me start with thanking you for tremendous blog, of your’s, that has saved countless hours of head banging for me.

    So here’s my question..
    One of our DBAs has accidentally renamed a table by clicking on that in SSMS. We do have a backup of the database to retrieve the DB and getting the table name restored. But I was wondering if there was a quick UNDO option for name changes like these.

  41. sp_RENAME ‘[department_info]‘ , ‘[maintenence.info]‘

    after that i am not able to view below query

    select * from maintenence.info

  42. Hello Pinal,
    I have list of tables like A,B,C,D,E..
    All this tables i have to rename with different names

    e.g.
    SP_Rename
    ‘A’ to ‘AB’,
    ‘B’ to ‘BC’,
    ‘C’ to ‘DE’,
    ‘D’ to ‘DE’

    Is it Possible with SP_Rename?

    Urgent Reply please i m in big trouble..

    Thanks in advance..

  43. Hi PinalDave,

    My question was ” how to rename the multiple tablenames as dynamically”.
    See for example my db having five tables with same structure and these tables contails data also: temp_site1,temp_site2,temp_site3,temp_site4 and temp_site5.
    I need to change all table name as ” site1,site2,site3,site4 and site5 “. Is any query or some other way to solve.
    Thanks in advance….

  44. Pingback: SQL SERVER – Rename Columnname or Tablename – SQL in Sixty Seconds #032 – Video « SQL Server Journey with SQL Authority

  45. Hi,i I want to know that the message “caution :Changin g any part of the object can break the script ” would be there any impact if the data is very large.

  46. sp_RENAME ‘TableName.[OldColumnName]‘ , ‘[NewColumnName]‘, ‘COLUMN’

    this works great if you want brackets around your new column name

  47. Hello, I used the sp_rename to rename my column name like:
    sp_RENAME ‘copyTableTo.[date]‘ , ‘[date1]‘, ‘COLUMN’
    But now when I wants to change my column name from “date1″ to “date” like:
    sp_RENAME ‘copyTableTo.[date1]‘ , ‘[date]‘, ‘COLUMN’
    It is giving following error:
    **”Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.”**

  48. Hi gravit,
    Please have a look into a post

    http://www.bidn.com/blogs/Anil/ssas/4289/think-while-rename-a-column-name-of-table-in-sql

    Well there is a method for changing the mistakenly rename column ‘[date1]’ to date . Just simple you need to go to the table and then click plus sign of the table and then you can see the list of columns of table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[date1]’ to date . This will solve the issue we are having.
    Thanks,
    Anil Maharjan

  49. If you need to rename a column in a stored procedure and your working with a temp table. This worked for me!

    exec (‘use tempdb; EXEC sp_rename ”#tempRpt3.[YEAR]”, ”QUESTION”, ”COLUMN” ‘)

  50. I have a Column name like ” [year] ” open bracket Year close bracket .
    I tried to rename the this column into Year with below query

    EXEC sp_rename
    @objname = ‘Student.[Year]‘,
    @newname = ‘Year’,
    @objtype = ‘COLUMN’

    and I am getting below error

    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

    tried to drop that column but getting error while drop the column

    anyone suggest me how to delete or drop that column

    Thanks,
    Karna

    • Got Soluation with below code.

      EXEC sp_rename
      @objname = ‘Student.[[Year]]]’,
      @newname = ‘Year’,
      @objtype = ‘COLUMN’

  51. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  52. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

  53. sp_RENAME ‘TableName.[OldColumnName]‘ , ‘[NewColumnName]‘, ‘COLUMN’ –> is incorrect

    Please change this as it will insert square brackets [] as part of the the new column name.

  54. After using this sp_RENAME ‘TableName.[OldColumnName]‘ , ‘[NewColumnName]‘, ‘COLUMN’
    if we again rename the coulmn it gives error:
    Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.
    why?

  55. Pingback: SQL SERVER – Rename a Table Name Containing [ or ] in the Name – Identifier in the Table Name | Journey to SQL Authority with Pinal Dave

  56. there is a problem with the rename column script mentioned here. It adds square braces in the renamed column.

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