rem Program: d2fkchk.sql

rem Author : David Wendelken

rem        : Tactics, Inc.

rem Purpose: To Report Violations of Intended Foreign Key Constraints

rem        : by showing all rows in the actual data that would violate

rem        : the intended constraint.  Intended constraints are stored

rem        : in the Designer 2000 repository.

rem Genesis: 09/07/95 for Dan Kelley and team at the US Forest Service 



rem Notes  : This utility is intended to assist developers who are reverse

rem        : engineering an application where Oracle constraints had not

rem        : been implemented.  It is assumed that the data tables have been

rem        : reverse engineered into the Designer 2000 repository and that

rem        : the analysts have made their "best educated guess" as to what

rem        : the foreign key is composed of.  This will build a SQL script

rem        : that will report all rows in each selected table that would

rem        : violate the constraint should an attempt be made to enable it.

rem        :

rem        : It is assumed that any row that shows up from running that 

rem        : script is an indication that either those data rows are

rem        : invalid or the foreign key definition is invalid.

rem        : The analysts will have to determine which is the case.



rem Coding : The first four columns are 'tricks' used to order the 

rem        : output.  Each line of output represents one line in the

rem        : SQL output file.  It is important to keep the lines of the

rem        : SQL statements that we are creating in their proper order.

rem        : The construct '' inside a pair of quotes represents a single

rem        : quote mark ' in the output file.  Thus, '''xx''' would create

rem        : 'xx' in the output file.



rem Problem: This code assumes that the ordering sequence of the columns

rem        : starts with 1 and is incremented by 1 with each new column.



prompt Enter the Application System Name

DEFINE P_APPLICATION_SYSTEM_NAME='&1'

prompt Enter the Application System Version

DEFINE P_APPLICATION_SYSTEM_VERSION=&2

prompt Enter the Table Name or % for All

DEFINE P_TABLE_NAME='&3'

prompt Enter the Database Name 

DEFINE P_DATABASE_NAME='&4'

prompt Enter the output script name

DEFINE P_OUTPUT_SCRIPT_NAME='&5'

prompt

prompt



set linesize 80

set pagesize 0

set heading  off

set echo     off

set feedback off

set showmode off

set verify   off

spool &P_OUTPUT_SCRIPT_NAME



column FOR_ORDERING_1 noprint

column FOR_ORDERING_2 noprint

column FOR_ORDERING_3 noprint

column FOR_ORDERING_4 noprint



select 

 td.name   "FOR_ORDERING_1"

,con.name  "FOR_ORDERING_2"

,0         "FOR_ORDERING_3"

,0         "FOR_ORDERING_4"

,'prompt Checking Foreign Key ' || substr(con.name,1,32) 

                     || ' on  ' || substr(td.name,1,32)  

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name        like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 	DISTINCT

 intd.name   

,con.name  

,1        

,0         

,'prompt     to ' || substr(reltd.name,1,32) 

from ci_columns             incol

    ,ci_table_definitions   intd

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         con

    ,ci_databases           db

    ,ci_table_definitions   reltd

    ,ci_columns             relcol

where aps.id                = intd.application_system_owned_by

  and aps.name              = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version           = &P_APPLICATION_SYSTEM_VERSION

  and intd.name          like upper('&P_TABLE_NAME')

  and con.constraint_type   = 'FOREIGN'

  and (   (    intd.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (intd.all_databases_ind = 'Y')

      )

  and intd.id             = incol.table_reference

  and con.id              = kc.constraint_reference

  and intd.id             = con.table_reference

  and incol.id            = kc.column_reference

  and relcol.id           = kc.foreign_column_reference

  and reltd.id            = relcol.table_reference

union

select 

 td.name   

,con.name  

,2         

,0         

,'select '  

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name   

,con.name  

,3        

,0         

,' ''FK'''  

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name            

,con.name  

,4                  

,kc.sequence_number 

,',intab.' || substr(col.name,1,32)

from ci_columns             col

    ,ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         con

    ,ci_databases           db

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and td.id               = col.table_reference

  and con.id              = kc.constraint_reference

  and td.id               = con.table_reference

  and col.id              = kc.column_reference

union

select 

 td.name   

,con.name  

,5         

,0         

,',''PK'''  

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id                = td.application_system_owned_by

  and aps.name              = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version           = &P_APPLICATION_SYSTEM_VERSION

  and td.name            like upper('&P_TABLE_NAME')

  and td.id                 = con.table_reference

  and con.constraint_type   = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name            

,confk.name  

,6                  

,kc.sequence_number 

,',intab.' || substr(col.name,1,32)

from ci_columns             col

    ,ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         confk

    ,ci_constraints         conpk

    ,ci_databases           db

