A growing number of businesses are discovering the importance of big data. Thirty-two percent of businesses have a formal data strategy and this number is rising year after year.
Unfortunately, they often have to deal with a variety of challenges when they manage their data. One of the biggest issues is with managing the tables in their SQL servers.
Renaming Tables is Important for SQL Server Management
Renaming a table in a database is one of the most common tasks a DBA will carry out. There are a lot of issues that you have to face when trying to manage an SQL database. One of them is knowing how to backup your data.
However, renaming tables is arguably even more important, since it is something that you will have to do regularly. 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 data tables in SQL Server. Some of the ways involve text queries while the other ways allow you to rename data tables in SQL Server via GUI.
In this article, you will see five main ways to rename tables in SQL Server to better manage your data:
- Rename a table with SQLCMD Utility
- Rename a table with SQL Server Management Studio Query Window
- Rename a table with SQL Server Management Studio GUI
- Rename a table with SQL Query Window in dbForge Studio for SQL Server
- Rename a Tables with 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. You can download the SQLCMD utility from the following link:
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
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 through 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 the methods in the following sections:
Renaming Table Using SQL Query Window
To rename a table via 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 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, click “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 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 successful renaming of 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 the 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 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 th 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 “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 SQL manager, 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.
Knowing How to Rename Tables is Essential as a Data-Driven Business
There are many powerful big data tools that companies can use to improve productivity and efficiency. However, big data capabilities are not going to offer many benefits if you don’t know how to manage your SQL databases properly.
In this article we’ve looked at five different ways that you can rename a table in SQL Server using SQLCMD, SSMS, and dbForge Studio for SQL. You should follow these guidelines if you want to run a successful data-driven business that manages its data properly.