Thread: A few more JDBC meta-data questions...
Peter, Thanks for your incisive replies to my earlier query.. I still have two questions. (I wouldn't ask them if I was able to find the answers elsewhere, but I haven't found them in the JDBC 1.2 or 2.0 specs, or the 2 books in my possession which give some coverage to JDBC...) >From JDBC, 1. Is it possible to programatically enumerate or discover the databases in a running database?? 2. Is it possible to programmaticaly enumerate or discover the tables in a database?? For the latter, I believe that I could use regular queries against postgresq's system catalogs, but I am primarily interested in portable approaches - generic SQL and JDBC which stand a good chance of (eventually) working with other databases. Thanks, Mark Dzmura -- )))) This email routed via a wireless gateway!! ((((
Thus spake Mark Dzmura > 1. Is it possible to programatically enumerate or discover the databases in a running database?? > > 2. Is it possible to programmaticaly enumerate or discover the tables in a database?? -- get list of databases SELECT datname FROM pg_database; -- get list of tables including views but not system tables SELECT relname FROM pg_class WHERE relkind = 'r' AND relname !~ '^Inv' AND relname!~ '^pg_'; And for completeness; -- get list of attributes and types for a specific table SELECT pg_attribute.attname, pg_type.typname FROM pg_class, pg_attribute, pg_type WHERE pg_class.relname = 'tablename'AND pg_attribute.attnum > 0 AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.atttypid= pg_type.oid; -- get list of tables and primary keys (assumes no complex keys) -- only includes tables with primary keys so no views SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid =pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't'; -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.