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:
|
1 |
sp_rename 'old_table_name', 'new_table_name' |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP TABLE IF EXISTS TestOrderList; DROP TABLE IF EXISTS NewTestOrderList GO CREATE TABLE TestOrderList ( Id INT PRIMARY KEY IDENTITY(1, 1), OrderCode VARCHAR(100), OrderName VARCHAR(100), DueDate DATETIME ) INSERT INTO TestOrderList VALUES ( 'X120', 'Notebook', '20191212' ), ( 'W201', 'PC', '20191120' ), ( 'KWW901', 'Printer', '20191001' ) GO SELECT * FROM TestOrderList |
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:.
|
1 |
sp_rename 'dbo.TestOrderList' ,'NewTestOrderList' |
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.
|
1 2 3 |
DECLARE @RetVal AS INT EXEC @RetVal=sp_rename 'dbo.TestOrderList', 'NewTestOrderList' SELECT @RetVal AS ReturnValue |
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.
After this step, the table name will be changed as an editable and then we will give the new table name and press Enter.
Answer the message box question as Yes and complete the table renaming.
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:
|
1 2 3 4 5 |
DROP TABLE IF EXISTS #UserTable GO CREATE TABLE #UserTable(UserId INT PRIMARY KEY IDENTITY(1,1), UserName VARCHAR(100),UserLoginDate DATETIME) EXEC sp_rename 'dbo.#UserTable' , '#NewUserTable' |
“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.
|
1 |
EXEC sp_helptext 'sp_rename' |
After that, we will paste and copy the result of a new query window and focus on PHASE 32 part of the script.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS #UserTable GO CREATE TABLE #UserTable(UserId INT PRIMARY KEY IDENTITY(1,1), UserName VARCHAR(100),UserLoginDate DATETIME) INSERT INTO #UserTable VALUES ('TestUser','20190302'), ('QAUser','20190406'), ('ProdUser','20190503') SELECT * FROM #UserTable DROP TABLE IF EXISTS #NewUserTable SELECT * INTO #NewUserTable FROM #UserTable DROP TABLE #UserTable SELECT * FROM #NewUserTable |
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:
|
1 2 3 4 5 |
BEGIN TRAN EXECUTE sp_rename 'old_table_name', 'new_table_name' END TRAN |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DROP TABLE IF EXISTS Test_UserList; GO CREATE TABLE Test_UserList ( Id INT PRIMARY KEY IDENTITY(1, 1), UserCode VARCHAR(100), UserName VARCHAR(100) ) INSERT INTO Test_UserList VALUES ( 'XK04', 'Power User'), ( 'KK12', 'Admin' ), ( 'PL56', 'A' ) |
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.
|
1 2 3 4 |
BEGIN TRANSACTION EXEC sp_rename 'dbo.OrderList' ,'NewOrderList' WAITFOR DELAY '00:01' COMMIT TRANSACTION |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'OBJECT' THEN ( SELECT NAME FROM sys.objects WITH(NOLOCK) WHERE object_id = dm_tran_locks.resource_associated_entity_id ) ELSE ( SELECT NAME FROM sys.objects WITH(NOLOCK) WHERE object_id = partitions.OBJECT_ID ) END AS ObjectName, partitions.index_id, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() ORDER BY request_session_id, resource_associated_entity_id |
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.
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023