如何将 sys.extended_properties 中的 id 映射到对象名称
当我们对 CRUD 存储过程进行代码生成时，我们添加一个扩展属性来指示它们所基于的表。我希望能够询问 sys.extended_properties 来获取依赖于给定表的进程列表，但是extended_properties 持有的 Major_id 似乎不是与 sys.objects 或 sys.sysobjects 中的 object_id 相同。
我需要什么神奇的加入？
When we codegen our CRUD stored procs, we add an extended property that indicates the table they are based on. I want to be able to interrogate sys.extended_properties to get a list of procs that depend on a given table, but extended_properties holds a major_id which doesn't seem to be the same as the object_id in sys.objects or sys.sysobjects.
What is the magic join I need?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
从 sys.extended_properties 的在线书籍条目中，如果类 = 1、2 或 7，则 Major_id = object_id。如果运行手动查询，则属性的类是什么？我的猜测是以下内容会起作用：
From the books online entry for sys.extended_properties, major_id = object_id if the class = 1, 2, or 7. If you run a manual query, what is the class of the property? My guess is that the following will work:
sys.extended_properties 的 MSDN 描述了major_id 和minor_id。
上下文取决于类列。
现在，当有意义时， sys.objects 中的 Major_id = object_id 。
我之前用过它来测试扩展属性，所以我知道它是有效的。
但是，在第二次阅读您的问题时，存储过程的扩展属性与过程中使用的表之间没有直接链接。如果我，您必须通过 sys.sql_expression_dependencies 进行连接正确理解你
MSDN for sys.extended_properties describes both major_id and minor_id.
Context depends on the class column.
Now, major_id = object_id in sys.objects when it makes sense.
I've used this before to test for extended properties so I know it works.
However, on 2nd reading of your question there is no direct link between the extended property of a stored proc and the tables that are used in the proc. You'd have to go via sys.sql_expression_dependencies to connect, if I understand you correctly
从 MSDN 中，您必须检查
类字段以及major_id。其他课程似乎没有记录，因此我不会依赖它们。例如，类 5 似乎用于 CLR 程序集 (SqlAssemblyProjectRoot)。在我的例子中，此类程序集的
major_id是 65673，并且sys.objects中甚至没有该 ID 不匹配的对象。编辑：我想补充一点，记录的 - 1、2、7 - 对应于 (1) 对象（例如表、存储过程）或列，(2) 参数，(7) 索引。就您而言，它应该涵盖您正在寻找的内容。
From MSDN, you have to examine the
classfield as well asmajor_id.Other classes appear to be undocumented and as such I wouldn't rely on them. For example, class 5 appears to be for CLR assemblies (SqlAssemblyProjectRoot). In my case the
major_idfor one such assembly is 65673 and there isn't even a mismatched object insys.objectsfor that ID.EDIT: I wanted to add that the documented ones - 1, 2, 7 - correspond to (1) Object (e.g. table, stored procedure) or column, (2) Parameter, (7) Index. In your case it should cover what you're looking for.
不直接回答您的问题，而是一些其他资源：
我有一个关于使用内置扩展属性进行文档等内容的演示：http://code.google.com/p/caderoux/wiki/LeversAndTurtles
Adam Aspin 最近在 SQLServer Central 上发表了一系列有关扩展属性的文章：
http://www.sqlservercentral.com/articles/Metadata/72607/
http://www.sqlservercentral.com/articles/Metadata/72608/
http://www.sqlservercentral.com/articles/Metadata/72609/
http://www.sqlservercentral.com/articles/Metadata/72610/
Not directly answering your question, but some additional resources:
I have a presentation about using the built-in extended properties for things like documentation: http://code.google.com/p/caderoux/wiki/LeversAndTurtles
There has also been a recent series of articles on extended properties by Adam Aspin published at SQLServer Central:
http://www.sqlservercentral.com/articles/Metadata/72607/
http://www.sqlservercentral.com/articles/Metadata/72608/
http://www.sqlservercentral.com/articles/Metadata/72609/
http://www.sqlservercentral.com/articles/Metadata/72610/