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
>> 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
> I noticed this from a quick google search:
> Is something similar planned for MySQL in the future? Doesn't appear to be from:
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.