ContactSitemapProduktenInfoWie zijn wijWelkomStartDownload


Interbase Script - Where used

Marco Bommeljé 22 aug 2000
[download] [email]

As a DBA, I have spent much time reverse-designing databases. Often, databases are created with a case-tool generated script that is not considered worth saving. Then, when changes are needed, you have to carefully consider the impact of the changes. Fortunately, the Interbase catalog tables are really easy to "read" and query. Analysing dependencies among database objects is straightforward in most cases.
The not-so-easy-to-trace dependencies are those that are "hidden" in source code. Database objects such as triggers, procedures and views are defined in source code that gets compiled. The dependencies in the compiled code are stored in a separate catalog table: RDB$DEPENDENCIES. Some additional JOINs make this table easier to interpret.
 

/*
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 */


(C) - Last updated: