各种数据库拿到某表的字段和备注信息

Oracle表字段和备注信息获取

select
    a.OWNER as table_schema,
    a.TABLE_NAME as table_name,
    d.COMMENTS as tale_comments,
    a.COLUMN_NAME as column_name,
    a.COLUMN_ID as column_id,
    c.COMMENTS as col_comments,
    a.DATA_TYPE as data_type,
    a.DATA_PRECISION as data_len,
    a.CHAR_LENGTH as char_len,
    a.DATA_SCALE as data_scale,
    DBMS_LOB.substr(b.constraint_type,100,1) as constraint_type
from ALL_TAB_COLS a
left join (
    SELECT
        ucc.table_name AS table_name,
        ucc.column_name AS column_name,
        wm_concat(uc.constraint_type) AS constraint_type
    FROM
        all_cons_columns ucc
            LEFT JOIN all_constraints uc on ucc.constraint_name = uc.constraint_name
    where ucc.OWNER='%s'
    GROUP BY
        ucc.table_name,
        ucc.column_name
) b on a.TABLE_NAME=b.table_name and a.COLUMN_NAME=b.column_name
LEFT JOIN all_col_comments c on a.OWNER=c.OWNER and a.TABLE_NAME=c.TABLE_NAME and a.COLUMN_NAME=c.COLUMN_NAME
left join all_TAB_COMMENTS d on a.OWNER=d.OWNER and a.TABLE_NAME=d.TABLE_NAME
where 1=1
  and a.OWNER='test'
  and a.TABLE_NAME in ('tablenames')
  and a.COLUMN_ID is not null
  and a.COLUMN_NAME not in ('name','id')
order by a.COLUMN_ID asc

Postgrep表字段和备注信息获取

select
       t7.nspname     table_schema,
       t1.relname     table_name,
       t4.description tale_comments,
       t2.attname     column_name,
       t2.attnum   column_id,
       t3.description col_comments,
       t5.typname     data_type,
       t2.attlen      data_len,
       0      data_scale,
       t2.atttypmod   char_len,
       t6.contype     constraint_type
from pg_class t1
         join pg_attribute t2 on t1.oid = t2.attrelid
         left join pg_description t3 on t2.attnum = t3.objsubid and t2.attrelid = t3.objoid
         left join pg_description t4 on t1.oid = t4.objoid and t4.objsubid = 0
         left join pg_type t5 on t2.atttypid = t5.OID
         left join pg_constraint t6 on t1.oid = t6.conrelid and t2.attnum = t6.conkey[1]
         join pg_namespace t7 on t1.relnamespace = t7.oid
 where t7.nspname = 'public'
  and t1.relname in ( 'table_rpt_list' )
  and t2.attnum > 0

Mysql表字段和备注信息获取

SELECT
    a.TABLE_SCHEMA as table_schema,
    a.TABLE_NAME as table_name,
    b.TABLE_COMMENT as tale_comments,
    a.COLUMN_NAME as column_name,
    a.ORDINAL_POSITION as column_id,
    a.COLUMN_COMMENT as col_comments,
    a.DATA_TYPE as data_type,
    a.NUMERIC_PRECISION as data_len,
    a.NUMERIC_SCALE as data_scale,
    a.CHARACTER_MAXIMUM_LENGTH as char_len,
    a.COLUMN_KEY as constraint_type
FROM INFORMATION_SCHEMA.COLUMNS a
left join information_schema.TABLES b on a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
WHERE
    a.TABLE_SCHEMA = 'public'
    AND a.TABLE_NAME in ('tablenames')
    order by a.ORDINAL_POSITION asc

SqlServer表字段和备注信息获取

SELECT
    a.TABLE_NAME as table_name,
    '' as tale_comments,
    a.COLUMN_NAME as column_name,
    a.ORDINAL_POSITION as column_id,
    '' as col_comments,
    a.DATA_TYPE as data_type,
    a.NUMERIC_PRECISION as data_len,
    a.NUMERIC_SCALE as data_scale,
    a.CHARACTER_MAXIMUM_LENGTH as char_len,
    c.CONSTRAINT_TYPE as constraint_type
FROM INFORMATION_SCHEMA.COLUMNS a
left join INFORMATION_SCHEMA.TABLES b
    on a.TABLE_CATALOG=b.TABLE_CATALOG and a.TABLE_SCHEMA=b.TABLE_SCHEMA and a.TABLE_NAME=b.TABLE_NAME
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
    on a.TABLE_CATALOG=c.TABLE_CATALOG and a.TABLE_SCHEMA=c.TABLE_SCHEMA and a.TABLE_NAME=c.TABLE_NAME
WHERE
        a.TABLE_CATALOG='tables'
  and a.TABLE_SCHEMA='dbo'