Search code examples
sql-serverstored-proceduresmodulesyssysobjects

SQL Server stored prcedure definition is different with sys_modules.definition

I have 10 stored procedures they are different with their definition in sys_modules. I find them with this query:

select 
    b.definition, a.name 
from 
    sysObjects a 
left outer join 
    sys.sys_modules b on b.id = a.object_id 
where 
    b.definition not like '%' + b.name + '%'

Could someone tell me why this happens?

It's the second time I faced this problem.

Solution

  • This happens if you use sp_rename and is explicitly called out in the documentation

    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.

    Note that the rename functionality in SSMS object explorer does in fact call this procedure.