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 Owner, TableName, ColumnName 
  FROM AllTabColumns 
  ORDER BY Owner, TableName, ColumnId

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.TableName,
       C.ConstraintName,
       R.DeleteRule,
       U.ColumnName
FROM ALL_CONSTRAINTS C
JOIN ALL_CONS_COLUMNS U ON C.ConstraintName = U.ConstraintName
JOIN ALL_CONSTRAINTS R ON C.RConstraintName = R.ConstraintName
WHERE C.ConstraintType = 'R'
ORDER BY C.TableName, C.ConstraintName;

List tables with foreign keys referencing ACCOUNTS

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

SELECT DISTINCT C.TableName
FROM ALL_CONSTRAINTS C
WHERE C.ConstraintType = 'R'
  AND C.RConstraintName IN (
        SELECT ConstraintName
        FROM ALL_CONSTRAINTS
        WHERE TableName = 'ACCOUNTS'
      );