Re: Getting Table Names in a Particular Database

pdc_uima=# select table_name from information_schema.tables where
table_schema='pdc_uima';
table_name
------------
(0 rows)

But filtering on 'public', it gives the result , :

pdc_uima=# select * from information_schema.tables where
table_schema='public';
table_catalog | table_schema | table_name | table_type |
self_referencing_column_name | reference_generation |
user_defined_type_catalog | user_define
d_type_schema | user_defined_type_name | is_insertable_into | is_typed |
commit_action
---------------+--------------+------------------+------------+------------------------------+----------------------+---------------------------+------------
--------------+------------------------+--------------------+----------+---------------
pdc_uima | public | spatial_ref_sys | BASE TABLE
| |
| |
| | YES | NO |
pdc_uima | public | geometry_columns | BASE TABLE
| |
| |
| | YES | NO |
pdc_uima | public | adarsh | BASE TABLE
| |
| |
| | YES | NO |
(3 rows)

Come back to the original problem. I have 10 databases with different
names you have to go into the database by \c command to fetch the table
names.

Thanks

Scott Marlowe wrote:
> On Tue, Aug 30, 2011 at 11:50 PM, Adarsh Sharma
> <adarsh(dot)sharma(at)orkash(dot)com> wrote:
>
>> I understand, So there is no way to fetch table in a single query. The only
>> way is :
>>
>> 1. Connect demo
>> 2. Execute the query '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','v','S','') AND n.nspname <> 'pg_catalog' AND
>> n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast'
>> ORDER BY 1,2;
>>
>> As in Mysql we can view all tables in a test database from below command :
>>
>> select table_name from information_schema.tables where table_schema
>> ='test';;
>>
>
> Have you tried it in pgsql, cause that works too.
>