Re: Getting Table Names in a Particular Database

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

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.sharma@xxxxxxxxxx> 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.