Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A2406476E39 for ; Wed, 18 Dec 2002 17:54:16 -0500 (EST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by postgresql.org (Postfix) with ESMTP id 8701A476A4E for ; Wed, 18 Dec 2002 17:53:31 -0500 (EST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.6/8.12.6) with ESMTP id gBIMrFJb024786; Wed, 18 Dec 2002 15:53:15 -0700 (MST) Date: Wed, 18 Dec 2002 15:54:55 -0700 (MST) From: "scott.marlowe" To: Mark Nelson Cc: Subject: Re: getting table names In-Reply-To: <01KQ6KLMZ9J68YARR9@cc.usu.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-MailBodyFilter: Message body has not been filtered X-MailScanner: Found to be clean X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/47 X-Sequence-Number: 1722 On Wed, 18 Dec 2002, Mark Nelson wrote: > Hi, > > I can't seem to find how to do this anywhere. I'm trying to query > the db and have it return the name of each of the tables in the > db. Something like \dt does in psql, but from PHP. Any ideas? Well, the quick and dirty trick is to run psql with the -E switch which will rEveal the sql psql is using to do it's dirty work. In this case, 7.2 and 7.3 are pretty similar. For postgresql 7.2 it's: SELECT 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_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid WHERE c.relkind IN ('r','v','S','') AND c.relname !~ '^pg_' ORDER BY 1; For 7.3 it's: 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','v','S','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2; note that you can probably trim a fair bit off of these queries to get just the table names.