Thread: How can I get the Db Schema Info??

Hi,

I have a query regarding the SCHEMA INFORMATION.
I require following information about a database i.e
1)How many tables are there?
2)What are their names?
3)What are the column types in each of the tables?
4)Primary key and foreign key information about a specific table.

This information is available by running commands like \d <table name>,
but what if
I want to execute a query to get this information.
i.e. I need the result in the recordset format for which I am require to
execute the query.
So, are there any sysobjects, System tables which stores the catalog
information?
Pg_tables is one but what about the column info (data types and all).

For e.g.:
select * from pg_tables where schemaname = 'public';
Gives all the user tables but I need the complete picture.

Thanks in advance,

Rajan Bhide

On Thu, Aug 12, 2004 at 11:21:30 +0530,
  Rajan Bhide <rbhide@nulinkinc.com> wrote:
> Hi,
>
> I have a query regarding the SCHEMA INFORMATION.
> I require following information about a database i.e
> 1)How many tables are there?
> 2)What are their names?
> 3)What are the column types in each of the tables?
> 4)Primary key and foreign key information about a specific table.
>
> This information is available by running commands like \d <table name>,
> but what if
> I want to execute a query to get this information.
> i.e. I need the result in the recordset format for which I am require to
> execute the query.
> So, are there any sysobjects, System tables which stores the catalog
> information?

If you use the -E option on psql you can see what queries it uses to
get the information. Much of this information is available through
the information schema. The postgres documentation has both information
about the system tables and the information schema.