How to Rename a Table Name in SQL Server

One of the most critical database administrator’s duties is managing database objects. For instance, the application logic changes, and DBAs may need to change the database object names. This task is crucial because we must ensure that renaming won’t break the application.

I have recently been assigned a project where I had to rename a couple of tables. Here, I am going to explain how to rename an SQL Server table in several ways.

We can rename a table using:

1. SQL Server management studio.
2. The sp_rename stored procedure.

Also, I am going to cover potential errors that can occur after renaming tables and the necessary precautions. This article is demo-oriented, so I have created the following objects on the SQL Server instance:

1. The database named StudentDB.
2. Two tables named tblSchool and tblStudent.
3. A foreign key between tblSchool and tblStudent. The tblSchool is a parent table, and tblStudent is a child table.
4. A stored procedure named sp_getStudents.
5. A view named vwStudents.

The T-SQL codes applied to create the above database objects are as follows:

Create database:

Create database [StudentDB]
Go

Create a table and foreign keys:

Use [StudentDB]
Go
CREATE TABLE [dbo].[tblSchool](
	[School_ID] [int] IDENTITY(1,1) NOT NULL,
	[School_Name] [varchar](500) NULL,
	[City] [varchar](50) NULL,
 CONSTRAINT [PK_tblSchool] PRIMARY KEY CLUSTERED 
(
	[School_ID] ASC
)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblStudent](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[student_name] [varchar](250) NOT NULL,
	[student_code] [varchar](5) NOT NULL,
	[student_grade] [char](2) NOT NULL,
	[SchoolID] [int] NOT NULL,
 CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
))
GO

ALTER TABLE [dbo].[tblStudent]  WITH CHECK ADD  CONSTRAINT [FK_tblStudent_tblSchool] FOREIGN KEY([SchoolID])
REFERENCES [dbo].[tblSchool] ([School_ID])
GO
ALTER TABLE [dbo].[tblStudent] CHECK CONSTRAINT [FK_tblStudent_tblSchool]
GO

E-R diagram of tblSchool and tblStudent:

Insert dummy data into the table:

INSERT INTO tblschool
            (school_name,
             city)
VALUES     ('Nalanda School',
            'Mehsana'),
            ('Sarvajanik School',
             'Mehsana')
go
INSERT INTO tblstudent
            (student_name,
             student_code,
             student_grade,
             schoolid)
VALUES      ('Nisarg Upadhyay',
             'ST001',
             'A ',
             1),
            ('Dixit Upadhyay',
             'ST002',
             'A ',
             1),
            ('Bharti Upadhyay',
             'ST003',
             'C',
             2),
            ('Nimesh Patel',
             'ST004',
             'C',
             2),
            ('Raghav Dave',
             'ST005',
             'A',
             1)
go  

Create a stored procedure:

USE studentdb
go
CREATE PROCEDURE Sp_getstudent
AS
  BEGIN
      SELECT id,
             student_code,
             student_name,
             student_grade,
             school_name,
             city
      FROM   tblstudent a
             INNER JOIN tblschool b
                     ON a.schoolid = b.school_id
  END

Create a view:

use StudentDB
go
create view vwStudents
as
select student_code [Student Code],
student_name [Student Name],
student_grade [Student Grade],
School_Name [School Name],
City [City]
from tblStudent a inner join tblSchool b on  a.SchoolID=b.School_ID

Rename a Table Name Using sp_rename Command

We can use the sp_rename stored procedure to rename any object of the SQL Server database (do not to confuse with alter table sql server operator which changes data in tables). The syntax is as follows:

Exec sp_rename ‘schema_name.old_name’,’schema_name.new_name’

1. Schema_name.old_name is the name of the table that you want to change.
2. Schema_name.new_name is the new name.

Now, let us rename the tblStudent table to tbl_Students.

Use StudentDB
go
Exec sp_rename 'tblStudent','tbl_Students'
Go

The output is:

Caution: Changing any part of an object name could break scripts and stored procedures.

The message indicates that the name of the table has been changed successfully.

Now let us see how we can change the table name using SQL Server Management studio.

How to Change Table Name in SQL Server Management Studio (SSMS)

To rename a table, open SQL Server Management Studio > Connect to SQL Server instance > Expand database > Expand tables > Right-click on tblSchool > Rename. Or, just click on tblSchool.

Specify the new name and hit Enter.

Once the name is changed, you can run the following query to verify:

As you can see, the name of the table has been changed. Notice also that the table’s modified date has been updated, and the new date is the timestamp when the table is modified.

Things to Care of Before Changing the Table Name in MSSQL

After changing the table names of tblStudent and tblSchool, let us run the stored procedure named sp_getstudent:

Use StudentDB
Go
Exec sp_getstudent
Go

Output

Msg 208, Level 16, State 1, Procedure sp_getstudent, Line 4 [Batch Start Line 15]
Invalid object name 'tblStudent'.

The error indicates that the object used in the view is missing.

Now, let us run the following query to view the data from the vwStudents:

Use StudentDB
Go
Select * from vwStudents
Go

Output

Msg 208, Level 16, State 1, Procedure vwStudents, Line 8 [Batch Start Line 16]
Invalid object name 'tblStudent'.
Msg 4413, Level 16, State 1, Line 17
Could not use view or function 'vwStudents' because of binding errors.

The error indicates that the query used to create a view cannot find the tables named tblStudent.

To avoid such errors after changing the name of the table, we must check the list of the database objects that are dependent on the tables.

The table renaming process table should be as follows:

1. Change the name of the table.
2. Change the name of the table in the Stored procedure, views, Ad-Hoc queries, and other objects.

To find the list of the dependent objects on the specific table, you can run the sp_depends stored procedure. The syntax is:

exec sp_depends [obj_name]

To find the list of the objects dependent on the tblStudent table, run the following query:

use studentDB
go
exec sp_depends [vwStudents]

Ouput:

Note: Make sure that you run this procedure before you rename the table. Otherwise, the query will return NULL as the output.

Summary

This article explains how we can use the sp_rename stored procedure and SQL Server Management Studio to change the table’s name. Moreover, we have defined potential errors that can occur after renaming the tables and the precautions that must take after changing the table names. To better cope with tables in the SQL server, read also how to use drop table statement in sql server.

Tags: sql rename, sql server, t-sql Last modified: September 17, 2021