Обсуждение: Getting Table Names in a Particular Database

Dear all,

Today I am researching about fetching all the table names in a particular database.
There is \dt command but I need to fetch it from metadata.
I find some commands as below :

1. SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

2. SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND tablename NOT LIKE ‘sql%’.

But I need to specify a particular database & then fetch tables in that.


Thanks

On Tue, Aug 30, 2011 at 11:26 PM, Adarsh Sharma
<adarsh.sharma@orkash.com> wrote:
> Dear all,
>
> Today I am researching about fetching all the table names in a particular
> database.
> There is \dt command but I need to fetch it from metadata.
> I find some commands as below :
>
> 1. SELECT table_name FROM information_schema.tables WHERE table_schema =
> 'public';
>
> 2. SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND
> tablename NOT LIKE ‘sql%’.
>
> But I need to specify a particular database & then fetch tables in that.

Try this, start psql with the -E switch, then run \d and copy and edit
the query(s) that gives you.

On Tue, Aug 30, 2011 at 11:30 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> But I need to specify a particular database & then fetch tables in that.
>
> Try this, start psql with the -E switch, then run \d and copy and edit
> the query(s) that gives you.

P.s. I think you have to connect to the database you want to pull info
from / about.

Below is the output of the \d command

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'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

I want to specify the database name & fetch tables from that but I think this query filters from schema 'public';


Thanks

Scott Marlowe wrote:

On Tue, Aug 30, 2011 at 11:26 PM, Adarsh Sharma
<adarsh.sharma@orkash.com> wrote: 

Dear all,

Today I am researching about fetching all the table names in a particular
database.
There is \dt command but I need to fetch it from metadata.
I find some commands as below :

1. SELECT table_name FROM information_schema.tables WHERE table_schema =
'public';

2. SELECT tablename FROM pg_tables WHERE tablename NOT LIKE ‘pg%’ AND
tablename NOT LIKE ‘sql%’.

But I need to specify a particular database & then fetch tables in that.   

Try this, start psql with the -E switch, then run \d and copy and edit
the query(s) that gives you. 

On Tue, Aug 30, 2011 at 11:38 PM, Adarsh Sharma
<adarsh.sharma@orkash.com> wrote:
> Below is the output of the \d command
>
> 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'
>   AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1,2;
>
> I want to specify the database name & fetch tables from that but I think
> this query filters from schema 'public';

You HAVE to connect to the db you want to query about tables.  They
are isolated from each other.

On Tue, Aug 30, 2011 at 11:42 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Aug 30, 2011 at 11:38 PM, Adarsh Sharma
> <adarsh.sharma@orkash.com> wrote:
>> Below is the output of the \d command
>>
>> 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'
>>   AND pg_catalog.pg_table_is_visible(c.oid)
>> ORDER BY 1,2;
>>
>> I want to specify the database name & fetch tables from that but I think
>> this query filters from schema 'public';
>
> You HAVE to connect to the db you want to query about tables.  They
> are isolated from each other.

Also take out the AND pg_catalog.pg_table_is_visible(c.oid) bit if you
want to view all tables in all schemas, not just the ones in your
search path.

On 08/30/11 10:26 PM, Adarsh Sharma wrote:
> Dear all,
>
> Today I am researching about fetching all the table names in a
> particular database.
> There is \dt command but I need to fetch it from metadata.
> I find some commands as below :
>
> |1. SELECT table_name FROM information_schema.tables WHERE
> table_schema = 'public';|

That should only return tables in the database you're currently
connected to...

assuming you have multiple schemas in your database, I'd use something
like...

select table_schema || '.' || table_name
     from information_schema.tables
     where table_schema not in ('pg_catalog', 'information_schema')
         and table_type = 'BASE TABLE';




--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

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


Thanks

Scott Marlowe wrote:

On Tue, Aug 30, 2011 at 11:42 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: 

On Tue, Aug 30, 2011 at 11:38 PM, Adarsh Sharma
<adarsh.sharma@orkash.com> wrote:   

Below is the output of the \d command

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'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

I want to specify the database name & fetch tables from that but I think
this query filters from schema 'public';     

You HAVE to connect to the db you want to query about tables.  They
are isolated from each other.   

Also take out the AND pg_catalog.pg_table_is_visible(c.oid) bit if you
want to view all tables in all schemas, not just the ones in your
search path. 

On Tue, Aug 30, 2011 at 11:50 PM, Adarsh Sharma
<adarsh.sharma@orkash.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.

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@orkash.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. 

On Wed, Aug 31, 2011 at 12:10 AM, Adarsh Sharma
<adarsh.sharma@orkash.com> wrote:
> 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.

Again, in PostgreSQL databases are very separate objects.  In mysql
they are closer to schemas than separate entities.  If you want to
examine a database in pg, you need to connect to it.  period.