Querying metadata

You can query Oracle data dictionary views to retrieve metadata about objects. These views can be queried directly, like any other table, to return information about tables, columns, and constraints.

Sample queries

List tables and columns in column order

Returns each table and its columns in the order they appear. Includes the owner to avoid cross-schema ambiguity.

SELECT TABLE_NAME, COLUMN_NAME 
  FROM ALL_TAB_COLUMNS 
  ORDER BY TABLE_NAME, COLUMN_ID;

List foreign keys with parent table, delete rule, and columns

Shows each foreign key, its delete rule, the parent (referenced) table, and the foreign-key columns in the correct column order.

SELECT C.TABLE_NAME,
       C.CONSTRAINT_NAME,
       R.DELETE_RULE,
       U.COLUMN_NAME
  FROM ALL_CONSTRAINTS C
  JOIN ALL_CONS_COLUMNS U ON C.CONSTRAINT_NAME = U.CONSTRAINT_NAME
  JOIN ALL_CONSTRAINTS R ON C.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME
  WHERE C.CONSTRAINT_TYPE = 'R'
  ORDER BY C.TABLE_NAME, C.CONSTRAINT_NAME;

List tables with foreign keys referencing ACCOUNTS

Returns tables that reference a specific parent table. Add the parent owner if known.

SELECT DISTINCT C.TABLE_NAME
FROM ALL_CONSTRAINTS C
WHERE C.CONSTRAINT_TYPE = 'R'
  AND C.R_CONSTRAINT_NAME IN (
        SELECT CONSTRAINT_NAME
        FROM ALL_CONSTRAINTS
        WHERE TABLE_NAME = 'ACCOUNTS'
      );