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. |
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.
The following query retrieves tables and their columns, preserving the order in which the columns appear in the tables.
SELECT TableName, ColumnName
FROM SYS_COLUMNS
ORDER BY TableName, OrdinalPosition;
This query retrieves tables along with their foreign key constraints, the corresponding delete rules, related tables, and foreign key columns.
SELECT C.TableName, C.ConstraintName, R.DeleteRule, U.ColumnName
FROM SYS_TABLE_CONSTRAINTS AS C, SYS_KEY_COLUMN_USAGE AS U, SYS_REFERENTIAL_CONSTRAINTS AS R
WHERE C.ConstraintName = R.ConstraintName AND R.ConstraintName = U.ConstraintName
ORDER BY C.TableName, C.ConstraintName;
To find tables with foreign keys referencing the Account table, use the following query.
SELECT DISTINCT C.TableName
FROM SYS_TABLE_CONSTRAINTS AS C, SYS_REFERENTIAL_CONSTRAINTS AS R
WHERE C.ConstraintName = R.ConstraintName AND R.RelatedObject = 'Account';