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

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,

Be courteous. Drive responsibly.