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.
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
All done!