4

I have a table name student and like 35 schemas in my DB. how can i get in which schema the table student exists? (there might be more than one in different schemas).

I've tried through pg_class but I don't know how to get schema name from there.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
John
  • 1,724
  • 6
  • 25
  • 48
  • http://www.postgresql.org/docs/9.4/static/catalog-pg-class.html The `relnamespace` field points to `pg_namespace.oid`, which contains the schemas – wildplasser Jun 03 '15 at 13:46

2 Answers2

10

You could query it from information_schema.tables:

SELECT table_catalog, table_schema 
FROM   information_schema.tables 
WHERE  table_name = 'student'
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • what table_catalog means? will it work on views as well? – John Jun 03 '15 at 13:50
  • 1
    @John `table_catalog` is the name of the database containing the table. If you already know it, you can omit it from the query. And yes, this will work for views as well. – Mureinik Jun 03 '15 at 14:18
2
select schemaname,relname from pg_stat_user_tables;

Example:

dvdrental=# select schemaname,relname from pg_stat_user_tables;
schemaname relname
public actor
public category
public payment
public film
public staff
Syscall
  • 19,327
  • 10
  • 37
  • 52
Kondala vinod raja
  • 51
  • 3