Re: SQL for listing the tables in a specific database

On Wed, Jan 6, 2010 at 2:46 PM, Lonni J Friedman <netllama@gmail.com> wrote:

> I've been googling for a while, and I can't seem to find a solution.
> What I'd like to do is obtain a list of the tables that are part of a
> specific database instance.  Google has provided me with numerous ways
> of listing every table across all the database instances, but not a
> way to isolate the dataset to just one database.
>
> Here's what I've found thus far, which lists every table from every database:
>
> select * from pg_tables
> select table_name from information_schema.tables where table_schema = 'public' ;

Here is the query that my version of psql issues:

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",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
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 <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug