PostpreSQL 表元数据查询

PostpreSQL 表元数据查询

-- 查询所有模式信息
SELECT * FROM information_schema.schemata;
-- 查询当前数据库
select current_database();
-- 查询当前模式
select current_schema();
-- 查询当前模式的信息，oid与pg_class的relnamespace关联
select oid, * from pg_namespace where nspname = current_schema();

-- 查询当前模式下的表信息  relkind: r = 普通表，i = 索引， S = 序列，t = TOAST 表，v = 视图， m = 物化视图， c = 组合类型， f = 外部表， p = 分区表
select * from pg_class where relkind ='r' and relnamespace = (select oid from pg_namespace where nspname = current_schema());
select a.relname from pg_class a, pg_namespace b where a.relnamespace = b.oid and b.nspname = current_schema() and a.relkind ='r' and a.relname = 'table_name';

-- 查询注释
select * from pg_description;
select a.description from pg_description a, pg_class b, pg_namespace c where a.objoid = b.oid and  b.relnamespace = c.oid and c.nspname = current_schema() and b.relkind ='r' and b.relname = 'table_name' and a.objsubid = 0;

-- 查询表字段信息
select attname, typname, description, adsrc from (
select a.oid,a.relname,c.attnum,c.attname,d.typname, e.description from pg_class a, pg_namespace b, pg_attribute c, pg_type d, pg_description e 
where a.relnamespace = b.oid and a.oid = c.attrelid and c.atttypid = d.oid and e.objoid = a.oid and e.objsubid = c.attnum 
and b.nspname = current_schema() and a.relkind ='r' and a.relname = 'table_name' and c.attnum > 0 order by c.attnum) v 
left join pg_attrdef f on f.adrelid = v.oid and f.adnum = v.attnum;