Information about Greenplum Schema, Tables and Columns

posted Apr 28, 2017, 12:06 PM by Sachchida Ojha

List all the schemas:

select * from pg_namespace;
select * from information_schema.schemata;

List all the tables within all the schemas:

select n.nspname,c.relname
from pg_class c inner join pg_namespace n
on c.relnamespace = n.oid
where c.relkind in ('r','v')
order by n.nspname,
c.relname;

select * from information_schema.tables
order by table_schema,
table_name;

List all the columns of all the tables within all the schemas:

select n.nspname,
c.relname,
a.attname
from pg_class c,
pg_namespace n,
pg_attribute a
where c.relkind in ('r','v')
and c.relnamespace = n.oid
and a.attrelid = c.oid
and a.attnum > 0
order by n.nspname,
c.relname,
a.attnum;

select * from information_schema.columns
order by table_schema,
table_name,ordinal_position;

Comments