Re: Followup / proposed action

From: Benjamin Gufler <benjamin.gufler-at-in.tum.de>
Date: Fri, 09 Mar 2007 15:16:54 +0100

On 2007-03-09 14:09, Doug Tody wrote:
> Speaking of standards to consider in this area, we also have [...]
> SQL92, which defines a general and widely implemented
> standard for describing tables. It would be good to look also at the
> latter, to see what kind of metadata has been defined.

SQL92 introduces the so-called INFORMATION_SCHEMA, consisting of a pre-defined set of views with standardized names describing the catalogue (= database).
For us, the two most important ones will probably be TABLES, containing a list of all tables in the catalogue, and COLUMNS, describing all of each table's attributes. The data type of the attribute as well as scaling and precision information has to be part of the COLUMNS view according to the standard.
However, not all of the DBMSs support all of that. I have looked at some of the most used systems and found

* Postgres 7.4 and 8.2
* MySQL as of 5.0 and
* MS SQL Server 2005

to support the INFORMATION_SCHEMA,
* Oracle 10 and
* IBM DB2 V8 and V9
to provide access to similar metadata, but in schemata or views with non-standardcompliant names (the columns view, for example, is syscat.columns in DB2 and ALL_TAB_COLUMNS in Oracle), and * MySQL in versions before 5.0
to not have a metadata schema at all (at least I haven't found one that was accessible via SQL).

Benjamin Received on 2007-03-09Z15:17:06