Re: How to retrive List of Tables in a Database using...

On Thu, Mar 25, 2010 at 1:26 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> Yogi Yang 007 wrote:
>
>> Hello,
>>
>> I am new to pgSQL. I would like to know if there is a way to do the
>> following using pure SQL:
>> 1. Retrieve list of all Tables in a database
>> 2. Retrieve list of all Functions, Triggers, Sequences, Views, etc.
>> 3. Retrieve list of all Group Roles
>> 4. Retrieve list of all Login Roles
>> 5. Structure of a Table
>>
>
If you want to learn the postgres catalogs, fire up 'psql -E'

When you use the meta-commands like \dt -- lists tables.... then it will
show you the SQL it ran to generate the table list. Run those meta-commands
and pay attention to the emitted SQL, it's helpful.

--Scott

>
> most of that stuff can be fetched from the INFORMATION_SCHEMA, such as ..
>
> select * from information_schema.tables where table_schema not in
> ('pg_catalog','information_schema');
>
> select * from information_schema.columns where table_schema='schemaname'
> and table_name='tablename';
>
>
> note, * on both of these returns a lot of data, you may want to be more
> selective, depending on just what infoyou need to know, especially about the
> columns.
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>