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

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

1、Oracle

select a.COLUMN_NAME,a.DATA_TYPE||(case when nvl(a.DATA_SCALE，0) <>0 THEN '('||a.DATA_PRECISION||','||a.DATA_SCALE||')' 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 information_schema.columns WHERE table_name='#table_name#' and TABLE_SCHEMA='#database#'

3、PostgreSQL

SELECT pg_attribute.attname AS COLUMN_NAME,pg_type.typname AS DATA_TYPE,pg_description.description AS COLUMN_COMMENT,case when pg_constraint.conname is not null then 'PRI' end COLUMN_KEY FROM pg_attribute INNER JOIN pg_classON pg_attribute.attrelid = pg_class.oid INNER JOIN pg_type ON pg_attribute.atttypid = pg_type.oid LEFT OUTER JOIN pg_description ON pg_description.objoid = pg_class.oid AND pg_description.objsubid = pg_attribute.attnum left join pg_constraint on pg_constraint.conrelid = pg_class.oid and pg_constraint.contype='p' and pg_attribute.attnum = pg_constraint.conkey[1] WHERE pg_attribute.attnum > 0AND attisdropped <> 't' and pg_class.relname ='#table_name#' ORDER BY pg_attribute.attnum

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