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

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;