Thread: How to get table/DB info

This is a follow up to my previous question about this
subject.

I found out there are existing tables that hold all of
the info about a database. All of them begin with
'pg_'
(ie 'pg_tables'). You can use all of these tables in
combination to get the data you need about your
database. I have come up with some basic queries to
start getting some info. When I get done with all of
the queries to get the info equivilant to typing '\d
*' in psql I will publish them to this mailing list.
In the meantime, here is a simple query that shows how
to get all the table names and owners:
SELECT * FROM pg_tables WHERE tablename !~ '^pg_';

Hope it helps,
Jeff Davis

PS: you can get some help also by looking at the psql
source code, that is how I found out.
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com

On Sat, Mar 04, 2000 at 05:47:22PM -0800, Jeff Davis wrote:
> This is a follow up to my previous question about this
> subject.
>

Jeff -
Sorry no-one replied to your earlier messsage: have you looked
at the -E switch to psql? It'll echo all the queries sent to the
backend, so to find out how \d tablename works, just do:

psql -E test
...

test=> \d no_such_table
QUERY: SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod,
a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = 'no_such_table'    and a.attnum > 0     and a.attrelid =
c.oid     and a.atttypid = t.oid   ORDER BY attnum

Couldn't find table no_such_table!
test=>

And so on. You might want to use 'transcript' to grab a copy of your
entire session (I think that'll work) and do a bunch of \d commands that
you're interested in.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005