Information about Greenplum Schema, Tables and Columns

Post date: Sep 25, 2014 1:9:0 AM

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;