查询数据库表的基本信息

query.sql

SELECT tb.name       AS "表名",
       c.name        AS "字段名称",
       p.value       AS "字段注释",
       t.name        AS "字段类型",
       c.max_length  AS "字段长度",
       c.is_nullable AS "是否可空",
       c.is_identity AS "是否主键"
FROM sys.tables AS tb
         INNER JOIN sys.columns AS c ON tb.object_id = c.object_id
         INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
    AND is_user_defined = 0
    AND t.name <> 'sysname'
         LEFT OUTER JOIN sys.extended_properties AS p ON p.major_id = tb.object_id
    AND p.minor_id = c.column_id
    AND p.name = 'MS_Description'

ORDER BY t.name, c.object_id