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

How to list which tables are available?

P: n/a
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

Bertwim

Nov 12 '05 #1
Share this Question
Share on Google+
16 Replies


P: n/a
On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


In psql use \d or \dt

If you start psql with the -E flag it will show you how it does that.

There are also a number of pg_xxx views that show this sort of thing
(pg_tables, pg_indexes etc). I think these are covered in an appendix of the
manuals.
--
Richard Huxton
Archonet Ltd

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

Nov 12 '05 #2

P: n/a
On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


"$ man psql", then search for the string "list of all tables"

$ psql test1
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

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Experience hath shewn, that even under the best forms [of
government] those entrusted with power have, in time, and by slow
operations, perverted it into tyranny."
Thomas Jefferson
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #3

P: n/a
B.W.H. van Beest writes:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


SELECT * FROM pg_tables;

--
Peter Eisentraut pe*****@gmx.net
---------------------------(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 12 '05 #4

P: n/a
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?

Regards,
Bertwim

B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

Bertwim


Nov 12 '05 #5

P: n/a
On Tue, Oct 14, 2003 at 09:02:13PM +0200, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).


Actually, the SQL standard _has_ a way to get this information, called the
INFORMATION_SCHEMA. Not all vendors implement it; at least, it's present
in PostgreSQL 7.4. Meanwhile you can use the pg_tables view.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #6

P: n/a
On Tuesday 14 October 2003 10:53, B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


In psql use \d or \dt

If you start psql with the -E flag it will show you how it does that.

There are also a number of pg_xxx views that show this sort of thing
(pg_tables, pg_indexes etc). I think these are covered in an appendix of the
manuals.
--
Richard Huxton
Archonet Ltd

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

Nov 12 '05 #7

P: n/a
On Tue, 2003-10-14 at 04:53, B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


"$ man psql", then search for the string "list of all tables"

$ psql test1
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

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Experience hath shewn, that even under the best forms [of
government] those entrusted with power have, in time, and by slow
operations, perverted it into tyranny."
Thomas Jefferson
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #8

P: n/a
On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?
This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.
B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.


--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made it
popular"
David Bradley, regarding Ctrl-Alt-Del
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #9

P: n/a
To get a list of the non-default tables in your database, do this:

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';

If you really want all of the tables, including the ones PostgreSQL
creates by default, just do this:

SELECT tablename FROM pg_tables;

Hope this helps,
ch**@crashed.net

"B.W.H. van Beest" <bw**@xs4all.nl> wrote in message news:<3f***********************@news.xs4all.nl>...
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

Bertwim

Nov 12 '05 #10

P: n/a
B.W.H. van Beest writes:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!


SELECT * FROM pg_tables;

--
Peter Eisentraut pe*****@gmx.net
---------------------------(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 12 '05 #11

P: n/a
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?

Regards,
Bertwim

B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

Bertwim


Nov 12 '05 #12

P: n/a
On Tue, 14 Oct 2003, Ron Johnson wrote:
On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?


This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.


Actually, as of 7.4 we implement the spec standard information_schema,
which is the spec way of doing it. It's just fairly new, so most
databases probably don't support it yet.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #13

P: n/a
On Tue, Oct 14, 2003 at 09:02:13PM +0200, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).


Actually, the SQL standard _has_ a way to get this information, called the
INFORMATION_SCHEMA. Not all vendors implement it; at least, it's present
in PostgreSQL 7.4. Meanwhile you can use the pg_tables view.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #14

P: n/a
On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?
This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.
B.W.H. van Beest wrote:
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.


--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"As I like to joke, I may have invented it, but Microsoft made it
popular"
David Bradley, regarding Ctrl-Alt-Del
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #15

P: n/a
To get a list of the non-default tables in your database, do this:

SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%';

If you really want all of the tables, including the ones PostgreSQL
creates by default, just do this:

SELECT tablename FROM pg_tables;

Hope this helps,
ch**@crashed.net

"B.W.H. van Beest" <bw**@xs4all.nl> wrote in message news:<3f***********************@news.xs4all.nl>...
It seems so elementary, but how I get a list of which tables are
available in a database. I can't find an SQL command for this, but there
must be a way!

Thanks.

Bertwim

Nov 12 '05 #16

P: n/a
On Tue, 14 Oct 2003, Ron Johnson wrote:
On Tue, 2003-10-14 at 14:02, B.W.H. van Beest wrote:
Thanks for the answers given. I am discovering the world of SQL ...

Given the nature of all replies (they all refer to something that is
PG-specific), I conclude that there is no generic way (i.e. something
that will hold for all sql-compliant systems).

Is that correct, and moreover, what could be the reason that such an
listing function is not "standard" sql?


This operation is not, to my knowledge, specified by The Standard.

Thus, it's implementation-specific.


Actually, as of 7.4 we implement the spec standard information_schema,
which is the spec way of doing it. It's just fairly new, so most
databases probably don't support it yet.
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #17

This discussion thread is closed

Replies have been disabled for this discussion.