Renaming a SQL Server object is a fairly common operation when working with a product. Overtime, objects need to be enhanced as and when new features are introduced. One of the most frequently used mechanisms to rename objects is to use the system stored procedure: sp_rename.
However, this system stored procedure comes with a word of caution – it is not applicable to all SQL Server object types. Here’s an example:
Renaming a Column
To demonstrate the renaming of a column using sp_rename, let us first create a simple table with 2 columns:
USE tempdb;
GO–Safety Check
IF OBJECT_ID(‘dbo.Student’,’U’) IS NOT NULL
DROP TABLE dbo.Student;
GO–Create the test Table
CREATE TABLE dbo.Student (StudentId INT IDENTITY(1,1),
SchoolId INT,
CONSTRAINT pk_StudentId PRIMARY KEY CLUSTERED (StudentId)
);
GO
Let us now check the table properties:
USE tempdb;
GO–Check the table values
SELECT sc.name,
sc.object_id,
sc.column_id
FROM sys.columns AS sc
WHERE sc.object_id = OBJECT_ID(‘dbo.Student’,’U’);
GO
Now, let us rename the column – SchoolId to EstablishmentId and check the table properties again.
USE tempdb;
GO–Rename a column: SchoolId to EstablishmentId
sp_rename ‘dbo.Student.SchoolId’,’EstablishmentId’,’COLUMN’;
GO–Check if the column has been renamed
SELECT sc.name,
sc.object_id,
sc.column_id
FROM sys.columns AS sc
WHERE sc.object_id = OBJECT_ID(‘dbo.Student’,’U’);
GO
As can be seen from the screenshot above, the column renaming was successful. Now, let us extend this example to rename a stored procedure.
Renaming a Stored Procedure
Using sp_rename
Let us first create a new stored procedure using the table created above.
USE tempdb;
GO–Creat the test procedure
CREATE PROCEDURE dbo.proc_GetStudents
AS
BEGIN
SELECT s.StudentId,
s.EstablishmentId
FROM dbo.Student AS s;
END;
GO
Let us take a look at the object definition in the sql_modules:
USE tempdb;
GO–Check the procedure properties
SELECT sm.object_id AS ObjectId,
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ObjectDefinition
FROM sys.sql_modules AS sm;
GO
Now, let us rename the stored procedure using sp_rename and check the stored procedure properties.
USE tempdb;
GO–Rename the procedure from proc_GetStudents to proc_GetAllStudents
sp_rename ‘proc_GetStudents’,’proc_GetAllStudents’,’OBJECT’;
GO–Check the procedure properties
SELECT sm.object_id AS ObjectId,
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ObjectDefinition
FROM sys.sql_modules AS sm;
GO
As can be seen from the screenshot above, SQL Server is left in a state of partial meta-data update. This is therefore, not the correct way to rename a stored procedure.
The Correct Way to Rename a Stored Procedure
The correct way to rename a stored procedure is to drop and recreate the object. To demonstrate this, let us re-create the stored procedure first.
USE tempdb;
GOIF OBJECT_ID(‘dbo.proc_GetStudents’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_GetStudents;
GOIF OBJECT_ID(‘dbo.proc_GetAllStudents’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_GetAllStudents;
GO–Creat the test procedure
CREATE PROCEDURE dbo.proc_GetStudents
AS
BEGIN
SELECT s.StudentId,
s.EstablishmentId
FROM dbo.Student AS s;
END;
GO–Check the procedure properties
SELECT sm.object_id AS ObjectId,
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ObjectDefinition
FROM sys.sql_modules AS sm;
GO
Let us now drop and recreate the procedure:
USE tempdb;
GO–Rename the procedure from proc_GetStudents to proc_GetAllStudents
IF OBJECT_ID(‘dbo.proc_GetStudents’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_GetStudents;
GO
–Creat the test procedure
CREATE PROCEDURE dbo.proc_GetAllStudents
AS
BEGIN
SELECT s.StudentId,
s.EstablishmentId
FROM dbo.Student AS s;
END;
GO–Check the procedure properties
SELECT sm.object_id AS ObjectId,
OBJECT_NAME(sm.object_id) AS ProcedureName,
sm.definition AS ObjectDefinition
FROM sys.sql_modules AS sm;
GO
As can be seen from the screenshot above, the stored procedure rename has now succeeded.
Conclusion
In conclusion, the following thumb rules can be established:
- The sp_rename can be used to rename a column, database, index, statistics and/or user defined data types
- The sp_rename can also be used to rename constraints (check, primary-key, foreign-key and unique-key), user tables and rules
- The sp_rename should NOT be used for renaming stored procedures, functions, views and triggers. They MUST be dropped and recreated
Further Reading
- sp_rename [Link]
Until we meet next time,
SQLTwins by Nakul Vachhrajani 