Information schema

The information schema is a standardized set of system tables that provide metadata about the structure of a database, including available tables, columns, constraints, and relationships. The tables in the information schema follow the SQL-92 information schema standard.

The following table describes the information schema tables.

Table	Description
SYS_TABLES	Provides information about available tables and their parameters.
SYS_COLUMNS	Provides information about column names, data types, positions, and the tables to which they belong.
SYS_TABLE_CONSTRAINTS	Provides information about table constraints, including the constraint type and tables to which they belong.
SYS_REFERENTIAL_CONSTRAINTS	Provides information about referential (foreign key) constraints between tables.
SYS_KEY_COLUMN_USAGE	Provides metadata that maps columns to key constraints.

Querying metadata

Queries to the information schema tables are typically performed by applications that connect to Salesforce using the ODBC driver. However, you can also query these tables directly, just as you would query Salesforce objects to retrieve metadata.

Warning

Do not include both metadata tables and regular objects in the same query—this will result in an exception.

Sample queries

The following query retrieves tables and their columns, preserving the order in which the columns appear in the tables.

SELECT TABLE_NAME, COLUMN_NAME FROM SYS_COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION;

This query retrieves tables along with their foreign key constraints, the corresponding delete rules, related tables, and foreign key columns.

SELECT C.TABLE_NAME, C.CONSTRAINT_NAME, R.DELETE_RULE, U.COLUMN_NAME
  FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U, SYS_REFERENTIAL_CONSTRAINTS AS R 
  WHERE C.CONSTRAINT_NAME = R.CONSTRAINT_NAME AND R.CONSTRAINT_NAME = U.CONSTRAINT_NAME
  ORDER BY C.TABLE_NAME, C.CONSTRAINT_NAME;

To find tables with foreign keys referencing the Account table, use the following query.

SELECT DISTINCT C.TABLE_NAME
  FROM SYS_TABLE_CONSTRAINTS AS C, SYS_REFERENTIAL_CONSTRAINTS AS R 
  WHERE C.CONSTRAINT_NAME = R.CONSTRAINT_NAME AND R.RELATED_OBJECT = 'Account';