PgSQL、MySQL、SQLServer查询某张表的表结构

PgSQL、MySQL、SQLServer查询某张表的表结构

记录一下Pg SQL、MySQL、SQLServe查询某张表的表结构，方便更新表结构文档

---

PgSQL查询表结构

SELECT
    a.column_name AS 字段名称,
    udt_name AS 字段类型,
    (CASE
        WHEN COALESCE(character_maximum_length, -999) = -999 THEN numeric_precision
        ELSE character_maximum_length
    END)||(CASE
        WHEN udt_name IN ('numeric', 'decimal') THEN ','||numeric_scale::text
        ELSE '' end) AS 字段长度,
    
    CASE
        WHEN is_nullable = 'YES' THEN '√'
        ELSE ''
    END AS 是否可为空,
    CASE
        WHEN pk.constraint_name IS NOT NULL THEN '√'
        ELSE ''
    END AS 是否为主键,
    column_default AS 默认值,
		col_description(b.oid, a.ordinal_position) AS 字段说明
FROM
    information_schema.columns AS a
LEFT JOIN
    pg_class AS b ON a.table_name = b.relname
LEFT JOIN
    information_schema.key_column_usage AS pk ON a.table_name = pk.table_name AND a.column_name = pk.column_name
LEFT JOIN
    information_schema.table_constraints AS tc ON pk.constraint_name = tc.constraint_name
WHERE
    a.table_name = '表名'
ORDER BY
    a.table_schema,
    a.table_name,
    a.ordinal_position;

查询结果：

---

MySQL表结构程序

* MySQL 表结构查询是当前连接的所有数据库的表结构，所以要加上c.TABLE_SCHEMA ='dbname' 条件指定数据库名称，不加的话如果多个数据库有相同的表名也会查出来

SELECT
    c.COLUMN_NAME AS `字段名称`,
    c.DATA_TYPE AS `字段类型`,
    CASE
        WHEN c.CHARACTER_MAXIMUM_LENGTH IS NULL THEN CONCAT_WS(',', c.NUMERIC_PRECISION, c.NUMERIC_SCALE)
        ELSE CAST(c.CHARACTER_MAXIMUM_LENGTH AS CHAR)
    END AS `字段长度`,
    c.COLUMN_DEFAULT AS `默认值`,
    IF(c.IS_NULLABLE = 'YES', '√', '') AS `是否可为空`,
    IF(c.COLUMN_KEY = 'PRI', '√', '') AS `是否为主键`,
    IFNULL(c.COLUMN_COMMENT, '') AS `字段说明`
FROM
    INFORMATION_SCHEMA.COLUMNS c
WHERE
    c.TABLE_NAME = 'system_users' and c.TABLE_SCHEMA ='dbname'
ORDER BY
    c.ORDINAL_POSITION;

查询结果：

---

SQLServer查询表结构

SELECT
    c.COLUMN_NAME AS 字段名称,
    c.DATA_TYPE AS 字段类型,
   CAST(( CASE
        WHEN ISNULL(c.CHARACTER_MAXIMUM_LENGTH, -999) = -999 THEN c.NUMERIC_PRECISION
        ELSE  c.CHARACTER_MAXIMUM_LENGTH
    END) AS NVARCHAR(MAX)) + 
    CASE
        WHEN c.DATA_TYPE IN ('numeric', 'decimal') THEN ',' + CAST(ISNULL(c.NUMERIC_SCALE, 0) AS NVARCHAR(MAX))
        ELSE ''
    END AS 字段长度,
    ISNULL(c.COLUMN_DEFAULT, '') AS 默认值,
    case when ISNULL(c.IS_NULLABLE, '') = 'YES' then '√' else '' end AS 是否可为空,
    CASE
        WHEN ic.index_id IS NOT NULL THEN '√'
        ELSE ''
    END AS 是否为主键,
    ISNULL(ep.value, '') AS 字段说明
FROM
    INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN
    sys.indexes i ON OBJECT_NAME(i.OBJECT_ID) = c.TABLE_NAME AND i.is_primary_key = 1
LEFT JOIN
    sys.index_columns ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id AND ic.column_id = c.ORDINAL_POSITION
LEFT JOIN
    sys.extended_properties ep ON ep.major_id = OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME) AND ep.minor_id = c.ORDINAL_POSITION AND ep.name = 'MS_Description'
WHERE
    c.TABLE_NAME = '表名'
ORDER BY
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.ORDINAL_POSITION;

查询结果：