INFORMATION_SCHEMA is a set of metadata views that provide information about the structure of a database, including available tables, columns, constraints, and relationships. The views follow the SQL-92 INFORMATION_SCHEMA standard.
Applications that connect to Dynamics 365 through the ODBC driver typically query the information schema to retrieve metadata. You can also query these tables directly, the same way you query Dynamics 365 objects.
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';