When the table name is no longer relevant or no longer serves the purpose of creation, we can change it. SQL Server does not provide any command to change the table name directly. However, it has a stored procedure named sp_rename that enables us to rename a table. It must ensure that we have ALTER permission on the object before changing the table’s name.
Limitations and Restrictions
SQL Server has some restrictions while renaming the table because when we rename a table, it does not imply that all references to that table will be renamed as well. We must manually modify all objects that belong to the renamed table.
For example, if we want to change the table name that is also referenced in a trigger, it is required to modify the trigger to reflect the new table name. The sys.sql_expression_dependencies can be used for listing all dependencies on the object before changing the name.
We can change the table name in SQL Server in two ways:
- SQL Query
- SQL Server Management Studio (SSMS)
We must have very careful before changing the table name. Existing queries, views, user-defined functions, stored procedures, or programs that refer to the table will become invalid due to the name change.
SQL Query
SQL Server provides a standard stored procedure called SP_RENAME for renaming the user-created object in the current database. The user-created object can be a table or column.
The following syntax is used to rename the table:
EXEC sp_rename 'old_table_name', 'new_table_name' Example
We can demonstrate the renaming of a table using the below employee table:
Now, we will use the sp_rename stored procedure syntax to change the name of an employee table to employee_info. Here is the query:
EXEC sp_rename 'employee', 'employee_info';
Executing this statement, SQL Server shows the following message:
We will get this message because while renaming a table, SQL Server does not automatically change the name of any references associated with that table. However, the table is renamed successfully. We can verify whether the table is renamed or not by executing the SELECT statement:
SQL Server Management Studio (SSMS)
SSMS is a GUI tool that allows us to change the table name in the following ways:
- Rename a table using Object Explorer
- Double click on the table name
Let us discuss each of them in detail.
Rename a table using Object Explorer
The following steps are used to rename a table using Object Explorer:
Step 1: Go to the Databases -> Tables menu and expand it.
Step 2: Select the desired table, right-click on it and choose the Rename option.
The below image explains all the above steps where we have renamed the ‘cust_order‘ table to ‘customer_order’:
Step 3: When we click on the rename option, the table name will be changed as editable. Type a new name for your selected table and press Enter. If you get any prompt message, click on the Yes button.
Step 4: Refresh the database or table to finish the renaming steps.
Step 5: Execute the SELECT statement to verify the changed table name.
Double click on the table name
We can use the following steps to rename the table using a double click:
Step 1: Go to the Databases -> Tables.
Step 2: Select the table you want to change and double-click on it. We can the table name is changed as an editable form. See the below image:
Step 3: Type a new name for your chosen table, press Enter, and refresh the database or table to finish the renaming steps.
Conclusion
This article gives the complete overview of changing the table name in the database with the help of stored procedure ‘sp_rename’ and SQL Server Management Studio. It is always suggested that use T-SQL queries to make any changes in the database or table. Renaming the table using SSMS is not a good idea because it can make DBA’s work more difficult to understand.
Leave a Reply