Skip to content

Rename a Table or Column

2007 May 14
tags: , , ,
by Shannon Lowder

In a previous post, I showed you how to rename a database in Microsoft SQL. If you haven’t already been hit with a request to change the name of a table or column by now… it’s coming. In order to prepare you, I’m not giving you the master sword. It’s almost as helpful, right?

To rename a table using code, execute the sp_rename stored procedure using the following formula:

EXEC sp_rename ExistingTableName, TableNewName;

Here is an example:

EXEC sp_rename 'baddies', 'Enemies';

In this case, the interpreter would look for a table named baddies in the current or selected database. If it finds it, it would rename it Enemies. If the table doesn’t exist, you would receive an error.

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of ‘baddies’ could be found in the current database ‘databaseName’, given that @itemtype was input as ‘(null)’.

To rename a column, first open an empty query window. In a query window, execute sp_rename using the following formula:

EXEC sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

The TableName is the name of the table that the column belongs to. The ColumnName is the current name of the column. The NewColumnName is the desired name you want to give to the column.

Here is an example:

EXEC sp_rename 'baddies.FullName', 'Name', 'COLUMN'

When this code is executed, the interpreter will look for a column named FullName in the baddies table of the current or selected database. If it finds that column in the table, then it renames it name.  Again, if the table or column doesn’t exist, you’ll get an error message.

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 215
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

Pretty easy to use, right?  If you have any questions, please, let me know!  I’m here to help you learn as much as you want in Microsoft SQL Server!

4 Responses leave one →
  1. John Winterton permalink
    July 17, 2011

    This simply doesn’t work (at least in SQL Server 2008). I get the error message:

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘sp_rename’.

    • July 18, 2011

      What were you trying to rename? You have to be specific when renaming: Hit me up on twitter @shannonlowder or via emailat [email protected]. We can work through the issue and get you back up and running in no time.

    • Medalla permalink
      October 3, 2011

      @John. I had the same issue. Use EXEC in front of your sp_rename command and it will work.

      @Shannon, you should update this in your example here.

      • October 4, 2011

        Medalla, I’ll update the demo to include the EXEC command. But, if you highlight just the sp_rename with it’s parameters, you can run it without the EXEC. If you run more than one or more statements before the rename, you will have to included EXEC. I’ve confirmed this using my test instance of 2008. @john, if you have any further questions, please let me know!

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS