The SQL2 standard does not directly specify the structure of a system catalog that must be supported by DBMS implementations. In practice, given the widely differing features supported by different DBMS brands and the major differences in the system catalogs that were already being used by commercial SQL products when the SQL2 standard was adopted, it would have been impossible to reach an agreement on a standard catalog definition. Instead, the writers of the SQL2 standard defined an idealized system catalog that a DBMS vendor might design if he or she were building a DBMS to support the SQL2 standard from scratch. The tables in this idealized system catalog (called the definition schema in the standard) are summarized in Table 16-13.
The SQL2 standard does not require a DBMS to actually support the system catalog tables in Table 16-13, or any system catalog at all. Instead, it defines a series of views on these catalog tables that identify database objects that are accessible to the current user. (These catalog views are called an Information Schema in the standard.) Any DBMS that claims the Intermediate or Full conformance level to the SQL2 standard must support these views. This effectively gives a user a standardized way to find out about the objects in the database that are available to him or her by issuing standard SQL against the catalog views. Note that support for the catalog views is not required for the Entry conformance level to the SQL2 standard.
In practice, major commercial SQL implementations have been slowly moving to support the SQL2 Information Schema, typically by defining corresponding views on the tables in their own system catalogs. In most cases, the information in the DBMS’ own system catalogs is similar enough to that required by the standard that the first 90 percent of the conformance to the SQL2 standard is relatively easy. The last 10 percent has proven to be much more difficult, given the variations among DBMS brands and the degree to which even the SQL2 catalog views expose the specific features and capabilities of the underlying DBMS.
As a result, full support for the SQL2 catalog views has usually been implemented in conjunction with a major new version of a DBMS product, accompanied by underlying changes in the core of the DBMS software. The catalog views required by the SQL2 standard are summarized in Table 16-14, along with a brief description of the information contained in each view. Here are some sample queries that can be used to extract information about database structure from the SQL2-defined system catalog views:
List the names of all tables and views owned by the current user.
SELECT TABLE_NAME
FROM TABLES
List the name, position, and data type of all columns in all views.
SELECT TABLE_NAME, C.COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
FROM COLUMNS
WHERE (COLUMNS.TABLE_NAME IN (SELECT TABLE_NAME FROM VIEWS))
Determine how many columns are in the table named OFFICES.
SELECT COUNT(*)
FROM COLUMNS
WHERE (TABLE_NAME = ‘OFFICES’)
The standard also defines three domains that are used by the catalog views and are also available to users. These domains are summarized in Table 16-15. Appendix E contains a complete summary of the major SQL2 catalog views and their contents.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.