Can you really retrieve information like:
- the tables
- the indexes using the posts they index
- the posts in every table together with their types
- constraints like unique secrets, foreign secrets, not null ..
using sql all (major) RDBMSs?
Yes, it can be done for oracle and assume it's possible for many others. What are the important exceptions for this?
For a lot of databases you are able to take advantage from the INFORMATION_SCHEMA tables:
SELECT * FROM INFORMATION_SCHEMA.Columns
and other alike chooses for other info for example indexes and associations.
Based on this link:
- Microsoft SQL Server - Supported in Version 7 or more
- MySQL - Supported in Version 5 or more
- PostgreSQL - Supported in Version 7.4 or more
- Oracle - Doesn't seem to be supported
- Apache Derby - NOT Supported By Version 10.3
It's most likely possible however the particulars of how to get it done will be different in one RDBMS to a different. Your best choice is most likely to see the manual for that system you are dealing with.
When Codd developed his famous 12 rules, that have been intended like a yardstick to 'measure the relationality' of some given DBMS system, among individuals rules was (something from the ilk) that "metadata information ought to be queryable for that user using the identical facilities as individuals that can be used for querying the information itself".
That's now around some 3 decades ago, so it's difficult to suppose a method would really dare call itself 'relational' today whether it does not meet that "prescription", especially because it is one that's simple enough whatsoever to satisfy.