Changing the name of a stored procedure in SQL Server 2008

Question

I have a stored procedure that I am editing through Visual Studio 2008. Is there an easy way to change the name of the stored procedure? Right now, if I look at the sproc properties, the name sproc will be grayed out.

Answer 1

If you have SSMS, you can right-click on the name of the procedure and select rename.

Another option is to abandon the procedure and recreate it with a new name.

Answer 2

EXEC sp_rename OLDNAME, NEWNAME

It is assumed that you can execute SQL statements through VS2008.

Answer 3

This question is very old and already considered an answer, but just to know that Renaming SP is not a good idea , because it does not update sys.procedures.

For example, create SP "sp_abc",

 CREATE PROCEDURE [dbo].[sp_abc] AS BEGIN SET NOCOUNT ON; SELECT ID, Name FROM tbl_Student WHERE IsDeleted = 0 END 

Now rename it

 sp_rename 'sp_abc', 'sp_Newabc' 

The following warning is displayed.

 Caution: Changing any part of an object name could break scripts and stored procedures. 

Now see sp_Newabc

 sp_helptext sp_Newabc 

you can see this result.

 CREATE PROCEDURE [dbo].[sp_abc] AS BEGIN SET NOCOUNT ON; SELECT ID, Name FROM tbl_Student WHERE IsDeleted = 0 END 

It still contains the old sp_abc procedure name. Since when renaming SP it does not update sys.procedure.

 Better solution is to drop the stored procedure and re-create it with the new name. 

Answer 4

 sp_rename <oldname> <newname> 

In SQL Server 2008 R2, sys.procedures seems to be updating. (in test environment)

Answer 5

Rename the SQL server stored procedure:

For the correct answer: View this article

Usage: sp_rename'[old_object_name]','[new_object_name]','[object_type]'

Answer 6

like kkk mentioned in his answer

Better solution corresponds to drop stored procedure and re-create it with a new name.

to do this

 DROP PROCEDURE [dbo].[Procedure_Name] 

then

 Create Procedure [dbo].[GetEmployees] as .... 

Answer 7

Rename a stored procedure in SQL Server

 EXEC sp_rename 'Proc_OldSpName', 'Proc_NewSpName';