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:
I have given a name to my pain...MCM SQL Server, MVP
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events