Thread: how to list all tables in a database

Hello all,

this is my first question... I'm really new to PG.

How do I get a list of all tables in a database ?

Is there an SQL command for this ?

thx

jmf

On 1/9/06 12:37 PM, "Joao Miguel Ferreira" <jmf@estg.ipvc.pt> wrote:

> this is my first question... I'm really new to PG.
>
> How do I get a list of all tables in a database ?

Bom Dia!  Bemvindo ao Pg.

In psql, type:

\dt

> Is there an SQL command for this ?

Look at the chapter from the manual here:

http://www.postgresql.org/docs/8.1/static/view-pg-tables.html

As a starter, you can do:

select * from pg_tables;

Sean

On 9/1/06 5:37 pm, "Joao Miguel Ferreira" <jmf@estg.ipvc.pt> wrote:

> Hello all,
>
> this is my first question... I'm really new to PG.
>
> How do I get a list of all tables in a database ?
>
> Is there an SQL command for this ?

If you are using psql then simply \dt

Or the SQL query that is performed by \dt is

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Cheers

Adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

am  09.01.2006, um 12:37:19 -0500 mailte Joao Miguel Ferreira folgendes:
> Hello all,
>
> this is my first question... I'm really new to PG.
>
> How do I get a list of all tables in a database ?

Depends. In psql you can type '\t' (without ') to see all tables. Hint:
read the welcome-message, when you start psql. *wink*

If you want to see the complete underlaying SQL, you should start psql
with the option '-E'. For more info: man psql *wink*


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Joao Miguel Ferreira wrote:

>Hello all,
>
>this is my first question... I'm really new to PG.
>
>How do I get a list of all tables in a database ?
>
>Is there an SQL command for this ?
>
>thx
>
>jmf
>
>
>
In the psql terminal

\dt


--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.