Do all RDBMS have a Data Dictionary comparable to the one Oracle has?

Question

Is it possible to retrieve information like:

• the tables
• the indexes with the columns they index
• the columns in each table along with their types
• constraints like unique keys, foreign keys, not null ..

using sql from all (major) RDBMSs?

I know it is possible for oracle and assume it is possible for most others. Are there any important exceptions to this?

Answer 1

It is probably possible but the details of how to do it will vary from one RDBMS to another. Your best bet is probably to read the manual for the system you're working with.

Answer 2

For many databases you can make use of the INFORMATION_SCHEMA tables:

SELECT *
FROM INFORMATION_SCHEMA.Columns

and similar selects for other info such as indexes and relationships.

According to this link:

• Microsoft SQL Server - Supported in Version 7 and up
• MySQL - Supported in Version 5 and up
• PostgreSQL - Supported in Version 7.4 and up
• Oracle - Does not appear to be supported
• Apache Derby - NOT Supported As of Version 10.3

Answer 3

When Codd formulated his famous 12 rules, which were intended as a yardstick to 'measure the relationality' of some given DBMS system, one of those rules was (something of the ilk) that "metadata information should be queryable for the user using exactly the same facilities as those that are used for querying the data itself".

That is now around some 30 years ago, so it is hard to imagine that a system would actually dare call itself 'relational' today if it doesn't meet that "prescription", especially as it is one that is not difficult at all to meet.