Esat Erkec
Renaming the temp tables

How to rename tables in SQL Server with the sp_rename command

October 7, 2019 by

In this article, we will learn to rename tables in SQL Server. sp_rename is a built-in stored procedure which helps to rename user tables in the SQL Server. During the software development life cycle, we might be faced with various scenarios that required to rename the existing tables in the database. For example, the tables which are created in the SQL Server might be needed to be renamed, because of the software version upgrades, bug fixing or other various development requirements. These and similar reasons can make renaming tables important. So, in this article, we will particularly focus on how to rename the tables in SQL Server.

How to rename a table in SQL Server

The sp_rename is a stored procedure which helps to rename tables in SQL Server and the usage syntax will be like the below:

The result outputs of this procedure might be 0 or non-zero values. 0 value indicates that the procedure execution successfully completed and non-zero values indicate failure. Now, let’s create a sample table and then rename this table name:

Creating a sample table in SQL Server

As we can see TestOrderList table storing some data, however, renaming the table does not influence the TestOrderList data only it makes modification in the name of the tables. The following query renames the TestOrderList table to NewTestOrderList:.

sp_rename result message

After the execution of the sp_rename procedure, it generates a warning message like the below one:

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

The purpose of this message is to notify about that if you rename a table, you have to change the old table name with the new one in the database objects (stored procedure, view, etc), which include the old table name. Thus, we can prevent errors that can be caused by renamed tables. If we want to work with the return value, we can execute the table below. Before executing the following query, re-run the sample table creation script.

sp_rename usage with output parameter

How to rename a table in SQL Server with SSMS

In order to rename a table, we can follow another method procedure through the SQL Server Management Studio, which is different than the sp_rename option. We can find out the table name whose name will change under the Tables path of the SSMS and then click the Rename in the menu or we can press to F2 function key as a shortcut.

Rename tables in SSMS

After this step, the table name will be changed as an editable and then we will give the new table name and press Enter.

Rename tables in Management Studio

Answer the message box question as Yes and complete the table renaming.

Rename tables in SQL Server Managment Studio message box

How to rename a temporary table name in SQL Server

SQL Server allows creating temporary tables and temporary tables provide to store data in it. However, we can not use the sp_rename to rename the temporary tables. Now, let’s prove this case and find out the reason. When we try to execute the following query, we will get an error like this:

How to use sp_rename with temp tables

“An invalid parameter or option was specified for procedure ‘sys.sp_rename’.” Actually, the reason for error details is hidden in the procedure creation script depths. In order to figure out the details of this issue about the temporary table renaming, we should analyze the creation script. Now, let’s apply the following steps and solve the mystery.

At first, we will run the following query in order to obtain the creation script of the sp_rename.

sp_rename creation script

After that, we will paste and copy the result of a new query window and focus on PHASE 32 part of the script.

sp_rename procedure script details

PHASE 32 section identifies the reason for the error, obviously. If the old table name or the new table name parameter first character is equal to # sign it returns an error. Therefore, we can not rename the temp tables. So, how can we overcome this issue? The following query offers an alternative method to rename the temporary tables.

Renaming the temp tables

As we can see, we renamed the temporary tables in an alternative method, but this method is not suitable for the user tables if they contain a big amount of data and other considerations.

Table renaming and locking

In some cases, we required to execute the DDL (data definition language) statements in an explicit transaction. Starting from this point we can execute sp_rename like the below in an explicit transaction:

However, the renamed table acquires schema modification lock (SCH-M) during the renaming transaction, for this reason, other sessions will wait until the completion of this transaction. SCH-M acquired by sessions when altering the metadata of the objects. (For more details about the DDL statements locking issue, you can see the SQL Server lock issues when using a DDL (including SELECT INTO) clause in long-running transactions article). Now, we will reinforce this scenario with the following example.

In this step, we will create a sample table and then we will rename it in a explicit transaction:

In the following query, the table renaming operation will be executed in a transaction block and then the Test_UserList table will be acquired SCH-M lock.

Now we will open another query window and execute the following query. so that we are able to figure out the lock type which is created by the table renaming session.

Analyze and identify the Schema modification lock (SCH-M)

As we can see in the above illustration showed, the NewOrderList table acquired Sch-M lock.

Conclusion

In this article, we learned how to rename tables with the help of the sp_rename procedure with different aspects in SQL Server. This procedure is very helpful and pretty simple to rename tables.

Esat Erkec
SQL commands, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

168 Views