Thread: db andtable schemas

db andtable schemas

From
"Jamie Lawrence Jenner"
Date:

Hi there

 

I am creating a code generator to build our project from our postgress data base

 

But, what I cant seem to find is the sql to do the following

 

  1. Retrieve a list of tables from a given database
  2. How to retrieve a list of table properties from a table
  3. how retireve what constraints a table has
  4. foreign ekeys
  5. primary keys

 

I would be really greatful if someone could point me in the direction

 

Many thanks

 

Jamie

Re: db andtable schemas

From
George Weaver
Date:
---  Original Message From: Jamie Lawrence Jenner ---

> Retrieve a list of tables from a given database
> How to retrieve a list of table properties from a table
> how retireve what constraints a table has
> foreign ekeys
> primary keys

> I would be really greatful if someone could point me in the direction

Jamie, the type of metadata you're looking for is detailed in the
Information Schema:

http://www.postgresql.org/docs/8.2/static/infoschema-schema.html

and the PostgreSQL system catalogs:

http://www.postgresql.org/docs/8.2/static/catalogs-overview.html

You can use ordinary SQL queries to pull the inforamation you require from
these sources.

Regards,
George



Re: db andtable schemas

From
Richard Broersma Jr
Date:
> > Retrieve a list of tables from a given database
> > How to retrieve a list of table properties from a table
> > how retireve what constraints a table has
> > foreign ekeys
> > primary keys
> > I would be really greatful if someone could point me in the direction
>
> Jamie, the type of metadata you're looking for is detailed in the
> Information Schema:
> http://www.postgresql.org/docs/8.2/static/infoschema-schema.html
> and the PostgreSQL system catalogs:
> http://www.postgresql.org/docs/8.2/static/catalogs-overview.html
> You can use ordinary SQL queries to pull the inforamation you require from
> these sources.

Another method is to cheat. ;-) I learned this useful trick from another user on the list.

start psql with the the option "-E".  Such as:

psql -d postgres -U postgres -E

then from inside psql if you want a list of all of the tables in your current schema you would use
the psql command "\dt".  Because the "-E" option was used in calling psql, the actual sql query is
echoed to the screen for you to see.  This works for any of the psql commands that issue queries
in the background.

Regards,

Richard Broersma Jr.