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.