How do I map the id in sys.extended_properties to an object name

Question

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?

score 3 · Answer 1 · answered May 10 '11 at 16:43

3

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

answered May 10 '11 at 16:43

gbn

422,506
82
585
676

Is `sys.sql_expression_dependencies` reliable? IIRC, it's possible to `CREATE` or `ALTER` a stored procedure even if the parser can't determine all of the dependent objects. In that case wouldn't they be missing from the DMV? – Yuck May 10 '11 at 16:48
@Yuck: I'm not sure off top of my head. It replaces older sys tables that were unreliable but I can't recall how accurate this one is, sorry. Edit: yep, it's better https://sqlblog.org/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008 – gbn May 10 '11 at 16:53

score 3 · Accepted Answer · answered May 10 '11 at 16:43

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:

select object_name([major_id]), [name], [value]
from sys.extended_properties

score 2 · Answer 3 · answered May 10 '11 at 16:43

From MSDN, you have to examine the class field as well as major_id.

If class is 0, major_id is always 0.

If class is 1, 2, or 7 major_id is object_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_id for one such assembly is 65673 and there isn't even a mismatched object in sys.objects for 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.

score 1 · Answer 4 · answered May 10 '11 at 18:16

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/

How do I map the id in sys.extended_properties to an object name

4 Answers4