Oracle Mysql Postgrsql 查询表的所有字段（字段名称、字段类型、字段长度）、主键

一、查询表字段：

1.Oracle数据库脚本：

-- 查询表字段 oracle
select UPPER(B.COLUMN_NAME) as FIELD_NAME,
       UPPER(B.DATA_TYPE) as FIELD_DATA_TYPE,
       case
         when B.DATA_SCALE is null then
          0
         else
          B.DATA_SCALE
       end as FIELD_DECIMAL,
       (case
         when B.DATA_PRECISION is null then
          B.CHAR_LENGTH
         else
          B.DATA_PRECISION
       end) as FIELD_LENGTH,
       case
         when B.NULLABLE = 'N' then
          '1'
         else
          '0'
       end as IS_NULL,
       C.COMMENTS as FIELD_COMMENT
  from (select A.TABLE_NAME,
               A.COLUMN_NAME,
               A.DATA_TYPE,
               A.DATA_SCALE,
               A.DATA_PRECISION,
               A.CHAR_LENGTH,
               A.NULLABLE
          from USER_TAB_COLUMNS A
         where A.TABLE_NAME = UPPER('表名')) B
  left join USER_COL_COMMENTS C
    on B.TABLE_NAME = C.TABLE_NAME
   and B.COLUMN_NAME = C.COLUMN_NAME
 order by B.COLUMN_NAME;

2.Mysql 数据库脚本：

select UPPER(COLUMN_NAME) FIELD_NAME,
       UPPER(DATA_TYPE) FIELD_DATA_TYPE,
       case
         when NUMERIC_SCALE is null then
          0
         else
          NUMERIC_SCALE
       end as FIELD_DECIMAL,
       case
         when CHARACTER_MAXIMUM_LENGTH is null then
          NUMERIC_PRECISION
         else
          CHARACTER_MAXIMUM_LENGTH
       end FIELD_LENGTH,
       case
         when UPPER(IS_NULLABLE) = 'NO' then
          '0'
         else
          '1'
       end IS_NULL,
       COLUMN_COMMENT FIELD_COMMENT
  from INFORMATION_SCHEMA.COLUMNS
 where TABLE_SCHEMA = 'scheme名称或者数据库用户名'
   and UPPER(TABLE_NAME) = UPPER('表名')
 order by COLUMN_NAME;

3.postgresql数据库脚本：

select UPPER(A.ATTNAME) FIELD_NAME,
       UPPER(P.TYPNAME) FIELD_DATA_TYPE,
       A.ATTTYPMOD - 4 as FIELD_LENGTH,
       case
         when A.ATTNOTNULL = 'T' then
          '0'
         else
          '1'
       end as IS_NULL,
       DB.DESCRIPTION FIELD_COMMENT
  from PG_TABLES T
 inner join PG_CLASS C
    on T.TABLENAME = C.RELNAME
  left join PG_DESCRIPTION D
    on C.OID = D.OBJOID
   and D.OBJSUBID = 0
  left join PG_DESCRIPTION DB
    on C.OID = DB.OBJOID
   and DB.OBJSUBID > 0
  left join PG_ATTRIBUTE A
    on C.OID = A.ATTRELID
   and DB.OBJSUBID = A.ATTNUM
  left join PG_TYPE P
    on A.ATTTYPID = P.OID
  left join PG_ATTRDEF AT
    on C.OID = AT.ADRELID
   and A.ATTNUM = AT.ADNUM
 where UPPER(C.RELNAME) = UPPER('表名') 
 order by A.ATTNUM;

二、查询表主键

1.oracle数据库：

select UPPER(COLUMN_NAME)
  from USER_CONS_COLUMNS
 where UPPER(TABLE_NAME) = UPPER('表名')
   and CONSTRAINT_NAME in (select CONSTRAINT_NAME
                             from USER_CONSTRAINTS
                            where UPPER(TABLE_NAME) = UPPER('表名')
                              and CONSTRAINT_TYPE = 'P');

2.mysql数据库：

select UPPER(COLUMN_NAME)
  from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 where UPPER(TABLE_NAME) = UPPER('表名')
   and TABLE_SCHEMA = 'scheme实例名或者用户名'
   and CONSTRAINT_NAME = 'PRIMARY';

3.postgresql数据库：

select PG_ATTRIBUTE.ATTNAME
  from PG_CONSTRAINT
 inner join PG_CLASS
    on PG_CONSTRAINT.CONRELID = PG_CLASS.OID
 inner join PG_ATTRIBUTE
    on PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID
   and PG_ATTRIBUTE.ATTNUM = PG_CONSTRAINT.CONKEY[1]
 inner join PG_TYPE
    on PG_TYPE.OID = PG_ATTRIBUTE.ATTTYPID
 where UPPER(PG_CLASS.RELNAME) = UPPER('表名')
   and PG_CONSTRAINT.CONTYPE = 'p';