人大金仓-数据库表及字段查询

SELECT c.relname AS 表名, a.attname AS 字段名, format_type(a.atttypid, a.atttypmod) AS 数据类型, CASE WHEN a.attnotnull true THEN 是 ELSE 否 END AS 是否必填, CASE WHEN a.atthasdef true THEN 是 ELSE 否 END AS 是否有默认值, pg_get_expr(d.adbin, d.adrelid) AS 默认值, col_description(a.attrelid, a.attnum) AS 字段注释 FROM sys_class c JOIN sys_namespace n ON c.relnamespace n.oid JOIN sys_attribute a ON a.attrelid c.oid -- 关联默认值系统表 LEFT JOIN sys_attrdef d ON a.attrelid d.adrelid AND a.attnum d.adnum WHERE n.nspname xxxx -- 模式名 AND c.relkind r -- 普通表 AND a.attnum 0 -- 排除系统隐藏字段 ORDER BY c.relname, a.attnum;