pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: list of tables ?
9+ messages / 8 participants
[nested] [flat]
* Re: list of tables ?
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: Steve Brett <SBrett@e-mis.com>
2 siblings, 0 replies; 9+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: SBrett@e-mis.com; +Cc: pgsql-sql
>
> can anyone point me in the right direction ?
>
> i need to list all the tables in a database.
>
Steve,
Your request reminds me of a similar problem I had.
Try the following:
CREATE VIEW sesql_usertables AS
SELECT
UPPER(u.usename) AS tbl_owner, UPPER(c.relname) AS tbl_name,
UPPER(a.attname) AS col_name, a.atttypid AS col_type,
INT4LARGER(a.attlen, a.atttypmod - 4) AS col_length,
CASE WHEN a.attnotnull=TRUE THEN 0 ELSE 1 END AS col_null, a.attnum AS col_seq,
CASE WHEN EXISTS(SELECT adsrc FROM pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum) THEN
1
ELSE
0
END AS COL_DEFAULT
FROM pg_attribute a,
pg_class c LEFT JOIN pg_user u ON (u.usesysid = c.relowner)
WHERE c.oid = a.attrelid AND NOT (c.relname ~* 'pg_') AND
c.relkind = 'r' AND a.attnum > 0 ;
SELECT * FROM sesql_usertables ORDER BY tbl_owner, tbl_name, col_seq ;
It should give at least some ideas how to retrieve information
from all the tables in a database.
Regards, Christoph
^ permalink raw reply [nested|flat] 9+ messages in thread
* list of tables?
@ 1998-05-21 02:03 The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 9+ messages in thread
From: The Hermit Hacker @ 1998-05-21 02:03 UTC (permalink / raw)
To: pgsql-sql; +Cc: Rosauro, Guillaume <Guillaume.Rosauro@matra-com.fr>
Hello
I d like to know if it is possible to have the list of tables like we can do with psql with \dt
thanks
Guillaume
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] list of tables?
@ 1998-05-21 09:26 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: The Hermit Hacker <scrappy@hub.org>
0 siblings, 1 reply; 9+ messages in thread
From: Jose' Soares Da Silva @ 1998-05-21 09:26 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql@postgreSQL.org, "Rosauro, Guillaume" <Guillaume.Rosauro@matra-com.fr>
On Wed, 20 May 1998, The Hermit Hacker wrote:
>
> Hello
>
> I d like to know if it is possible to have the list of tables like we can do with psql with \dt
>
> thanks
>
-- list all and only tables/views...
SELECT usename, relname
FROM pg_class, pg_user
WHERE ( relkind = 'r')
AND relname !~ '^pg_'
AND relname !~ '^xin[vx][0-9]+'
AND usesysid = relowner
ORDER BY relname;
Jose'
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] list of tables?
@ 1998-05-21 09:30 Oleg Broytmann <phd@comus.ru>
parent: Jose' Soares Da Silva <sferac@bo.nettuno.it>
0 siblings, 1 reply; 9+ messages in thread
From: Oleg Broytmann @ 1998-05-21 09:30 UTC (permalink / raw)
To: Jose' Soares Da Silva <sferac@bo.nettuno.it>; +Cc: pgsql-sql
Hello!
I see this SELECT every other day in mailbox. Isn't it a FAQ?
On Thu, 21 May 1998, Jose' Soares Da Silva wrote:
> -- list all and only tables/views...
> SELECT usename, relname
> FROM pg_class, pg_user
> WHERE ( relkind = 'r')
> AND relname !~ '^pg_'
> AND relname !~ '^xin[vx][0-9]+'
> AND usesysid = relowner
> ORDER BY relname;
Oleg.
----
Oleg Broytmann http://members.tripod.com/~phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [SQL] list of tables?
@ 1998-05-22 02:44 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Oleg Broytmann <phd@comus.ru>
0 siblings, 0 replies; 9+ messages in thread
From: Bruce Momjian @ 1998-05-22 02:44 UTC (permalink / raw)
To: phd2@earthling.net; +Cc: sferac@bo.nettuno.it; pgsql-sql
>
> Hello!
>
> I see this SELECT every other day in mailbox. Isn't it a FAQ?
Added to FAQ. See the web site for the most recent version.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 9+ messages in thread
* list of tables ?
@ 2002-07-11 14:00 Steve Brett <SBrett@e-mis.com>
0 siblings, 3 replies; 9+ messages in thread
From: Steve Brett @ 2002-07-11 14:00 UTC (permalink / raw)
To: pgsql-sql
can anyone point me in the right direction ?
i need to list all the tables in a database.
i've looked at pgadmin_tables which is empty and pga_schema whihc contains a
sinlge row i don't want to parse ...
is there an easier way t get a list of tables ?
i'm on 7.2
ta,
Steve Brett
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: list of tables ?
@ 2002-07-11 14:09 Ian Barwick <barwick@gmx.net>
parent: Steve Brett <SBrett@e-mis.com>
2 siblings, 0 replies; 9+ messages in thread
From: Ian Barwick @ 2002-07-11 14:09 UTC (permalink / raw)
To: Steve Brett <SBrett@e-mis.com>; pgsql-sql
On Thursday 11 July 2002 16:00, Steve Brett wrote:
> can anyone point me in the right direction ?
>
> i need to list all the tables in a database.
>
> i've looked at pgadmin_tables which is empty and pga_schema whihc contains
> a sinlge row i don't want to parse ...
>
> is there an easier way t get a list of tables ?
Start psql with the -E option and issue \dt
This gives you the statement(s) used internally by psql.
Ian Barwick
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: list of tables ?
@ 2002-07-11 14:09 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Steve Brett <SBrett@e-mis.com>
2 siblings, 0 replies; 9+ messages in thread
From: Achilleus Mantzios @ 2002-07-11 14:09 UTC (permalink / raw)
To: Steve Brett <SBrett@e-mis.com>; +Cc: pgsql-sql
On Thu, 11 Jul 2002, Steve Brett wrote:
> can anyone point me in the right direction ?
>
> i need to list all the tables in a database.
>
> i've looked at pgadmin_tables which is empty and pga_schema whihc contains a
> sinlge row i don't want to parse ...
>
> is there an easier way t get a list of tables ?
>
> i'm on 7.2
select * from pg_tables;
>
> ta,
>
> Steve Brett
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: list of tables ?
@ 2002-07-11 14:13 Steve Brett <SBrett@e-mis.com>
0 siblings, 0 replies; 9+ messages in thread
From: Steve Brett @ 2002-07-11 14:13 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Steve Brett <SBrett@e-mis.com>; +Cc: pgsql-sql
thanks.
Steve
> -----Original Message-----
> From: Achilleus Mantzios [mailto:achill@matrix.gatewaynet.com]
> Sent: 11 July 2002 15:10
> To: Steve Brett
> Cc: Pgsql-Sql (E-mail)
> Subject: Re: [SQL] list of tables ?
>
>
> On Thu, 11 Jul 2002, Steve Brett wrote:
>
> > can anyone point me in the right direction ?
> >
> > i need to list all the tables in a database.
> >
> > i've looked at pgadmin_tables which is empty and pga_schema
> whihc contains a
> > sinlge row i don't want to parse ...
> >
> > is there an easier way t get a list of tables ?
> >
> > i'm on 7.2
>
> select * from pg_tables;
>
> >
> > ta,
> >
> > Steve Brett
> >
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
> Achilleus Mantzios
> S/W Engineer
> IT dept
> Dynacom Tankers Mngmt
> tel: +30-10-8981112
> fax: +30-10-8981877
> email: achill@matrix.gatewaynet.com
> mantzios@softlab.ece.ntua.gr
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2002-07-11 14:13 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-21 02:03 list of tables? The Hermit Hacker <scrappy@hub.org>
1998-05-21 09:26 ` Re: [SQL] list of tables? Jose' Soares Da Silva <sferac@bo.nettuno.it>
1998-05-21 09:30 ` Re: [SQL] list of tables? Oleg Broytmann <phd@comus.ru>
1998-05-22 02:44 ` Re: [SQL] list of tables? Bruce Momjian <maillist@candle.pha.pa.us>
2002-07-11 14:00 list of tables ? Steve Brett <SBrett@e-mis.com>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2002-07-11 14:09 ` Ian Barwick <barwick@gmx.net>
2002-07-11 14:09 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2002-07-11 14:13 Re: list of tables ? Steve Brett <SBrett@e-mis.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox