Rename a Table or Column

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';
GO

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'
GO

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!

Tags: , , ,

4 Responses to “Rename a Table or Column”

  1. John Winterton 20110717 at 04:08 #

    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’.

    • Shannon Lowder 20110718 at 17:05 #

      What were you trying to rename? You have to be specific when renaming: http://msdn.microsoft.com/en-us/library/ms188351.aspx Hit me up on twitter @shannonlowder or via emailat slowder@toyboxcreations.net. We can work through the issue and get you back up and running in no time.

    • Medalla 20111003 at 05:55 #

      @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.

      • Shannon Lowder 20111004 at 08:23 #

        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

Notify me of followup comments via e-mail. You can also subscribe without commenting.