0

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.
2

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.

0

sys.extended properties only had one record when I selected from it. As a result, I was getting no records back due to the INNER JOIN.

Not sure if that's what your case is as well, but I would suggest joining to SysComments, Types, and sys.columns, and sys.types to get what you're looking for:

,ep.major_id
,sc.*
,c.*
,t.*
from sys.objects as o
LEFT join sys.extended_properties as ep on o.object_id = ep.major_id 
LEFT JOIN sys.SysComments as sc ON o.object_id = sc.id
LEFT JOIN sys.columns AS c ON c.column_id = sc.colid
LEFT JOIN sys.types AS t ON c.system_type_id = t.system_type_id
  • 1
    Thanks, @MguerraTorres! But but it does not return the information I'm trying to get. – Vladimir Jul 21 '16 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.