CODEWITHSUNDEEP
sqlvisual-studio-2008sql-server-2008
Kimball Robinson
Kimball Robinson

Reputation: 3387

Change name of stored procedure in SQL Server 2008

I have a stored procedure which I edit through Visual Studio 2008. Is there a simple way to change the name of the stored procedure? Right now if I look at the sproc's properties, the name of the sproc is grayed out.

Upvotes: 8

Views: 34935

Answers (7)

Manjunath Bilwar
Manjunath Bilwar

Reputation: 2353

Rename the stored procedure in SQL Server 

EXEC sp_rename 'Proc_OldSpName', 'Proc_NewSpName';

Upvotes: 1

Basheer AL-MOMANI
Basheer AL-MOMANI

Reputation: 15327

as kkk mentioned in his answer

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

to do this 

DROP PROCEDURE [dbo].[Procedure_Name]

then

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

Upvotes: 0

Mahesh
Mahesh

Reputation: 11

Rename stored procedure sql server:

For the correct answer: View this article.

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

Upvotes: 1

Greg
Greg

Reputation: 11

sp_rename <oldname> <newname>

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

Upvotes: 1

kkk
kkk

Reputation: 273

This question is very old and already seen as answered but just to know that Renaming SP is not a good idea because it does not update sys.procedures.

For example, create a 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'

it shows following warning.

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 old Procedure name sp_abc. Because when you rename SP it does not update sys.procedure.

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

Upvotes: 10

josephj1989
josephj1989

Reputation: 9709

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

The other option is to drop the procedure and recreate it with the new name.

Upvotes: 12

Derek Flenniken
Derek Flenniken

Reputation: 487

EXEC sp_rename OLDNAME, NEWNAME

This is assuming you can execute SQL statements via VS2008.

Upvotes: 20

Related Questions