Обсуждение: SQL for listing the tables in a specific database

Greetings,
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' ;

thanks!

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

On Wed, Jan 6, 2010 at 2:55 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:
> 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;
>

Duh.  For some reason, I got it into my head that the query associated
with \dt was too generic to do what I wanted.  Thanks!