*(/------------------------------------------------------------------------\
    Program Name: FindFKs.rmd -- Find Foreign Keys

          Author: Bill Downall, 317/297-3810  (Fax 317/297-2523)
                  Downall Consulting Services
                  5411 White Willow Court, Indianapolis IN 46254-9633

                  Copyright (c) 1996, Bill Downall, All Rights Reserved.

         Purpose: Find all foreign key constraints that reference
                  a particular primary key.

       Is Run by: user at R> prompt:  RUN findfks.rmd

    MODIFICATION HISTORY
             Created: 5/96

  \------------------------------------------------------------------------/)
SET QUOTE ='

-- all variables start with vffk so that clearing them
-- is unlikely to clear YOUR variables.

SET VAR vffkDBName = (CVAL('database'))     -- make sure user is connected
IF vffkDBName IS NULL THEN
  PAUSE 2 USING 'You must be connected to a database. Press any key.'
  GOTO erlyexit
ENDIF

CLS
WRITE 'FindFKs.rmd -- Find Foreign Keys '
WRITE '---------------------------------------------------------------------'
WRITE '           Copyright (c) 1996, Bill Downall'
WRITE ' '
SET VAR vffkPKTabID INTEGER
SET VAR vffkChooseVar TEXT
CHOOSE vffkChooseVar +
  FROM #VALUES FOR +
    t1.sys_table_name, t1.sys_table_id +
  FROM sys_tables t1 +
  WHERE (t1.sys_table_name NOT LIKE 'sys%') +
    AND (t1.sys_table_id IN +
    (SELECT sys_table_id FROM sys_indexes i2 +
      WHERE (i2.sys_primary_key = 1) +
        AND (i2.sys_table_id = t1.sys_table_id))) +
  ORDER BY sys_table_name +
  AT 5, CENTER +
  TITLE 'Choose a Primary Key table'

IF vffkChooseVar = '[Esc]' THEN
  GOTO Erlyexit
ENDIF

SET VAR vffkpktabid = (INT(.vffkchoosevar))
SELECT sys_table_name INTO vffkPKTabName +
  FROM sys_tables WHERE (sys_table_id = .vffkPKTabID)

CLS
WRITE 'FK''s that reference the PK in table', .vffkPKTabName, ':'
WRITE ' '
SET HEADINGS OFF

WRITE ' FK ID FK Name            FK Table           FK Column'
WRITE ' ----- ------------------ ------------------ ------------------'
SELECT fk2.sys_index_id=5, +
    fk2.sys_index_name=18, +
    ft3.sys_table_name=18, +
    fk2.sys_column_name=18 +
  FROM sys_indexes pk1, sys_indexes fk2, sys_tables ft3 +
  WHERE (pk1.sys_table_id = .vffkPKTabID) +
    AND (pk1.sys_primary_key = 1) +
    AND (fk2.sys_foreign_key = pk1.sys_index_id) +
    AND (fk2.sys_table_id = ft3.sys_table_id) +
  ORDER BY fk2.sys_index_id, fk2.sys_column_name
if sqlcode = 100 then
  WRITE 'The Primary Key in this table is NOT referenced by any Foreign Keys.'
ENDIF

SET HEADINGS ON                             -- Clean up

LABEL ErlyExit
CLEAR VAR vffk%

RETURN

