Wrong Object_name And Definition Relationship In Sys.sql_modules And Sys.objects

I ran the following query SELECT sm.object_id, v1.object_name, o.type, o.type_desc, sm.definition FROM sys.sql_modules sm CROSS APPLY (VALUES (OBJE

Solution 1:

This is a side effect of using "sp_rename". These objects will work fine, but to refresh their definitions you need to recreate them.

From sp_rename documentation:

Renaming a stored procedure, function, view, or trigger will not change the name of the corresponding object either in the definition column of the sys.sql_modules catalog view or obtained using the OBJECT_DEFINITION built-in function. 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.