CODEWITHSUNDEEP
sql-serverextended-propertiessysobjects
Rikalous
Rikalous

Reputation: 4574

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

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?

Upvotes: 0

Views: 3851

Answers (4)

Cade Roux
Cade Roux

Reputation: 89721

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/

Upvotes: 1

Ben Thul
Ben Thul

Reputation: 32737

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

Upvotes: 3

gbn
gbn

Reputation: 432491

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

Upvotes: 3

Yuck
Yuck

Reputation: 50855

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.

Upvotes: 2

Related Questions