object_id mismatch and ssms
In sys.objects I found the object_id for the name 'adhoc_spDailyAccession_inf' a stored procedure.
When I look in sql_modules or syscomments using this object_id I see the code for a different stored procedure CREATE procedure [dbo].[rpt_spDailyAccession_inf]. Which is a little weird.
If I go into ssms and left click/modify the 'adhoc_spDailyAccession_inf' I get ALTER procedure [dbo].[adhoc_spDailyAccession_inf] Which is as it should be, so it is weirder.
The stored procdure rpt_spDailyAccession_inf can not be found in ssms.
When I do a search for '%adhoc_spDailyAccession%' in sql_modules I don't come up with anything.
This is in a sql 2008 db and the sql 2008 R2 db that it was restored to. Both dbs and serves have been cycled several times. dbcc checkdb comes up clean. I found this when writing code to do mass changes of the stored procedures. I tried to run the alter proc using the sql_modules definition column and got
Msg 208, Level 16, State 6, Procedure rpt_spDailyAccession_inf, Line 17 Invalid object name 'dbo.rpt_spDailyAccession_inf'
because there is no sys.object entry. This process worked for > 100 other stored procedures.
I'm ok with a glitch where the definition and object_id can get mismatched.
But where is sql hiding the stored procedure definition so that when I go through ssms I come up with the correct code? Any ideas? Do I have the tables I should be using wrong?
And no this is not a finger check or misread. I checked this about 20X and it is really impacting my positive outlook. Any help would be appreciated.
Best Answer
This is because the procedure has been renamed - check out my blog post about SQL modules.
From that:
definition
This is the actual text that represents the module at the time that it was created. Note that this may not be the same as the text needed to re-create an object, because name changes effected withsp_renameare not reflected in this definition. Client side code, whether in SSMS, our very own sql editor or any other editor is responsible for replacing the old object name with the new. Some (SSMS included) fail to re-create the object correctly under various circumstances (for example, if comments are placed between the schema and object names), so tread with care if using this definition to recreate objects. This column will show as NULL if the module was created usingWITH ENCRYPTIONkeyword.
Best Answer
The other place where you can look besides sql_modules or syscomments is the master.sys.syscomments view. Also, try to drop both procs and then re-create instead of altering.
Oleg
Thanks Matt your blog answered the missmatch question really well. It also answered the other part of the question where when you click modify in ssms the alter proc code is created using the name in the objects table. So, the stored procedure name you see when clicking the modify did not match the coded found in sql_modules. Thanks again.
