Renaming Tables in SQL Server

Renaming a table in a database is one of the most common tasks a DBA will carry out. In this article, you will see how to rename tables in SQL Server. 

Depending upon the client application that you use to manage your SQL Server, there are multiple ways of renaming tables in SQL Server. Some of the ways involve text queries while the other ways allow you to rename tables in SQL Server via GUI. 

In this article, you will see five main ways to rename tables in SQL Server:

1. SQLCMD Utility

2. SQL Server Management Studio Query Window

3. SQL Server Management Studio GUI

4. SQL Query Window in dBForge Studio for SQL Server

5. GUI in dBForge Studio for SQL Server

As an example, you will be renaming a fictional “Item” table in the SALES database. The following script can be used to create such a table.

CREATE DATABASE SALES
USE SALES
CREATE TABLE Item (
Id INT,
Name varchar(255),
    Price FLOAT
);

Renaming Table Using SQLCMD Utility

SQLCMD is a command line tool that can be used to perform various operations on SQL Server. The SQLCMD utility can also be used to rename tables in SQL. 

To open the utility in windows, open the “Run” shell, and enter the command sqlcmd -S  server_name -E . Here, "E" specifies that Windows Authentication is enabled to access the SQL Server. If Windows Authentication is not enabled, you will have to replace -E with the  -U your_user -P your_password command. 

The SQLCMD utility will open where you can execute SQL commands to perform different operations on your SQL Server instance. 

Before we rename our Item table from the SALES table, let’s first print the table name. You can do so like this:

SELECT name FROM SALES.sys.tables

In the output, you will see the names of all the tables in the SALES database, as shown in the output below:

There is no direct SQL Query that can be used to rename a table in SQL Server. You need to execute the sp_rename stored procedure to rename a table in SQL Server. 

The syntax for renaming a table in SQL Server via the sp_rename stored procedure is as follows:

EXEC sp_rename 'old_table_name', 'new_table_name'   

As an example, you will rename the 'Item' table as 'Product'. Here is how you can do it via SQLCMD utility:

From the output of the above command, you can see a warning which says that changing any part of an object’s name has the potential to break scripts and stored procedures. 

This warning is important because if you have a script that interacts with the 'Item' table using the name 'Item', that script will no longer execute since the table name is changed. 

Finally, to see if the table has actually been renamed, you can again execute the following script:

SELECT name FROM SALES.sys.tables

As you can see above, the table 'Item' has been renamed to Product.

It is important to mention that if your original table name contains a dot [.] in it, you won’t be able to rename it directly. 

For instance, if your SALES table has a table 'Product.Items' that you want to rename as 'Items', the following script will throw an error:

USE SALES
EXEC sp_rename 'Product.Items', 'Items' 

The error says that no item with the name 'Product.Items' could be found in the current database.

To rename a table that contains a dot in its name, you have to enclose the table name within square brackets as shown in the following script:

USE SALES
EXEC sp_rename '[Product.Items]', 'Items'  

From the output below, you can see no error or warning, which means that the table has successfully been renamed. 

Renaming Table Using SQL Server Management Studio

SQL Server Management Studio is a GUI-based tool developed by Microsoft that allows you to interact with SQL Server instances. SQL Server Management Studio can also be used to rename tables in SQL Server.

There are two main methods of renaming SQL Server tables via SQL Server Management Studio. You can either use the SQL Server query window, or you can directly rename a table via a mouse’s right-click in the GUI. You will see both methods in the following sections:

Renaming Table Using SQL Query Window

To rename a table via the SQL query window in SQL Server Management Studio, click the New Query option from the main dashboard of your SQL Server Management Studio as shown in the following screenshot. 

You can also see the “Item” table in the “SALES” database in the following screenshot. This is the table that you will be renaming. 

The script for renaming a table via the SQL query window is the same as the query you executed in SQLCMD. You have to execute the sp_rename stored procedure as shown in the following script.

USE SALES
EXEC sp_rename 'Item', 'Product' 

In the output message window, as shown in the following screenshot, you can again see the message which warns you that changing an object name can break the script.

You can use the command below to see if your table is renamed. 

Alternatively, you could right-click the database i.e. SALES -> Tables, and click the “Refresh” button from the list of options. You will see your renamed table. 

SELECT name FROM SALES.sys.tables 

It is worth mentioning that just as you saw with the SQLCMD utility, renaming a table whose name contains a dot operator, requires enclosing the table name inside square brackets. 

For instance, if you want to rename the 'Product.Items' table to 'Items', the following query will through an error:

USE SALES
EXEC sp_rename 'Product.Items', 'Items'

On the other hand, enclosing the table name inside the square brackets will result in the successful renaming of the table, as shown in the output of the script below:

Renaming Table Using SSMS GUI

SQL Server Management Studio provides a lot of one-click options to perform different tasks. You can rename a table via SQL Server Management Studio GUI. 

To do so, right-click on the table that you want to rename. From the list of options that appear select Rename as shown in the following screenshot. 

You will see that the text editing option will be enabled for the table that you want to rename, as shown in the below screenshot.

Here enter the new name for your table and click enter. Your table will be renamed. 

Rename Table Using dBForge Studio for SQL Server

dBForge Studio for SQL Server is a flexible IDE that allows you to perform a range of database management, administration, and manipulation tasks on SQL Server using an easy-to-use GUI.

dBForge Studio for SQL Server also allows you to rename tables in SQL Server.

Just like SQL Server Management Studio, you have two options for renaming tables. You can either use the query window where you can execute SQL scripts for renaming tables, or you can directly rename a table by right-clicking a table name and then renaming it. You will see both options in this section.

Connecting dBForge Studio With SQL Server

Before you can perform any operations on SQL Server via the dBForge Studio, you first have to connect the dBForge Studio with the SQL Server instance.

To do so, click the New Connection button from the main dashboard of dBForge Studio.

You will see the Database Connection Properties window as shown below. Here, enter the name of your SQL Server instance that you want to connect to, along with the authentication mode. Enter your user and password if needed and click the Test Connection button.

If your connection is successful, you will see the following message:

Renaming Tables Using SQL Query Window in dBForge Studio

To rename tables using the SQL query window in dbForge Studio for SQL Server, click the New SQL option from the top menu. An empty query window will open where you can execute your SQL queries. Look at the following screenshot for reference: 

The query to rename a table remains the same as you in the previous sections. 

You use the sp_rename stored procedure. 

The following script renames your 'Item' table in the SALES database to 'Product'.

USE SALES
EXEC sp_rename 'Item', 'Product'  

The output below shows that the query was successful. 

To see if the Item table has actually been renamed, run the script below:

SELECT name FROM SALES.sys.tables

In the output, the SALES database now contains the “Product” table instead of the “Item” table. 

As you saw with SQLCMD, and SQL Server Management Studio, if the table that has to be rename contains a dot (.) sign, you will have to enclose the table name inside square brackets in your SQL script. 

Renaming Tables Using GUI in dBForge Studio

To rename tables via the GUI interface in dBForge studio, simply right-click the table that you want to rename. From the list of options, select Rename as shown in the screenshot below:

Enter the new name for your table. In the following screenshot, we rename the Item table to Product. Click the Enter key. 

Finally, click the “Yes” button from the following message box to rename your table.