Mysql、Oracle、Postgresql查询数据库表字段信息

1、mysql查询数据表字段信息

SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME='sys_user';

注意：TABLE_SCHEMA 是数据库名，TABLE_NAME是表名

2、Postgresql查询数据表字段信息

SELECT 
    a.attname as name, 
    a.attnotnull as notnull,
    format_type(a.atttypid,a.atttypmod) as type,
    col_description(a.attrelid,a.attnum) as comment       
FROM pg_class as c,pg_attribute as a where c.relname = 'sys_user' and a.attrelid = c.oid and a.attnum>0;

注意：relname是表名

3、Oracle查询数据表字段信息

SELECT b.COLUMN_NAME,b.DATA_TYPE,a.COMMENTS 
FROM USER_TAB_COLUMNS b,USER_COL_COMMENTS a 
WHERE b.TABLE_NAME = 'SYS_USER' AND b.TABLE_NAME = a.TABLE_NAME AND b.COLUMN_NAME = a.COLUMN_NAME;

注意：TABLE_NAME是表名