主页 / dba / 问题 / 144553
Accepted
Vladimir
Asked: 2016-07-22 06:51:12 +0800 CST

查询表/视图及其列的 SQL Server 扩展属性

  • 772

我需要在 SQL Server 中获取有关表/视图及其列的元数据信息。当我为表/视图（sys.tables）或列（sys.columns）单独运行查询时，我可以获得扩展属性值，但是当我加入 sys.tables 和 sys.columns 时，某些东西无法正常工作。

这是我到目前为止所做的，缺少的信息是表的扩展属性和列名的值。

在网上搜索我无法找到有关它的信息，有人可以帮助我完成这项任务吗？

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

期望的结果是这样的：

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.
sql-server t-sql

2 个回答

  1. Best Answer

    如果您希望为数据库中的所有列返回列级扩展属性，请使用以下查询：

    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

    从Working with SQL Server Extended Properties获取（和修改）查询。

    • 2

  2. 当我从中选择时，sys.extended 属性只有一条记录。结果，由于 INNER JOIN，我没有收到任何记录。

    不确定这是否也是您的情况，但我建议加入 SysComments、Types 和 sys.columns 和 sys.types 以获得您正在寻找的内容：

    ,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
    • 0

相关问题