[oracle如何查询表字段]oracle查询表字段和备注!

开发代码生成器时需要用到这个，比如根据数据库里一个表，自动生成增删改查的代码

整理了几个数据库的查询方法

1、Oracle

select a.COLUMN_NAME,a.DATA_TYPE||(case when nvl，0) <>0 THEN '('||','||')' END) DATA_TYPE,b.COMMENTS COLUMN_COMMENT,case when constraint_type = 'P' then 'PRI' end COLUMN_KEY from user_tab_columns a left join user_col_comments b on a.table_name=b.table_name and a.COLUMN_NAME=b.COLUMN_NAME left join user_cons_columns ucc on ucc.table_name=a.table_name and ucc.column_name=a.column_name and ucc.constraint_name like 'PK%' left join user_constraints uc on uc.constraint_name = ucc.constraint_name and uc.constraint_type='P' where a.Table_Name='#table_name#'order by a.column_name

2、MySQL

SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT,COLUMN_KEY FROM in WHERE table_name='#table_name#' and TABLE_SCHEMA='#database#'

3、PostgreSQL

SELECT AS COLUMN_NAME, AS DATA_TYPE, AS COLUMN_COMMENT,case when is not null then 'PRI' end COLUMN_KEY FROM pg_attribute INNER JOIN pg_classON = INNER JOIN pg_type ON = LEFT OUTER JOIN pg_description ON = AND = left join pg_constraint on = and ;p' and = [1] WHERE > 0AND attisdropped <> 't' and ='#table_name#' ORDER BY

通过这几个 sql，可以查询到对应的表的字段和类型，以及表主键，注意把 sql 里的#table_name#换成表名，#database#换成数据库名即可。