rem Program: d2ukchk.sql

rem Author : David Wendelken

rem        : Tactics, Inc.

rem Purpose: To Report Violations of Intended Primary and Unique

rem        : Key Constraints.  This is done by showing all rows in

rem        : 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 Keller 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 unique 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 primary/unique 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.

rem        : the program kcreseq.sql is invoked to deal with this.



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"

,1         "FOR_ORDERING_3"

,0         "FOR_ORDERING_4"

,'prompt Checking ' 

  || decode(con.constraint_type

           ,'UNIQUE','Unique'

           ,'Primary'

           )

  || ' Key ' 

  || substr(con.name,1,24)

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 in ('UNIQUE','PRIMARY')

  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   "FOR_ORDERING_1"

,con.name  "FOR_ORDERING_2"

,2         "FOR_ORDERING_3"

,0         "FOR_ORDERING_4"

,'prompt             for ' || 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 in ('UNIQUE','PRIMARY')

  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         

,'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 in ('UNIQUE','PRIMARY')

  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 

,decode(kc.sequence_number

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

       ,  ',' || 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 in ('UNIQUE','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 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         

,',count(*)' 

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 in ('UNIQUE','PRIMARY')

  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  

,6         

,0         

,'from ' || 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 in ('UNIQUE','PRIMARY')

  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  

,7         

,0         

,'group 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 in ('UNIQUE','PRIMARY')

  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                 

,kc.sequence_number 

,decode(kc.sequence_number

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

       ,  ',' || 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 con.constraint_type in ('UNIQUE','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 con.id              = kc.constraint_reference

  and td.id               = con.table_reference

  and col.id              = kc.column_reference

union

select 

 td.name   

,con.name  

,9    

,0         

,'having count(*) > 1 ' 

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 in ('UNIQUE','PRIMARY')

  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  

,10       

,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 in ('UNIQUE','PRIMARY')

  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  

,11      

,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 in ('UNIQUE','PRIMARY')

  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  off

set echo     off

set feedback off

set showmode off

set verify   off