where aps.id                = td.application_system_owned_by

  and aps.name              = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version           = &P_APPLICATION_SYSTEM_VERSION

  and td.name            like upper('&P_TABLE_NAME')

  and td.id                 = confk.table_reference

  and confk.constraint_type = 'FOREIGN'

  and td.id                 = conpk.table_reference

  and conpk.constraint_type = 'PRIMARY'

  and (   (    td.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and td.id               = col.table_reference

  and conpk.id            = kc.constraint_reference

  and td.id               = conpk.table_reference

  and col.id              = kc.column_reference

  and exists

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where confk.id = kc.constraint_reference

      )

union 

select 

 td.name   

,con.name  

,7         

,0         

,'from ' || substr(td.name,1,32) || ' intab '

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name   

,con.name  

,8         

,0         

,'where not exists ' 

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name   

,con.name  

,9       

,0         

,'( select ''x'' ' 

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 	DISTINCT

 intd.name   

,con.name  

,10         

,0         

,'  from   ' || substr(reltd.name,1,32) || ' reltab ' 

from ci_columns             incol

    ,ci_table_definitions   intd

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         con

    ,ci_databases           db

    ,ci_table_definitions   reltd

    ,ci_columns             relcol

where aps.id                = intd.application_system_owned_by

  and aps.name              = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version           = &P_APPLICATION_SYSTEM_VERSION

  and intd.name          like upper('&P_TABLE_NAME')

  and con.constraint_type   = 'FOREIGN'

  and (   (    intd.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (intd.all_databases_ind = 'Y')

      )

  and intd.id             = incol.table_reference

  and con.id              = kc.constraint_reference

  and intd.id             = con.table_reference

  and incol.id            = kc.column_reference

  and relcol.id           = kc.foreign_column_reference

  and reltd.id            = relcol.table_reference

union

select 	

 intd.name   

,con.name  

,11   

,kc.sequence_number        

,decode(kc.sequence_number

       ,1,'  where   ' || substr(incol.name,1,32) 

                       || ' = ' 

                       || substr(relcol.name,1,32)

       ,  '    and   ' || substr(incol.name,1,32) 

                       || ' = ' 

                       || substr(relcol.name,1,32) 

       ) 

from ci_columns             incol

    ,ci_table_definitions   intd

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         con

    ,ci_databases           db

    ,ci_table_definitions   reltd

    ,ci_columns             relcol

where aps.id                = intd.application_system_owned_by

  and aps.name              = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version           = &P_APPLICATION_SYSTEM_VERSION

  and intd.name          like upper('&P_TABLE_NAME')

  and con.constraint_type   = 'FOREIGN'

  and (   (    intd.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (intd.all_databases_ind = 'Y')

      )

  and intd.id             = incol.table_reference

  and con.id              = kc.constraint_reference

  and intd.id             = con.table_reference

  and incol.id            = kc.column_reference

  and relcol.id           = kc.foreign_column_reference

  and reltd.id            = relcol.table_reference

union

select 

 td.name   

,con.name  

,12

,0         

,') order by ' 

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name            

,con.name  

,13             

,kc.sequence_number 

,decode(kc.sequence_number   /* add 1 cause of fake col 'fk' */

       ,1,' ' || to_char(kc.sequence_number + 1,'9999')

       ,  ',' || to_char(kc.sequence_number + 1,'9999')

       )

from ci_columns             col

    ,ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_key_components      kc

    ,ci_constraints         con

    ,ci_databases           db

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name     = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and td.id               = col.table_reference

  and con.id              = kc.constraint_reference

  and td.id               = con.table_reference

  and col.id              = kc.column_reference

union

select 

 td.name   

,con.name  

,14    

,0         

,';' 

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

union

select 

 td.name   

,con.name  

,15   

,0         

,'  ' 

from ci_table_definitions   td

    ,ci_application_systems aps

    ,ci_databases           db

    ,ci_constraints         con

where aps.id              = td.application_system_owned_by

  and aps.name            = upper('&P_APPLICATION_SYSTEM_NAME')

  and aps.version         = &P_APPLICATION_SYSTEM_VERSION

  and td.name          like upper('&P_TABLE_NAME')

  and td.id               = con.table_reference

  and con.constraint_type = 'FOREIGN'

  and (   (    td.database_reference = db.id 

           and db.name    = upper('&P_DATABASE_NAME')

          )

       or (td.all_databases_ind = 'Y')

      )

  and exists 

      (select distinct kc.constraint_reference 

       from   ci_key_components kc

       where con.id = kc.constraint_reference

      )

order by 1,2,3,4

/

 

 

SPOOL OFF



set heading  on

set echo     on

set feedback on

set showmode on

set verify   on