I have a query thta does exactly what I need it to do. It was created by
someone far more experienced that I (read I don't understand it)
It includes some column formating comands (an Oracle feature) which +apera_
to be critical to it's working corectly. If I am not mistaken these are
only efective for a given "session", so here's the question, if I do an
execute() with these before I execute the query will they still be in
effect?
Here is the query BTW:
----------
1. My previous scripts list ALL FOREIGN KEY columns, not PK cols.
2. It works no matter how many FKs a table has.
3. I simplified a little bit, it gives out all the info for sys schema
in less than 1 sec.
4. To list the PK cols, you only need to do slightly modification (see
below).
5. For DBA, I still think the 2nd one is much, much userful.
Here is the output:
Enter the table name check for foreign key: bonus
FK Column Name PK Table Name PK Column Name
-------------------- ------------------------- --------------------
DEPTNO DEPT DEPTNO
EMP_NAME, EMP_ID EMP ENAME, EMPNO
___________________________
Script for single table
___________________________
set verify off linesize 100
ACCEPT tab_n PROMPT 'Enter the table name check for foreign key: '
col "PK Table Name" format a25
col "FK Column Name" format a20 word_wrapped
col "PK Column Name" format a20 word_wrapped
select u.columns "FK Column Name", x.table_name "PK Table Name",
x.columns "PK Column Name"
from
(select a.constraint_name, a.r_constraint_name,
max(decode(position, 1,
substr(column_name,1,30),NULL)) ||
max(decode(position, 2,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,10,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,11,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,12,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,13,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,14,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,15,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,16,',
'||substr(column_name,1,30),NULL)) columns
from user_cons_columns b, user_constraints a
where a.table_name = UPPER('&tab_n') and
a.constraint_name = b.constraint_name
and a.constraint_type = 'R'
group by a.constraint_name,a.r_constraint_name) u,
(select a.table_name, a.constraint_name,
max(decode(position, 1,
substr(column_name,1,30),NULL)) ||
max(decode(position, 2,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 3,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 4,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 5,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 6,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 7,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 8,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position, 9,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,10,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,11,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,12,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,13,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,14,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,15,',
'||substr(column_name,1,30),NULL)) ||
max(decode(position,16,',
'||substr(column_name,1,30),NULL)) columns
from user_cons_columns b, user_constraints a
where
b.constraint_name = a.constraint_name
and a.constraint_type = 'P'
group by a.table_name, a.constraint_name) x
where u.r_constraint_name=x.constraint_name
/
--
Stan Brown [EMAIL PROTECTED] 843-745-3154
Charleston SC.
--
Windows 98: n.
useless extension to a minor patch release for 32-bit extensions and
a graphical shell for a 16-bit patch to an 8-bit operating system
originally coded for a 4-bit microprocessor, written by a 2-bit
company that can't stand for 1 bit of competition.
-
(c) 2000 Stan Brown. Redistribution via the Microsoft Network is prohibited.
