Renaming tables in SQL Server can be a common requirement during the software development life cycle. Whether it’s due to software version upgrades, bug fixing, or other development requirements, renaming tables can help keep your database organized and up to date. In this article, we will explore different methods to rename tables in SQL Server.

Using sp_rename Stored Procedure

The sp_rename stored procedure is a built-in feature in SQL Server that allows you to rename user tables. The syntax for using this procedure is as follows:

sp_rename 'old_table_name', 'new_table_name'

The procedure returns a result output of either 0 or a non-zero value. A value of 0 indicates that the procedure execution completed successfully, while a non-zero value indicates failure.

Let’s create a sample table and rename it using the sp_rename procedure:

DROP TABLE IF EXISTS TestOrderList;
DROP TABLE IF EXISTS NewTestOrderList;

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

SELECT * FROM TestOrderList;

EXEC sp_rename 'dbo.TestOrderList', 'NewTestOrderList';

After executing the sp_rename procedure, a warning message is generated: “Caution: Changing any part of an object name could break scripts and stored procedures.” This message serves as a reminder to update the old table name with the new one in any database objects (stored procedures, views, etc.) that reference the old table name, in order to prevent errors.

If you want to work with the return value of the procedure, you can execute the following query:

DECLARE @RetVal AS INT;
EXEC @RetVal = sp_rename 'dbo.TestOrderList', 'NewTestOrderList';
SELECT @RetVal AS ReturnValue;

Renaming Tables with SQL Server Management Studio (SSMS)

In addition to using the sp_rename procedure, you can also rename tables using SQL Server Management Studio (SSMS). This method provides a graphical interface for renaming tables:

1. Open SSMS and navigate to the Tables path.
2. Find the table you want to rename and right-click on it.
3. Select “Rename” from the menu or press the F2 function key as a shortcut.
4. Enter the new table name and press Enter.
5. Answer “Yes” to the message box prompt to complete the table renaming.

Renaming Temporary Tables

SQL Server allows the creation of temporary tables, which are useful for storing temporary data. However, the sp_rename procedure cannot be used to rename temporary tables. Attempting to do so will result in an error.

DROP TABLE IF EXISTS #UserTable;

CREATE TABLE #UserTable (
  UserId INT PRIMARY KEY IDENTITY(1, 1),
  UserName VARCHAR(100),
  UserLoginDate DATETIME
);

EXEC sp_rename 'dbo.#UserTable', '#NewUserTable';

The error message “An invalid parameter or option was specified for procedure ‘sys.sp_rename'” is generated. The reason for this error lies in the creation script of the sp_rename procedure. The script does not allow renaming temporary tables.

However, there is an alternative method to rename temporary tables:

DROP TABLE IF EXISTS #UserTable;

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;

In this alternative method, we create a new temporary table with the desired name, insert the data from the original table into the new table, drop the original table, and finally, select the data from the new table.

Table Renaming and Locking

In some cases, you may need to execute data definition language (DDL) statements in an explicit transaction. When renaming a table within an explicit transaction, the renamed table acquires a schema modification lock (SCH-M). This lock prevents other sessions from accessing the table until the transaction is completed.

BEGIN TRAN;
EXECUTE sp_rename 'old_table_name', 'new_table_name';
COMMIT TRAN;

It’s important to note that SCH-M locks are acquired by sessions when altering the metadata of objects. This can cause other sessions to wait until the renaming transaction is completed.

Let’s illustrate this scenario with an example:

DROP TABLE IF EXISTS Test_UserList;

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

BEGIN TRANSACTION;
EXEC sp_rename 'dbo.OrderList', 'NewOrderList';
WAITFOR DELAY '00:01';
COMMIT TRANSACTION;

In the above example, the table renaming operation is executed within a transaction block, and the Test_UserList table acquires a SCH-M lock. This lock can be observed by executing the following query in another query window:

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 shown in the query results, the NewOrderList table acquires a SCH-M lock.

Conclusion

In this article, we have explored different methods to rename tables in SQL Server. The sp_rename stored procedure provides a simple and efficient way to rename user tables. Additionally, SQL Server Management Studio offers a graphical interface for renaming tables. However, it’s important to note that the sp_rename procedure cannot be used to rename temporary tables. We have also discussed the implications of table renaming on locking, particularly when executing DDL statements within explicit transactions.

By understanding these concepts and techniques, you can effectively manage and organize your SQL Server database by renaming tables as needed.