Rename Table in SQL Server

In this article I would like to explain about how to rename a table using SQL Server Management Studio (SSMS).

If you look at my previous article on “Load text or csv file data into SQL Server” I created a table called “Cusomer” and loaded the data from Flat file. I am supposed to create “Customer” but ended up missing “t”. I guess "T" is on a vacation during that time :)

Let’s get back to the task. When you are renaming an object you need to make sure
that no-one is using this table. When I say no-one I mean it is not referenced in any other objects like views, stored procedures and etc.

Once you are sure about this then, you can execute the sp_Rename system stored procedure.

SP_RENAME 'CUSOMER','CUSTOMER'

You just have to pass old table and new table name. Once the operation is completed you will get the below message.

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

What if the new table name already exists?? Then you will get below error message.

I tried to rename the CUSTOMER to DEPT.

Msg 15335, Level 11, State 1, Procedure sp_rename, Line 402
Error: The new name 'DEPT' is already in use as a object name and would cause a duplicate that is not permitted.

What if the table that you are trying to rename is referenced in Views? How can you find this? Is there any simple query to do it??

For all these questions please check out my next article.