Renaming the Stored Procedures

• u2kota

  SSChasing Mays

  Points: 601

  More actions

  February 25, 2004 at 3:34 am

  #107667

  Dear All,

  In my project all the stored procedures are named with a prefix "sp_". Many articles in the past suggested that using the prefix "sp_" to the local stored procs will have an impact on performance. Therefore, I am thinking of renaming my storedprocs. 

  My query is.....Is there an easy way to modify the stored proc names and also what sort of improvement in performance I get by doing so?

  Cheers..

  PK

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 25, 2004 at 3:50 am

  #495498

  As for improvements in performance this might be interesting

  http://www.sqlservercentral.com/columnists/jtravis/sp_performance.asp

  http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

  As for renaming:

  Take a look at BOL for 'renaming stored procedures'. Seems to be more work than looping through the database object and doing a sp_rename. In the explanation of sp_rename one can read

  Renaming a stored procedure, view or trigger will not change the name of the corresponding object name in the syscomments table. This may result in problems generating a script for the object as the old name will be inserted from the syscomments table into the CREATE statement. For best results, do not rename these object types. Instead, drop and re-create the object by its new name.

  I'm sure one else has done this before and will jump right in.

  

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• u2kota

  SSChasing Mays

  Points: 601

  More actions

  February 25, 2004 at 5:48 am

  #495522

  Hi Frank,

  Thanks very much for the information. Though it is a tedious task I have to do it at some point.

  Cheers....

  Phani

• ButchH

  Ten Centuries

  Points: 1073

  More actions

  February 29, 2004 at 11:38 am

  #496369

  There's a nifty feature in SQL Server Enterprise Manager where you can right-click on a database object, All Tasks > Generate SQL Script.  Click on the Show All button, then click the All Stored Procedures check-box.  Make sure you look at the Formatting Options Tab where you can select the DROP procedures for one run then on the next run you can select CREATE procedures for the next run.  On the Options Tab, select ONE FILE so everything is in one place.

  Once you have the files, make a copy of the NEW CREATE procedures file (just so you can fall back and regroup if necessary) then edit one of the files using REPLACE and change 'sp_' with your new prefix.  We use 'usp_' for UserStoreProcedure.

  Once you have edited the file, open Query Analyzer and load the edited file through File Open and load the edited file.  Run the code and you should have all your new procedures.

  If everything is working well, then load the DROP file into Query Analyzer and run that code to drop the OLD procedures.

  Hope this helps.

  Butch

  
  Butch