Thread: How to list which tables are available?
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
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
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. ron.l.johnson@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
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 peter_e@gmx.net
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 >
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)
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. ron.l.johnson@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
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.
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, chet@crashed.net "B.W.H. van Beest" <bwvb@xs4all.nl> wrote in message news:<3f8bc79c$0$58708$e4fe514c@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