获取MSSQL 表结构中字段的备注、主键等信息的sql

获取MSSQL 表结构中字段的备注、主键等信息的SQL，可通过查询系统表和视图来完成。

以下是获取列备注和主键信息的示例SQL：

1. 获取表中所有列的备注信息和主键信息

SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.is_nullable AS Nullable,
    p.value AS ColumnDescription,
    pk.name AS PrimaryKey
FROM 
    sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
    LEFT JOIN sys.extended_properties p ON p.major_id = c.object_id AND p.minor_id = c.column_id
    LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT JOIN sys.indexes pk ON pk.object_id = ic.object_id AND pk.index_id = ic.index_id AND pk.is_primary_key = 1
WHERE 
    c.object_id = OBJECT_ID('YourTableName')

1. 获取指定列的备注信息和主键信息

SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.is_nullable AS Nullable,
    p.value AS ColumnDescription,
    pk.name AS PrimaryKey
FROM 
    sys.columns c
    INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
    LEFT JOIN sys.extended_properties p ON p.major_id = c.object_id AND p.minor_id = c.column_id
    LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT JOIN sys.indexes pk ON pk.object_id = ic.object_id AND pk.index_id = ic.index_id AND pk.is_primary_key = 1
WHERE 
    c.object_id = OBJECT_ID('YourTableName')
    AND c.name = 'YourColumnName'

以上两个示例查询语句中使用了sys.columns系统表和sys.extended_properties视图来获取表结构信息。其中，sys.columns系统表包含了关于表中各列的信息，而sys.extended_properties视图则包含了所有表、列、约束和其它对象的扩展属性。两个表可以通过object_id和minor_id两个列的值进行联合，从而获取到列的备注信息。

此外，上述查询语句还使用了sys.index_columns和sys.indexes系统表来获取指定列是否为主键。其中，sys.index_columns系统表包括了关于表中所有索引列的信息，而sys.indexes系统表则包含了关于表中所有索引的信息。两个表可以通过object_id和index_id两个列的值进行联合，从而获取到主键信息。