sql-server - 在 SQL Server 中访问表注释

Question

我正在将数据库从 Oracle 迁移到 SQL Server。在 Oracle 中，可以使用 all_tab_comments 表读取表注释。在 SQL Server 中找不到执行此操作的方法。怎么能处理这个？

标签： sql-servercomments

Answer 1

select  t.id                        as  "object_id",
        t.name                      as  "TableName",
        schema_name(t2.schema_id)   as  "Schema",
        t3.name                     as  "Column",
        t4.value                    as  "Column description",
        t5.value                    as  "Table description"
from    sysobjects t
inner join sys.tables t2 on t2.object_id = t.id
inner join sys.columns t3 on t3.object_id = t.id
left join sys.extended_properties t4 on t4.major_id = t.id
                                        and t4.name = 'MS_Description'
                                        and t4.minor_id = t3.column_id
left join sys.extended_properties t5 on t5.major_id = t.id
                                        and t5.name = 'MS_Description'
                                        and t5.minor_id = 0
where   t.name = 'YourTableName'
        and t2.schema_id = schema_id('YourSchemaName')

SQL Server：提取表元数据（描述、字段及其数据类型）