SQL Command - To List Tables ?

On 27 Aug 2003 at 20:37, Peter Moscatt wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

\dt from psql

or select relname from pg_class;

Check the column name. There might be a typo there but pg_class is what you
are looking at.

Bye
Shridhar

--
There are certain things men must do to remain men. -- Kirk, "The Ultimate
Computer", stardate 4929.4

Is there a SQL command I can issue which will list all the TABLES within
a database ?

If you want to get database-specific attributes, such as tables, views,
etc... just try \? from the psql monitor, and you will get a list of nice
functions. '\dt' will list all tables, for example.

Thanks
--mathan

On 27/08/2003 11:37 Peter Moscatt wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

Pete

select tablename from pg_tables

HTH

-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller 
Business |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Is there a SQL command I can issue which will list all the TABLES within
a database ?

select datname from pg_database;

--
WBR, sector119

On Wed, 2003-08-27 at 05:37, Peter Moscatt wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

http://www.postgresql.org/docs/7.3/static/reference.html
http://www.postgresql.org/docs/7.3/static/reference-client.html
http://www.postgresql.org/docs/7.3/static/app-psql.html

OR

http://www.postgresql.org/docs/7.3/static/reference.html
http://www.postgresql.org/search.cgi?ul=http%3A%2F%2Fwww.postgresql.org%2Fdocs%2F7.3%2Fstatic%2F&q=list+all+tables

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo

Peter Moscatt wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

Pete

Well, when you are the postgres commandline client, you can type
# \dt

That will show all the tables in your current database.

--
Robby Russell, | Sr. Administrator / Lead Programmer
Command Prompt, Inc. | http://www.commandprompt.com
rrussell@commandprompt.com | Telephone: (503) 222.2783

On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pgmoscatt@optushome.com.au> wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

select datname from pg_database;

Hello. I type \dt as user postgres, but it show only the tables within
public schema. I need to know all tables (like v$_ or dba_ or user_
in oracle). How I do that?
Thanks.

Robby Russell wrote:

On Wednesday, August 27, 2003, at 07:57 AM, Robby Russell wrote:

Peter Moscatt wrote:

Is there a SQL command I can issue which will list all the TABLES
within
a database ?

Pete

Well, when you are the postgres commandline client, you can type
# \dt

That will show all the tables in your current database.

If you would actually like to issue a command, you can start psql with
the -E option. So it will show you all the queries it uses:
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;

Which of course you can pare down to get just the information you want.

Jeff

If you start psql with the -E option you can see the internal commands sent to
the backend. This can often give you a lot of hints as to the best way to
pull catalog data from a db:

jason=# \dt
********* QUERY **********
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;
**************************

So from this to get a list of tables you would execute the following in SQL:

select c.relname FROM pg_catalog.pg_class c
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);

This will give you tables only. If you wanted schema's and owners then you
would execute a similar variant to that thrown out by psql.

Rgds,

Jason

On Wednesday, August 27, 2003, at 09:17 AM, Carmen Gloria Sepulveda
Dedes wrote:

Hello. I type \dt as user postgres, but it show only the tables within
public schema. I need to know all tables (like v$_ or dba_ or user_
in oracle). How I do that?

Start psql with the echo flag:

[inligo:~] heath% psql -E dpe
********* QUERY **********
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
COMMIT
**************************

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

dpe=# \dt
********* QUERY **********
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;
**************************

-heath

Carmen Gloria Sepulveda Dedes <csepulveda@atichile.com> writes:

Hello. I type \dt as user postgres, but it show only the tables within
public schema.

More accurately, it shows the tables visible in your schema search path.

I need to know all tables

\dt *.*

regards, tom lane

On Wed, Aug 27, 2003 at 08:09:16 -0500,
Bruno Wolff III <bruno@wolff.to> wrote:

On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pgmoscatt@optushome.com.au> wrote:

Is there a SQL command I can issue which will list all the TABLES within
a database ?

select datname from pg_database;

This was an oops. I misread your question as asking for all databases,
not tables.

On Wed, Aug 27, 2003 at 09:17:01AM -0400, Carmen Gloria Sepulveda Dedes wrote:

Hello. I type \dt as user postgres, but it show only the tables within
public schema. I need to know all tables (like v$_ or dba_ or user_
in oracle). How I do that?

You need to change your search_path. Use

SET search_path TO 'one-schema', 'another', ...

Then repeat your \dt

Or IIRC you can also use
\dt *.*

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

On Wed, 2003-08-27 at 21:03, Heath Tanner wrote:

On Wednesday, August 27, 2003, at 09:17 AM, Carmen Gloria Sepulveda
Dedes wrote:

Hello. I type \dt as user postgres, but it show only the tables within
public schema. I need to know all tables (like v$_ or dba_ or user_
in oracle). How I do that?

Start psql with the echo flag:

[inligo:~] heath% psql -E dpe
********* QUERY **********
BEGIN; SELECT usesuper FROM pg_catalog.pg_user WHERE usename = 'heath';
COMMIT
**************************

Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

dpe=# \dt
********* QUERY **********
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;
**************************

Isn't that big CASE statement unnecessary, since you also have
c.relkind IN ('r','')

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

484,246 sq mi are needed for 6 billion people to live, 4 persons
per lot, in lots that are 60'x150'.
That is ~ California, Texas and Missouri.
Alternatively, France, Spain and The United Kingdom.

On Thu, Aug 28, 2003 at 04:31:16AM -0500, Ron Johnson wrote:

Isn't that big CASE statement unnecessary, since you also have
c.relkind IN ('r','')

Not really. In the code almost all the \d commands use that same query but
just change the "relkind in" clause.

So you can see it's redundant on a local level but the code that generates
it doesn't need to know.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/