sys.sql_modules 和 sys.objects 中的 object_name 和定义关系错误 [英] Wrong object_name and definition relationship in sys.sql_modules and sys.objects

问题描述

我运行了以下查询

SELECT sm.object_id,
       v1.object_name,
       o.type,
       o.type_desc,
       sm.definition
FROM sys.sql_modules sm
     CROSS APPLY (VALUES (OBJECT_NAME(sm.object_id))) v1 (object_name)
     JOIN sys.objects o ON sm.object_id = o.object_id;

并且object_name 和definition 之间存在错误关系的三个对象.名称与其引用的定义之间没有匹配项，也没有对应关系.

And there are three objects with a wrong relation between object_name and definition. There is no match, no correspondence between the name and the definition it references.

看起来这个表没有跟踪这三个对象的名称和定义的删除或更改.

It looks like this tables didn't track the delete or changes in name and definitions of these three objects.

怎么才能给出这种情况呢?如何更新"此表或正确修复此问题?

How can this situation can be given? How can I "update" this tables or fix this properly?

推荐答案

这是使用sp_rename"的副作用.
这些对象可以正常工作，但要刷新它们的定义，您需要重新创建它们.

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

来自 sp_rename 文档:

重命名存储过程、函数、视图或触发器不会在定义中更改相应对象的名称sys.sql_modules 目录视图的列或使用OBJECT_DEFINITION 内置函数.因此，我们建议sp_rename 不能用于重命名这些对象类型.相反，放下并使用新名称重新创建对象.

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.

这篇关于sys.sql_modules 和 sys.objects 中的 object_name 和定义关系错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！