On Wed, 11 Aug 2004 11:04:01 -0500, Josh Trutwin wrote: > On Wed, 11 Aug 2004 17:20:45 +0200 Jochem van Dieten wrote: >> >> SHOW TABLES does not make sense. How are you going to join the >> output of SHOW TABLES against the output of SHOW COLUMNS and SHOW >> INDEXES? >> >> SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense. >> >> >> And as for easy remembering: I prefer to remember just one standard, >> instead of the idiosyncracies of each product. > > Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this. > The MySQL set of "SHOW" commands is pretty painful for any serious development. > > Does PostgreSQL have a set of information schema tables to query against like Oracle > does (e.g. SELECT table_name FROM user_tables)?
It does have a system that is pretty comparable to what is in the the SQL standard. There is a pg_catalog schema that contains the base tables (information about database objects in PostgreSQL is stored in normal tables). Although not recommended (to say the least), these base tables can even be updated using SQL. As a queryable front end there is a read-only information_schema schema that contains many views describing the objects in the database. It is somewhat similar to what Oracle has, but it is exactly the same as what the SQL standard has. Since MS SQL Server implements the same part of the SQL standard metadata queries are quite portable between PostgreSQL and MS SQL Server. > I noticed this from a quick google search: > > http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php > > Is something similar planned for MySQL in the future? Doesn't appear to be from: > http://dev.mysql.com/doc/mysql/en/Roadmap.html MySQL strives to ful SQL standard compliance (ISO/IEC 9075), so an INFORMATION_SCHEMA must be planned for someday. However, that day does not appear to be in the near future. I wonder how far one would be able to mimic one once MySQL 5.1 is out by creating a database named INFORMATION_SCHEMA, which contains views which call all the SHOW commands. That would probably need some serious work to make the SHOW commands joinable, but the naming convention might work. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
