Skip site navigation (1) Skip section navigation (2)

Re: SQL for listing the tables in a specific database

From: Richard Broersma <richard(dot)broersma(at)gmail(dot)com>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: SQL for listing the tables in a specific database
Date: 2010-01-06 22:55:12
Message-ID: 396486431001061455i2df3bf65p134d43412155ecfd@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Jan 6, 2010 at 2:46 PM, Lonni J Friedman <netllama(at)gmail(dot)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

In response to

Responses

pgsql-novice by date

Next:From: Lonni J FriedmanDate: 2010-01-06 23:04:20
Subject: Re: SQL for listing the tables in a specific database
Previous:From: John J. UrbaniakDate: 2010-01-06 22:50:54
Subject: Re: Error on Vacuum?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group