You are viewing an old version of this article. View the current version here.

The CLI Catalog functions are so called because they involve implicit searches of the metadata what in pre-SQL-92 days was known as the system catalog. Nowadays the metadata is in INFORMATION_SCHEMA. The functions, and the INFORMATION_SCHEMA Views which provides most of the information for them, are:

FunctionRelated INFORMATION_SCHEMA View(s)
SQLColumnPrivilegesCOLUMN_PRIVILEGES
SQLColumnsCOLUMNS
SQLForeignKeysKEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS
SQLGetTypeInfo
SQLParametersPARAMETERS
SQLPrimaryKeysKEY_COLUMN_USAGE, TABLE_CONSTRAINTS
SQLRoutinePrivilegesROUTINE_PRIVILEGES
SQLRoutinesROUTINES
SQLSpecialColumnsCOLUMNS
SQLTablePrivilegesTABLES, TABLE_PRIVILEGES
SQLTablesTABLES

You should study Catalog functions if:

  • Your problem matches the limited range of ad-hoc solutions offered here.
  • They're the standard in your shop.
  • You're maintaining an old ODBC program.
  • Your DBMS doesn't support INFORMATION_SCHEMA.
  • You want to see what extremely long SELECT statements look like.

Otherwise, study the description of the INFORMATION_SCHEMA in Chapter 16 "SQL Catalogs" and use the simple mechanisms you already know. It's cleaner to SELECT from a View in INFORMATION_SCHEMA.

Comments

Comments loading...