CODEWITHSUNDEEP
sql-server
George Ober
George Ober

Reputation: 961

How to get a list of all extended properties for all objects

I’ve been tasked with creating a query that will return following data:

[Schema Name], [Object Name], [Extended Property Name], [Extended Property Value]

Any ideas how to accomplish this? I’ve found fn_listextendedproperty function but that doesn’t help much.

Upvotes: 21

Views: 37340

Answers (2)

roncansan
roncansan

Reputation: 2380

This one brings all columns of an specific object, and its properties if they exists.

SELECT  
    O.name AS [Object Name],
    c.name, 
    ep.value AS [Extended property]
FROM 
    sys.columns AS c INNER JOIN 
    sys.all_objects O ON c.object_id = O.object_id LEFT JOIN 
    sys.extended_properties EP ON ep.major_id = c.object_id AND ep.minor_id = c.column_id   
WHERE
    O.name = 'table-name'

Upvotes: 5

Joel Cuff
Joel Cuff

Reputation: 1038

Use this query which is column specific:

SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
INNER JOIN sys.all_objects O ON ep.major_id = O.object_id 
INNER JOIN sys.schemas S on O.schema_id = S.schema_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

Use this query for all extended properites:

SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id 
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id

Upvotes: 38

Related Questions