How to rename table in SQL Server
In this tutorial, we will learn how to rename existing table in SQL Server database. Mainly, there are two ways to rename existing table as following.
- SQL Server Management Studio
- Built in store procedure “sp_rename”
While working with database, we might be needed to rename the existing tables in the database. For example, the tables that are created in the SQL Server database might be needed to be renamed as per required due to the version updated of software or to fix bugs in the system.
Let's understand how to rename table name using Transact SQL and SQL Server Management Studio in details with syntax and examples.
1. Rename table using SQL Server Management Studio (SSMS)
One easiest way to rename a table name in SQL Server database is using Management Studio software which is developed by Microsoft. We have to follow a few steps to rename table.
Let’s say we have a table called “Customers” in database and we will rename that table with new name called “CustomerDetails”.
Expand your tables under database, choose table which you want to rename and right click on that table, and choose “Rename” option from list of options given in panel as per following screen.
Once “Rename” option chosen, system will allow you to write a new name for that table as appeared in the following screen. Once you write new name “CustomerDetails”, press enter and table name will be changed with new name.
2. Rename table using Transact SQL (using built in store procedure “sp_rename”)
You cannot change your table name by using any SQL statement directly in SQL Server. SQL Server provides inbuilt stored procedure to rename your table. The “sp_rename” stored procedure which allows you to rename your table in SQL Server.
sp_rename 'OLD_TABLE_NAME', 'NEW_TABLE_NAME'
As name implies, first parameter is old table name in single quite which you want to rename and second parameter is a destination which name you want have a new name for table in your database.
Note: Both the table names must be enclosed with single quote as per syntax.
Let’s create a new table in SQL server called “Customer” and we will rename that with “CustomerDetails” using inbuilt stored procedure “sp_rename”.
CREATE TABLE dbo.Customers (
[Id] [bigint] IDENTITY(1,1) PRIMARY KEY,
[FirstName] [nvarchar](30) NULL,
[LastName] [nvarchar](30) NULL,
[DOB] [date] NULL,
[Active] [bit] NULL);
Now, we have a table called “Customers” and we will rename that table with new name called “CustomerDetails” inbuilt stored procedure “sp_rename”.
sp_rename 'Customers', 'CustomersDetails'
It will rename the table successfully and display the following message.
Caution: Changing any part of an object name could break scripts and stored procedures.
Now, you should be familiar about how to rename table in SQL Server database using Transact SQL and SQL Server Management Studio software.