August 12, 2014 at 9:28 pm
Comments posted to this topic are about the item sp_rename and the procedure definition
Lowell
August 13, 2014 at 12:15 am
Good question, didn`t touch sp_rename since ages 🙂
Thx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
August 13, 2014 at 12:38 am
Great question, and a fine example of why sp_rename should be avoided.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2014 at 1:47 am
sp_rename leave unaltered the definition, (see sys.syscomments)
create procedure My_proc
as
select 1 as a
go
SELECT * FROM sys.syscomments
WHERE object_name(id) = 'My_proc'
go
exec sp_rename My_proc,Myproc
SELECT * FROM sys.syscomments
WHERE object_name(id) = 'Myproc'
After the rename the definition in the columnn "text" is unaltered.
The only advantage of sp_rename instead of "drop and re-create" is that you shouldn't reassign permissions on procedure.
August 13, 2014 at 3:30 am
This was removed by the editor as SPAM
August 13, 2014 at 4:52 am
It says it very clearly here: http://msdn.microsoft.com/en-gb/library/ms188351.aspx
"Changes the name of a user-created object in the current database. This object can be a table, index, column, alias data type, or Microsoft .NET Framework common language runtime (CLR) user-defined type."
"Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object name in the definition column of the sys.sql_modules catalog view. Therefore, we recommend that sp_rename not be used to rename these object types. Instead, drop and re-create the object with its new name.
Renaming an object such as a table or column will not automatically rename references to that object. You must modify any objects that reference the renamed object manually. For example, if you rename a table column and that column is referenced in a trigger, you must modify the trigger to reflect the new column name. Use sys.sql_expression_dependencies to list dependencies on the object before renaming it."
August 13, 2014 at 5:13 am
August 13, 2014 at 6:01 am
It's a good question. Learned something today.
Actually, if you run the script as is it will not even rename the object.
Since it is lacking a GO statement between the SELECT and the EXECUTE, the last statement just becomes part of the procedure.
At first, I wondered if that intentionally part of the actual question 🙂
---------------
Mel. 😎
August 13, 2014 at 6:03 am
SqlMel (8/13/2014)
It's a good question. Learned something today.Actually, if you run the script as is it will not even rename the object.
Since it is lacking a GO statement between the SELECT and the EXECUTE, the last statement just becomes part of the procedure.
At first, I wondered if that intentionally part of the actual question 🙂
Yeah, me too. But then I re-read the question and it was pretty clear.
August 13, 2014 at 6:04 am
Yep, good question, learned something today and I answered the question AFTER getting my coffee.
August 13, 2014 at 7:32 am
Nice question. Thanks for sharing
August 13, 2014 at 7:42 am
Nice question. I've never used sp_rename, and now I know that I shouldn't use it. 🙂
I also noticed the missing go statement, but when the available options didn't mention it, I figured it was unintentional.
August 13, 2014 at 7:48 am
Interesting question. It's a pity that the first answer option is ambiguous, with one reading making it correct and the other incorrect, while the second option, which is claimed to be the correct answer, is just plain incorrect.
The definition is changed to use the new name in the text defining the proc's name; that's one interpretation of the first option, and is the only correct answer.
The definition is changed to use the new name everywhere it had the old name; that's another interpretation of the first option, and is an incorrect answer.
"The definition is untouched" - anything that says that is wrong, because the definition of a stored procedure includes its name, which is changed. It's in the first bit of the text returned by SELECT object_definition(Object_id(<name of sp>)) when that returns any text, rather than NULL. The explanation mentions that stored procedure, so it's a bit surprising that the question's author apparently didn't notice it. So the second option is incorrect. Of course the second option also states that the procedure now contains the new name, which is true because the body contains that name, but since the second option also makes the incorrect claim that the definition is untouched the second option is not a correct answer.
Some of the earlier comments suggest that renaming SPs (and views and so on) is more of a problem that renaming other things like tables. I don't agree. Renaming most things is fraught: that's because everything that uses the name has to be altered. Tables are no easier than stored procedures: every script or stored procedure that uses the table has to be changed to use the new name. So does every view and every function that uses the table, and since views and functions can't call stored procedures that makes a table more awkward to rename than a stored procedure. This always makes me think that people who claim rename is less of a problem with tables than with SPs are not actually correct. I guess it depends on the extent to which sys.object_definition() and the sys.sql_modules view are important; to me they don't seem to be very important at all - SSMS gets the SP definitions correct when asked to script them and the sql system invokes the correct SP when asked to despite object_definition(), and hence sys.sql_modules, delivering NULL for the definitions of renamed SPs (and SSMS gets it right views and so on, too). When I want an SP or View definition, I ask SSMS for a script. When I want to call an SP or use a view in a DML statement object_definition() isn't involved. So why should I care that object_definition() can't provide the definition of a renamed view or trigger or SP or whatever? Oddly enough I do care, because maybe object_definition() will in future be needed for something I want to handle; so I shouldn't use sp_rename where it breaks object_definition(); but equally I can see that because object_definition doesn't work for some renamed things, in the future maybe it won't work for others - so I shouldn't use sp_rename for tables either. In practice the only things I have ever used sp_rename for in production code are databases (but I use it for prettty well anything when not in production).
Tom
August 13, 2014 at 7:50 am
stephen.long.1 (8/13/2014)
Nice question. I've never used sp_rename, and now I know that I shouldn't use it. 🙂
+1 Thanks so much for the question. I learned something new today.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply