sp_rename and the procedure definition
-
Maybe I'm missing something. To test I executed the first component to create the SP. I looked in SSMS and saw the incorrectly named SP. I then ran the sp_rename, refreshed SSMS and saw the new name. I then right clicked and did Script Stored Procedure as .. Create in new SQL window and the resulting script returned as
USE [master]
GO
/****** Object: StoredProcedure [dbo].[myexample] Script Date: 8/13/2014 10:11:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[myexample]
AS
select 'Procedure naming test for myexample' As Results
GO
Looks like it fixed it correctly.
There are no facts, only interpretations.
Friedrich Nietzsche -
TomThomson (8/13/2014)
"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.
The point on the second option is that the definition remains the same text as when the procedure was created with the improperly spelled name.
In this example, the proc was originally named "myxeample" and then renamed to "myexample". After renaming the procedure, the definition still shows the original text showing "Create Procedure myxeample" which would not match the name of the procedure.
When renaming the procedure, it is only changing the object name (sys.objects) and does not touch sys.sql_modules (or the deprecated sys.syscomments) where the definition is held.
Therefore, the second option is correct.
Here is a setup script
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'myexample2')
DROP PROCEDURE myexample2
GO
CREATE procedure myxeample
AS
select 'Procedure naming test for myexample' As Results
GO
select object_id,object_name(sm.object_id) AS ObjName,definition
From sys.sql_modules sm
where object_name(sm.object_id) = 'myxeample';
GO
SELECT so.object_id, so.name
, OBJECT_NAME(so.object_id) AS FuncObjName
, OBJECT_ID('myxeample') AS FuncObjID
FROM sys.objects so
WHERE so.name = 'myxeample';
GO
EXECUTE sp_rename 'myxeample','myexample2'
GO
select object_id,object_name(sm.object_id) AS ObjName,definition
From sys.sql_modules sm
where object_name(sm.object_id) = 'myexample2';
GO
SELECT so.object_id, so.name
, OBJECT_NAME(so.object_id) AS FuncObjName
, OBJECT_ID('myexample2') AS FuncObjID
FROM sys.objects so
WHERE so.name = 'myexample2';
And some results of those queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
drop and re-create the object with its new name, will change the object permission. it need very careful for production server.
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius -
SQLRNNR (8/13/2014)
The point on the second option is that the definition remains the same text as when the procedure was created with the improperly spelled name.I guess the problem is what you think the definition of the procedure is.
My idea of the definition is that it's the SQL that defines a stored proc; in the case of an SP that's been renamed, that isn't what's delivered by OBJECT_DEFINITION() and hence isn't what's in sys.sql_modules, because what's delivered by OBJECT_DEFINITION is the definition of a non-existent proc - a proc which I can not call, because there is no proc which has the name specified in that definition. In other words, OBJECT_DEFINITION() gets it wrong. Your assumption is that OBJECT_DEFINITION() gets it right, but that's not valid because if what it produces is right I could call the proc using the old name and couldn't call it using the new name, exactly the oppisite of what actually happens.
SSMS, when you ask it to script the SP, gves a script containing a valid definition with the correct (new, changed) name; and the SQL parser/compiler recognises that the new name is valid and the old one is not, links correctly when the new name is used and refuses to link when the old name is used. Clearly neither none of SSMS, the parser/compiler, or the execution system pays any attention to OBJECT_DEFINITION() or to sys.sql_modules.
Do you really think it's sensible to say that the definition is what the fundamentally important parts (parsing/compilation/execution) of SQL Server ignore and not what they actually accept?
Or, to put it more simply, after I've rename a stored procedure, I can call it using its new name because that's what SQL Server understands, and I can't call it using the old name because SQL Server doesn't understand that. So any sane definition of the SP will not say its name is the old name, it will say its name is the new name. The incorrect results of OBJECT_DEFINITION() make no difference, the new name is the only one that actually works. SInce the sys.sql_modules view uses OBJECT_DEFINITION() it delivers incorrect information. The QOTD answer is wrong because it's based (like your comment) on the invalid assumption that what's delivered by sys.sql_modules is correct.
Tom
-
i guess that was my point of the question of the day: the definition may not be what you think it should be.
the "correct" definition as Tom puts it, where it has the new valid name and the new valid procedure code, is not stored anywhere.
the old definition IS stored in SQL Server, however.
if you use the GUI, it really is grabbing the definition form sys.sql_modules and modifying it for you, to have the correct name...
you cannot confuse a GUI's SMO action and modification of the resulting script with what SQL is storing behind the scenes.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
TomThomson (8/13/2014)
SQLRNNR (8/13/2014)
The point on the second option is that the definition remains the same text as when the procedure was created with the improperly spelled name.I guess the problem is what you think the definition of the procedure is.
My idea of the definition is that it's the SQL that defines a stored proc; in the case of an SP that's been renamed, that isn't what's delivered by OBJECT_DEFINITION() and hence isn't what's in sys.sql_modules, because what's delivered by OBJECT_DEFINITION is the definition of a non-existent proc - a proc which I can not call, because there is no proc which has the name specified in that definition. In other words, OBJECT_DEFINITION() gets it wrong.
I had a nice long reply typed up and then SSC errored on post and I have to start afresh.
Anyway, yes both sys.sql_modules and object_definition are somewhat intertwined. They both call upon hidden objects in the database to pull the metadata.
Where I think the big difference is, is that SSMS will strip the Create Proc statement and then regenerate it as necessary when executing the stored procedure. The guts of the proc is mostly what matters when executing the procedure and technically should be the definition. But the metadata stores the create procedure along with the sql statements and calls it the definition.
This metadata is the same metadata accessed when generating a plan and maintaining execution statistics.
If I were to execute the following statement, I would be able to trap the sql associated to that stored procedure.
SELECT qs.query_plan_hash,t.text,t.objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
WHERE qs.query_plan_hash = 0xE701AFB2D865FA71
--where 0xE701AFB2D865FA71 was the hash for the proc we
--renamed.
Running that query, I can see the following results for the stored procedure that was renamed.
You will probably note that my object_id has changed. That is simply due to running the scenario a few times. I also decided to create a stored procedure with a bit more to it than a simple select text statement that does not generate an execution plan. When evaluating the query plan, you can see that it calls FNGETSQL. The statistics held for this procedure ties back to the metadata that hold the sql statement and is a different access path than the OBJECT_DEFINITION() method, which by itself does not generate an actual execution plan and the estimated plan only produces a plan of the following type.
StatementType="SELECT WITHOUT QUERY"
All of that said, if I alter the procedure to which we already changed the name, then it properly updates the definition in the metadata. No changes need to be made within the procedure body - just simply alter procedure...
Why can't sp_rename perform that same metadata update?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Lowell (8/13/2014)
i guess that was my point of the question of the day: the definition may not be what you think it should be.the "correct" definition as Tom puts it, where it has the new valid name and the new valid procedure code, is not stored anywhere.
the old definition IS stored in SQL Server, however.
if you use the GUI, it really is grabbing the definition form sys.sql_modules and modifying it for you, to have the correct name...
you cannot confuse a GUI's SMO action and modification of the resulting script with what SQL is storing behind the scenes.
Agreed. I had that in my original response that got wiped (see my immediately previous post) and couldn't recall the exact words. I hope the rewrite conveys the message as well as you put it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
SQLRNNR (8/13/2014)
Why can't sp_rename perform that same metadata update?Good question. That's something we definitely agree on. sp_rename should update all the metadata. If it did, OBJECT_DEFINITION would return the correct data and as a result of that sys.sql_modules would deliver the correct definition (your comment seem to suggest that you don't think sys.sql_modules doesn't use OBJECT_DEFINITION() - let me assure you that it does).
But: presumably the parser, the compiler, and the execution machine operate on some metadata somewhere, and that's the metadata that counts - something that is just ignored by the parse/compile/execute operation surely doesn't count. So the only useful definition is the definition that the parse/compile/execute operation uses? Even if it's constructed by changing the name in the definition to matche the name in sys.objects, rather than actually being stored anywhere (for Lowell: if you had said "reported definition" instead of just "definition" I would have had to agree with you; but I'm too pedantic to let unqualified "definition" pass).
edit: But at the end of the day, I guess there's an argument that says the definition is what the system view says the definition says it is, even if the system doesn't actually use that as the definition - the terminology is just a bit adrift of what pedantic types like me think it should be.
Tom
-
TomThomson (8/13/2014)
SQLRNNR (8/13/2014)
Why can't sp_rename perform that same metadata update?Good question. That's something we definitely agree on. sp_rename should update all the metadata. If it did, OBJECT_DEFINITION would return the correct data and as a result of that sys.sql_modules would deliver the correct definition (your comment seem to suggest that you don't think sys.sql_modules doesn't use OBJECT_DEFINITION() - let me assure you that it does).
Oh no. We agree that sys.sql_modules uses Object_Definition. That is plain to see in the execution plan. The query to find execution statistics relies on a different function - FNGETSQL. So the problem is deeper than just OBJECT_DEFINITION - it is at least a metadata issue or multiple functions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Welcome back tricky questions 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Koen Verbeeck (8/13/2014)
Great question, and a fine example of why sp_rename should be avoided.Yeah, that`s why I prefer to Drop & re-create the SP instead of using sp_rename.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
SQLRNNR (8/13/2014)Why can't sp_rename perform that same metadata update?
Presumably it's the standard Microsoft trick - there's a bug in the software, they can't be bothered to fix it, so they change the documentation to redefine it as intended behaviour.
-
Hany Helmy (8/14/2014)
Koen Verbeeck (8/13/2014)
Great question, and a fine example of why sp_rename should be avoided.Yeah, that`s why I prefer to Drop & re-create the SP instead of using sp_rename.
+1
Once we drop & re-create the procedure, we need to add/provide required rights once again.
Thanks
-
SQLRNNR (8/13/2014)
TomThomson (8/13/2014)
SQLRNNR (8/13/2014)
Why can't sp_rename perform that same metadata update?Good question. That's something we definitely agree on. sp_rename should update all the metadata. If it did, OBJECT_DEFINITION would return the correct data and as a result of that sys.sql_modules would deliver the correct definition (your comment seem to suggest that you don't think sys.sql_modules doesn't use OBJECT_DEFINITION() - let me assure you that it does).
Oh no. We agree that sys.sql_modules uses Object_Definition. That is plain to see in the execution plan. The query to find execution statistics relies on a different function - FNGETSQL. So the problem is deeper than just OBJECT_DEFINITION - it is at least a metadata issue or multiple functions.
Yes, it's a metadata issue; some metadata doesn't get updated by sp_rename. When you say "Oh no" do you mean you don't agree that sp_rename should update all the metadata, instead of leaving some of it not updated?
I suspect we do disagree on the importance of teh metadata that doesn't get updated; now that you tell me is screws up execution statistics too that might change, as I think execution stats sometimes do matter (they don't when one has no problems, of course, but ....).
Tom
-
Toreador (8/14/2014)
SQLRNNR (8/13/2014)Why can't sp_rename perform that same metadata update?
Presumably it's the standard Microsoft trick - there's a bug in the software, they can't be bothered to fix it, so they change the documentation to redefine it as intended behaviour.
I reckon that's a very perspicacious statement! (And Jason was very perspicacious to ask that question.)
Tom
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply