| Author |
Topic |
|
kneel
Starting Member
36 Posts |
 Posted - 2007-11-05 : 01:34:18
|
| Hello All,Does anyone know how to rename SP using Transact SQL ?Thanks in advance--kneel |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-05 : 01:48:37
|
| 1 sp_helptext 'yourSP'2 copy it in QA and change the procedure name and run 3 Drop procedure yourSPMadhivananFailing to plan is Planning to fail |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-05 : 04:53:03
|
| EXEC sp_rename 'OldSprocName', 'NewSprocname'will probably do it, won't it?And I expect there is a RENAME option in Enterprise Manager / SSMS isn't there?Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-05 : 05:05:04
|
quote:
Originally posted by Kristen
EXEC sp_rename 'OldSprocName', 'NewSprocname'will probably do it, won't it?And I expect there is a RENAME option in Enterprise Manager / SSMS isn't there?Kristen
Yes it willMadhivananFailing to plan is Planning to fail |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-11-05 : 06:05:54
|
| Careful about using sp_rename. It will change the name of the sp but not update the script in syscomments (unless the behaviour has changed which I doubt). Means if you script the SP it will have the create statement for the wrong SP.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-05 : 06:29:23
|
| "if you script the SP it will have the create statement for the wrong SP"Now that sounds like a useful obfuscation device! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-05 : 07:05:35
|
quote:
Originally posted by nr
Careful about using sp_rename. It will change the name of the sp but not update the script in syscomments (unless the behaviour has changed which I doubt). Means if you script the SP it will have the create statement for the wrong SP.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Does it mean that my suggestion is more safer?  MadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-05 : 07:37:28
|
| Yup, definitely. Although getting the SProc back out with sp_HelpText has the problem that overly-long lines will be wrapped incorrectly. Better to store the source code in a Version Control system of course ...Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-05 : 07:53:45
|
quote:
Originally posted by Kristen
Yup, definitely. Although getting the SProc back out with sp_HelpText has the problem that overly-long lines will be wrapped incorrectly. Better to store the source code in a Version Control system of course ...Kristen
Not, if you set Text mode MadhivananFailing to plan is Planning to fail |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-05 : 08:37:58
|
quote:
Originally posted by nr
Careful about using sp_rename. It will change the name of the sp but not update the script in syscomments (unless the behaviour has changed which I doubt). Means if you script the SP it will have the create statement for the wrong SP.
IIRC this was fixed in in 2005 as long as you use SMO to generate the script. SSMS uses SMO so scripting from SSMS after a sp_rename *should* be ok. best to verify first though! using sp_helptext or syscomments directly will still give you the wrong (old) name of course.
elsasoft.org |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-05 : 15:30:22
|
| I don't know much but I usually make a copy of the old one and create a new one with the new name and delete the old one. I don't know if is the most efficient way but works. |
|
|
|
|
Rename SP