SEO

#1 (**permalink**) 04-09-2008, 07:31 AM

Hi
I am trying to figure out how to get (select) a list of all the names of
the tables in a database when given the name of the database. ie.,
Select table_name from ??? where datbase name = "XYZ";
I can easily select names of tables in current db like this:
SELECT datname FROM pg_database;

But, if I want to retrieve all table names for a different db, I can
not figure it out.
I looked at doc for pg_class and can find a relation name but no ID#.
I thought maybe I could find a database ID related to table IDs, but
can not determine what a databases classID might be or what a tables
ID might be.

What system catalogs do I need to access for this information?

Thanks
Jeff

Jeff Webb
University of Scranton

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#2 (**permalink**) 04-09-2008, 07:31 AM

On Sun, Dec 18, 2005 at 12:18:04PM -0500, webbj2@Scranton.edu wrote:
> I am trying to figure out how to get (select) a list of all the names of
> the tables in a database when given the name of the database. ie.,
> Select table_name from ??? where datbase name = "XYZ";

Except for a few shared tables each database has its own catalogs,
so to get a list of a database's tables you have to connect to that
database.

> I can easily select names of tables in current db like this:
> SELECT datname FROM pg_database;

That doesn't list tables, it lists databases.

> But, if I want to retrieve all table names for a different db, I can
> not figure it out.

Connect to the other database and query its catalogs. You could
abstract this with dblink and a view.

> I looked at doc for pg_class and can find a relation name but no ID#.
> I thought maybe I could find a database ID related to table IDs, but
> can not determine what a databases classID might be or what a tables
> ID might be.

A relation's oid is in the pg_class.oid system column (not shown
in the documentation for pg_class but described in "System Columns"
in the "Data Definition" chapter). Databases aren't shown in
pg_class.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Select list of table names for particular DB