SQL Server – Querying Extended Properties for Tables/Views and Columns

sql servert-sql

I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended properties values, but when I join sys.tables and sys.columns something doesn't work properly.

Here is what I've done so far, the missing information is the value for the table's extended properties and column name.

Searching the web I wasn't able to find information about it, could somebody help me with this task?

select
    case
        when o.type_desc = 'user_table' and ep.minor_id = 0 then 'Table'
        when o.type_desc = 'user_table' and ep.minor_id > 0 then concat('Column from ', o.name)
        when o.type_desc = 'view' and ep.minor_id = 0 then 'View'
        when o.type_desc = 'view' and ep.minor_id > 0 then 'View'
        when o.type_desc = 'sql_stored_procedure' and ep.minor_id = 0 then 'Stored procedure'
        when o.type_desc = 'sql_trigger' and ep.minor_id = 0 then 'Trigger'
        else '---'
    end as object_type,
    o.name as object_name,
    ep.value as object_descripion,
    o.type,
    o.type_desc
from sys.objects as o
inner join sys.extended_properties as ep on o.object_id = ep.major_id --and ep.minor_id = 0
where len(convert(varchar, ep.value)) > 1 and o.name = 'foo'
order by o.type_desc, o.name

The desired result is something like:

object_type                 object_name         object_descripion
Table                       foo                 Table description.
Column from foo             column_1            column_1 description.
Column from foo             column_2            column_2 description.

Best Answer

If you wish to return Column level Extended Properties for all columns in the database, use this query:

SELECT
    ObjectType = o.type_desc,
    SchemaName = SCHEMA_NAME(o.schema_id),  
    ObjectName = o.name, 
    ColumnName = clmns.name,
    ExtendedPropertyName = p.name,
    ExtendedPropertyValue = CAST(p.value AS sql_variant)

FROM sys.objects AS o
INNER JOIN sys.all_columns AS clmns
    ON clmns.object_id = o.object_id
INNER JOIN sys.extended_properties AS p
    ON p.major_id = o.object_id
    AND p.minor_id = clmns.column_id
    AND p.class = 1

WHERE o.type IN ('U','V') -- User Tables and Views

Query was taken (and modified) from Working with SQL Server Extended Properties.

Related Question