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

pgsql、mysql、sqlserver查询某张表的表结构

记录一下pg sql、mysql、sqlserve查询某张表的表结构，方便更新表结构文档

文章目录

pgsql、mysql、sqlserver查询某张表的表结构
pgsql查询表结构
mysql表结构程序
sqlserver查询表结构

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表结构程序

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'
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;