Sql Server 查询数据库表结构

记录一下，感觉之后有可能会用得上

SELECT
    CASE WHEN sc.column_id=1 THEN so.name ELSE '' END 表名,
    CASE WHEN sc.column_id=1 THEN ISNULL(pt.value,'') ELSE '' END 表说明,
    sc.column_id 字段序号,
    sc.name 字段名,
    ISNULL(pc.value,'') 字段说明,
    CASE WHEN COLUMNPROPERTY(sc.object_id, sc.name, 'IsIdentity')=1 THEN '√' ELSE '' END 标识,
    CASE WHEN EXISTS (SELECT 1 FROM sys.objects WHERE type='PK' AND parent_object_id=sc.object_id AND EXISTS (
        SELECT name FROM sys.indexes WHERE EXISTS (SELECT index_id FROM sys.index_columns WHERE object_id=sc.object_id AND column_id=sc.column_id))
    ) THEN '√' ELSE '' END 主键,
    st.name 类型,
    sc.max_length 占用字节数,
    COLUMNPROPERTY(sc.object_id, sc.name, 'Precision') 长度,
    ISNULL(COLUMNPROPERTY(sc.object_id, sc.name, 'Scale'), 0) 小数位数,
    CASE WHEN sc.is_nullable=1 THEN '√' ELSE '' END 允许空,
    ISNULL(sd.definition,'') 默认值
FROM sys.columns sc
LEFT JOIN sys.types st ON sc.system_type_id=st.system_type_id AND st.name<>'sysname'
INNER JOIN sys.objects so ON sc.object_id=so.object_id AND so.type='U'
LEFT JOIN sys.default_constraints sd ON sc.default_object_id=sd.object_id
LEFT JOIN sys.extended_properties pc ON sc.object_id=pc.major_id AND sc.column_id=pc.minor_id
LEFT JOIN sys.extended_properties pt ON so.object_id=pt.major_id AND pt.minor_id=0
ORDER BY sc.object_id, sc.column_id