/*
Script: WhereUsed.sql
Schema: NONE
Module: N.A.
Author: Marco Bommeljé
Date:   2000-09-22

  This script selects compiled database objects that depend on
  a given table column.

  DESCRIPTION
  Views, triggers and procedures have source code dependencies that
  are visible only through Rdb$Dependencies. This script contains a
  select statement that selects dependencies and joins in information
  to explain what depends on what.

*/

CONNECT 'c:\projects\data\employee.gdb'
  USER 'SYSDBA' PASSWORD 'masterkey';

/*  Select database objects that reference a column.
    This examples is for column 'ORDER_STATUS' in table 'SALES'
*/

SELECT t1.rdb$type_name AS Object_Type,
  d.rdb$depended_on_name AS Object_Name,
  d.rdb$field_name AS Field_Name,
  t2.rdb$type_name AS Used_In_Type,
  d.rdb$dependent_name AS Used_In_Name
FROM rdb$dependencies d
  JOIN  rdb$types t1
    ON t1.rdb$type = d.rdb$depended_on_type
    AND t1.rdb$field_name = 'RDB$OBJECT_TYPE'
  JOIN rdb$types t2
    ON t2.rdb$type = d.rdb$dependent_type
    AND t2.rdb$field_name = 'RDB$OBJECT_TYPE'
WHERE d.rdb$depended_on_name = 'SALES'
  AND d.rdb$field_name = 'ORDER_STATUS'

COMMIT;

/* end of script */