Renaming the Stored Procedures

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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