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
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'
ORDER BY 1,2;
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
In response to
pgsql-novice by date
|Next:||From: Lonni J Friedman||Date: 2010-01-06 23:04:20|
|Subject: Re: SQL for listing the tables in a specific database|
|Previous:||From: John J. Urbaniak||Date: 2010-01-06 22:50:54|
|Subject: Re: Error on Vacuum?|