#0291 – SQL Server – sp_rename – Renaming SQL Server objects – Do’s and Dont’s


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


image


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


image


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


image


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


image


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;
GO

IF OBJECT_ID(‘dbo.proc_GetStudents’,’P’) IS NOT NULL
DROP PROCEDURE dbo.proc_GetStudents;
GO

IF 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


image


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


image


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



Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s