Database modeling!

[pthurmond]

Hello, I am trying to do some database modeling of a Postgres db. I would like to create a script that can scour a given database and list all of the tables and their respective columns. In MySQL they have a "SHOW TABLES" command, but I have yet to find such a thing for Postgres.

 

In the long run I would like to be able to visually model a db by running a php script. It may require using some other language with PHP to render it properly on the page. The visual model would include a visual representation of relations between tables. I have seen software for this but I can't afford to buy any and I am running on Mac OS X, so most applications for this won't work for my machine anyways.

 

I can always try to take a dump file of the db, but I would prefer a way to dynamically update the model on the fly to represent the current db.

 

Any ideas, suggestions, scripts I haven't found yet?

 

Thanks,

Patrick

[btherl]

There's a bunch of system catalogs which provide this.  These change a little with each major version of postgres.  You can find a list here

 

Of particular interest:

SELECT * FROM pg_tables WHERE schemaname = 'public';
SELECT * FROM pg_database;
SELECT * FROM pg_indexes WHERE schemaname = 'public';
SELECT * FROM pg_views WHERE schemaname = 'public';

 

There's also the pg_class table, which is global table containing just about everything.  Those tables I listed above are actually views into pg_class joined with some auxiliary tables.

[pthurmond]

Fantastic, that is exactly the kind of thing I am looking for. Eventually I will have to find a way to do this for other databases too because I want to build a class that can model multiple types of SQL databases. 

 

Currently I am working with a Postgres db, so I will write it for that db first. The hard part for me will be graphically rendering it and adding the ability for users to customize it for testing theoretical changes to the db. Looks like I am going to learn AJAX and probably a few other technologies.

 

Thanks for the strong start.

[Oldiesmann]

You might also find the Information Schema helpful