sp_rename and the procedure definition

Question

sp_rename and the procedure definition

Viewing 2 posts - 31 through 31 (of 31 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

TomThomson (8/14/2014)
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?

Nope I mean that we are in agreement that there is a problem with sp_rename and that it should be updating the metadata.

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 ....).

I think the important takeaway is that the metadata stores the "create procedure" and any notes that might precede that statement as part of the stored procedure definition. But the reality is that that statement doesn't affect execution, stats, or the actual sql statements (or the definition in your terms).

In the end, the "create procedure", though stored as part of the definition, does not affect the recreation of the stored procedure, the scripting of the object in SSMS, nor the execution or statistics of said execution. But it did make for a bit of a fun exercise.:w00t:

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

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 2

SQLRNNR - Thursday, August 14, 2014 7:42 PM
TomThomson (8/14/2014)
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?
Nope I mean that we are in agreement that there is a problem with sp_rename and that it should be updating the metadata.
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 ....).
I think the important takeaway is that the metadata stores the "create procedure" and any notes that might precede that statement as part of the stored procedure definition. But the reality is that that statement doesn't affect execution, stats, or the actual sql statements (or the definition in your terms).In the end, the "create procedure", though stored as part of the definition, does not affect the recreation of the stored procedure, the scripting of the object in SSMS, nor the execution or statistics of said execution. But it did make for a bit of a fun exercise. w00t

Just to fill in some gaps here:

Should sp_rename be updating the full, text definition? I would seem to us that it should, but there might be a valid reason for not doing so. It would be nice if there was some explanation for this behavior in the documentation.
To be clear, what is stored in the "definition" is the entire query batch that created the module (stored procedure, function, trigger, or view). This includes any comments after the module, which is possible if the module is defined in a BEGIN ... END block (i.e. scalar UDFs and multi-statement TVFs)
The out-of-sync definition does not appear to break anything, just as long as you don't rely on this definition when scripting out objects 😉 However, it does show up when using the sys.dm_exec_sql_text DMF. And perhaps this is part of why the definition doesn't get updated: the definition is used during execution (at least to display the definition), and altering it to a longer or shorter name would invalidate the statement_start_offset and statement_end_offset values in sys.dm_exec_requests because the rename operation happens immediately and does not wait for all executions of the module to complete. Yes, I have tested this.
On a related note (and why I happened to come across this thread), sp_rename isn't the only way to get out-of-sync module definitions:
Stored Procedure / Function / View / Trigger Definitions Can Be Wrong, Even If sp_rename Was Never Used

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR