How to query extended properties

If you are working in a company that your managers are getting database documentation seriously, thumbs up! One of the ways to write useful documentation that is really effective is using SQL Server extended properties.

We can add extended properties to almost all SQL Server objects by right clicking on the object-> select properties-> select “Extended Properties” and add new properties to the object.

clip_image002

So, after adding the new properties we might need to query those properties in the future.

Executing the following T-SQL script retrieves what we need:

select O.name ObjectName, e.name PropertyName, value

from sys.extended_properties e inner join sys.objects o on e.major_id=o.object_id

 

clip_image003

All done!

2 thoughts on “How to query extended properties

  1. But how do you bring the extended properties into PBI – seems like such an obvious thing to want to do in a BI tool – with both products being from MSFT too….

    1. Hi Phil,

      Welcome to BIInsight.com.
      That’s easy.
      You can simply copy the T-SQL code above when connecting to a SQL Server instance.
      On the “SQL Server Database” window click “Advanced Options” then paste the T-SQL you copied in the “SQL Statement” textbox.
      Running T-SQL query in Power BI Desktop
      Hopefully that helps.
      Cheers.

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.