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:
- SQL Server management studio.
- 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:
- The database named StudentDB.
- Two tables named tblSchool and tblStudent.
- A foreign key between tblSchool and tblStudent. The tblSchool is a parent table, and tblStudent is a child table.
- A stored procedure named sp_getStudents.
- A view named vwStudents.
The T-SQL codes applied to create the above database objects are as follows:
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 using sp_rename
We can use the sp_rename stored procedure to rename any object of the SQL Server database. The syntax is as follows:
Exec sp_rename ‘schema_name.old_name’,’schema_name.new_name’
- Schema_name.old_name is the name of the table that you want to change.
- 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.
Rename tables using SQL Server Management Studio
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
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
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
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:
- Change the name of the table.
- 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]
Note: Make sure that you run this procedure before you rename the table. Otherwise, the query will return NULL as the output.
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.
- Different Methods to Rebuild All Indexes for All Tables - March 26, 2021
- Renaming Indexes with sp_rename Procedure - March 24, 2021
- Create and Configure Oracle Linked Server in SQL Server - March 24, 2021