All of the major SQL products have a system table that keeps track of the columns in the database. There is one row in this table for each column in each table or view in the database. Most DBMS brands restrict access to this base system table, but provide user column information through a view that shows only columns in tables owned by, or accessible to, the current user. In an Oracle database, two system catalog views provide this information—USER_TAB_COLUMNS, which includes one row for each column in each table owned by the current user, and ALL_TAB_COLUMNS, which contains one row for each column in each table accessible to the current user.
Most of the information in the system columns table or view stores the definition of a column—its name, its data type, its length, whether it can take NULL values, and so on. In addition, the table sometimes includes information about the distribution of data values found in each column. This statistical information helps the DBMS decide how to carry out a query in the optimal way.
Here is a typical query you could use to find out about the columns in an Oracle database:
List the names and data types of the columns in my OFFICES table.
SELECT COLUMN_NAME, DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = ‘OFFICES’
Like the table information in the system catalog, the column information varies across DBMS brands. Table 16-4 shows the contents of the SYSCAT.COLUMNS system table, which contains column information in the DB2 catalog. Here are some queries that apply to this DBMS brand:
Find all columns in the database with a DATE data type.
SELECT TABSCHEMA, TABNAME, COLNAME
FROM SYSCAT.COLUMNS
WHERE TYPESCHEMA = ‘SYSIBMD’ AND TYPENAME = ‘DATE’
List the owner, view name, column name, data type, and length for all text columns longer than ten characters defined in views.
SELECT DEFINER, COLS.TABNAME, COLNAME, TYPENAME, LENGTH
FROM SYSCAT.COLUMNS COLS, SYSCAT.TABLES TBLS
WHERE TBLS.TABSCHEMA = COLS.TABSCHEMA
AND TBLS.TBLNAME = COLS.TBLNAME
AND (TYPENAME = ‘VARCHAR’ OR TYPENAME = ‘CHARACTER’)
AND LENGTH > 10
AND TYPE = ‘V’
There is considerable variation in the way that the column definition is provided by the system catalogs of various DBMS brands. For comparison, Table 16-5 shows the definition of the Informix Universal Server SYSCOLUMNS table. Some of the differences between the column information in the tables are simply matters of style:
- The names of the columns in the two tables are completely different, even when they contain similar data.
- The DB2 catalog uses a combination of the schema name and table name to identify the table containing a given column; the Informix catalog uses an internal table-id number, which is a foreign key to its SYSTABLES
- The DB2 specifies data types in text form (for example, CHARACTER); the Informix catalog uses integer data type codes.
Other differences reflect the different capabilities provided by the two DBMS brands:
- DB2 allows you to specify up to 254 characters of remarks about each column; Informix does not provide this feature.
- The Informix system table keeps track of the minimum and maximum length of actual data values stored in a variable-length column; this information is not available directly from the DB2 system catalog.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.