By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,178 Members | 987 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,178 IT Pros & Developers. It's quick & easy.

SQL Command - To List Tables ?

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

Pete

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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 Wed, 27 Aug 2003 08:37 pm, Peter Moscatt wrote:
Is there a SQL command I can issue which will list all the TABLES within
a database ?

Pete

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #2

P: n/a
On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pg*******@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;

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #3

P: n/a
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/s...reference.html
http://www.postgresql.org/docs/7.3/s...ce-client.html
http://www.postgresql.org/docs/7.3/static/app-psql.html

OR

http://www.postgresql.org/docs/7.3/s...reference.html
http://www.postgresql.org/search.cgi...ist+all+tables
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Man, I'm pretty. Hoo Hah!"
Johnny Bravo
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #4

P: n/a
> Is there a SQL command I can issue which will list all the TABLES within
a database ?


select datname from pg_database;

--
WBR, sector119
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #5

P: n/a

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 |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #6

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #7

P: n/a
Carmen Gloria Sepulveda Dedes <cs********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #8

P: n/a
On Wed, Aug 27, 2003 at 08:09:16 -0500,
Bruno Wolff III <br***@wolff.to> wrote:
On Wed, Aug 27, 2003 at 20:37:14 +1000,
Peter Moscatt <pg*******@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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #9

P: n/a
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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 11 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.