Pour lister l'ensemble des schémas :
select distinct schemaname from pg_catalog.pg_statio_all_tables order by schemaname;
Pour afficher la liste des tables :
select schemaname, relname as tablename, description
from pg_catalog.pg_statio_all_tables st
join pg_catalog.pg_description on (relid = objoid and objsubid = 0)
where schemaname in
('public', 'schema1', 'schema2')
order by schemaname, relname;
Pour afficher la liste des colonnes triées par schéma et par table :
with req as
(SELECT DISTINCT on ( schemaname, tablename, field) schemaname, pg_tables.tablename
,attnum
,pg_attribute.attname AS field
,format_type(pg_attribute.atttypid,NULL) AS "type"
,pg_constraint.conname AS "key"
,CASE pg_attribute.attnotnull
WHEN FALSE THEN null
ELSE 1
END AS "notnull"
,(SELECT col_description(pg_attribute.attrelid,pg_attribute.attnum)) AS COMMENT
,pc2.conname AS ckey
,(SELECT pg_attrdef.adsrc
FROM pg_attrdef
WHERE pg_attrdef.adrelid = pg_class.oid
AND pg_attrdef.adnum = pg_attribute.attnum) AS def
FROM pg_tables,
pg_class
JOIN pg_attribute
ON pg_class.oid = pg_attribute.attrelid
AND pg_attribute.attnum > 0
LEFT JOIN pg_constraint
ON pg_constraint.contype = 'p'::"char"
AND pg_constraint.conrelid = pg_class.oid
AND (pg_attribute.attnum = ANY (pg_constraint.conkey))
LEFT JOIN pg_constraint AS pc2
ON pc2.contype = 'f'::"char"
AND pc2.conrelid = pg_class.oid
AND (pg_attribute.attnum = ANY (pc2.conkey))
WHERE pg_class.relname = pg_tables.tablename
AND pg_attribute.atttypid <> 0::OID
and schemaname in
('public', 'schema1', 'schema2')
ORDER BY schemaname, tablename, field ASC)
select * from req order by schemaname, tablename, attnum;