Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Sqlplus Script to find FKs

Re: Sqlplus Script to find FKs

From: <kal121_at_yahoo.com>
Date: Fri, 17 Dec 1999 17:46:27 GMT
Message-ID: <83dsti$228$1@nnrp1.deja.com> 





Here's a procedure I wrote that will find ALL primary keys, unique
keys, check constraints, other constraints, foreign keys and indexes
for either: 1. an individual table, or for ALL tables in a user's
schema. Sometimes I am reluctant to use other people's scripts because
I don't want to have to read through them, but this one really does
work, it won't screw anything up, and it's a real timesaver. Try it,
you might like it! Runs under 8i and 8.0.5, haven't tried it on 7.


/*




** con.sql






CREATE OR REPLACE PROCEDURE con (table_name_in dba_tables.table_name%
TYPE, owner_in dba_tables.owner%TYPE) AS


hold	VARCHAR2(255);
hold2	VARCHAR2(255);
flag	INT := 0;




owner_in_up VARCHAR2(255);



TYPE t1 IS RECORD(

col_name	dba_cons_columns.column_name%TYPE,
r_name		dba_constraints.r_constraint_name%TYPE


);



t_rec t1;



/* loops thru all tables in schema */


CURSOR cAll IS

SELECT	table_name
FROM	dba_tables
WHERE	owner = UPPER(owner_in);





CURSOR c1 (t_in VARCHAR2, o_in VARCHAR2) IS
SELECT


	table_name,
	constraint_name,
	DECODE(constraint_type,
	'C', 'CHECK',
	'O', 'Read Only',
	'P', 'PRIMARY KEY',
	'R', 'FOREIGN KEY',
	'U', 'UNIQUE',
	'V', 'Check Option View') constraint_type,
	status,
	search_condition
FROM	dba_constraints
WHERE	table_name = t_in
AND	owner = o_in




ORDER BY constraint_type DESC;



/* used for finding the columns that comprise a primary, unique, or
index */


CURSOR c2(index_name_in VARCHAR2) IS

SELECT	column_name
FROM 	dba_ind_columns
WHERE	index_name = index_name_in




ORDER BY column_position;



/* other indexes besides primary and unique key indexes, ie) alternate
indexes */


CURSOR c3(t_in VARCHAR2, o_in VARCHAR2) IS

SELECT	index_name,
	status
FROM 	dba_indexes
WHERE	table_name = t_in
AND	owner = o_in
AND	index_name NOT IN (SELECT constraint_name
				FROM dba_constraints
				WHERE table_name = t_in
				AND owner = o_in
				AND constraint_type IN ('P', 'U'));


BEGIN


owner_in_up := UPPER(owner_in);



IF UPPER(table_name_in) = 'ALL' THEN



   FOR cAll_rec IN cAll LOOP /* loop thru all tables in dba_tables for
given "owner_in" */



        FOR c1_rec IN c1(cAll_rec.table_name, owner_in_up) LOOP



                hold := cAll_rec.table_name;



                IF flag = 0 OR hold <> hold2 THEN


			/* title */
			DBMS_OUTPUT.PUT_LINE (CHR(9));
			DBMS_OUTPUT.PUT_LINE ('Table




Name: '||c1_rec.table_name);



                        DBMS_OUTPUT.PUT_LINE (	RPAD('CONSTRAINT/INDEX

NAME',30,' ')||


					RPAD('TYPE',15,' ')||
					RPAD('STATUS',10,' ')||' CHECK
CONSTRAINT CONDITION');
			DBMS_OUTPUT.PUT_LINE
('**********************************************************************


*******************');


			flag := 1;
			hold2 := c1_rec.table_name;

		END IF;

		/* output one line for each constraint defined on the
table, regardless of type */
		DBMS_OUTPUT.PUT_LINE (	RPAD
(c1_rec.constraint_name,30,' ')||
					RPAD
(c1_rec.constraint_type,15,' ')||
					RPAD(c1_rec.status,10,' ')




||' '||c1_rec.search_condition);



                /* if it's primary or unique, list the columns in the
key */


                IF c1_rec.constraint_type in ('PRIMARY KEY', 'UNIQUE')
THEN


                        FOR c2_rec IN c2(c1_rec.constraint_name) LOOP



                                DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR

(9)||CHR(9)||LOWER(c2_rec.column_name));



                        END LOOP;


                /* if it's foreign, list the primary key constraint
name that it points to */


                ELSIF c1_rec.constraint_type = ('FOREIGN KEY') THEN


			SELECT	dcc.column_name,
				dc.r_constraint_name
			INTO	t_rec
			FROM	dba_constraints dc,
				dba_cons_columns dcc
			WHERE	dc.owner = owner_in_up
			AND	dc.constraint_type = 'R'
			AND	dc.table_name = c1_rec.table_name
			AND	dcc.constraint_name =
c1_rec.constraint_name
			AND	dc.constraint_name =




dcc.constraint_name;



                        DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR(9)||CHR

(9)||LOWER(t_rec.col_name)||' points to '||t_rec.r_name);



                END IF;


        END LOOP;


        FOR c3_rec IN c3(cAll_rec.table_name, owner_in_up) LOOP


		DBMS_OUTPUT.PUT_LINE (	RPAD(c3_rec.index_name,30,' ')||
					RPAD('INDEX',15,' ')||
					RPAD(c3_rec.status,10,' '));

		FOR c2_rec IN c2(c3_rec.index_name) LOOP

			DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR(9)||CHR


(9)||LOWER(c2_rec.column_name));



                END LOOP;


        END LOOP;


  END LOOP; -- for "ALL" loop



ELSE -- for only one table



        FOR c1_rec IN c1(UPPER(table_name_in), UPPER(owner_in)) LOOP



        IF flag = 0 THEN


		/* title */
		DBMS_OUTPUT.PUT_LINE (CHR(9));
		DBMS_OUTPUT.PUT_LINE ('Table




Name: '||c1_rec.table_name);



                DBMS_OUTPUT.PUT_LINE (	RPAD('CONSTRAINT/INDEX

NAME',30,' ')||


					RPAD('TYPE',15,' ')||
					RPAD('STATUS',10,' ')||' CHECK
CONSTRAINT CONDITION');
		DBMS_OUTPUT.PUT_LINE
('**********************************************************************


*******************');



		flag := 1;

	END IF;


		/* output one line for each constraint defined on the
table, regardless of type */
		DBMS_OUTPUT.PUT_LINE (	RPAD
(c1_rec.constraint_name,30,' ')||
					RPAD
(c1_rec.constraint_type,15,' ')||
					RPAD(c1_rec.status,10,' ')




||' '||c1_rec.search_condition);



                /* if it's primary or unique, list the columns in the
key */


                IF c1_rec.constraint_type in ('PRIMARY KEY', 'UNIQUE')
THEN


                        FOR c2_rec IN c2(c1_rec.constraint_name) LOOP



                                DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR

(9)||CHR(9)||LOWER(c2_rec.column_name));



                        END LOOP;


                /* if it's foreign, list the primary key constraint
name that it points to */


                ELSIF c1_rec.constraint_type = ('FOREIGN KEY') THEN


			SELECT	dcc.column_name,
				dc.r_constraint_name
			INTO	t_rec
			FROM	dba_constraints dc,
				dba_cons_columns dcc
			WHERE	dc.owner = UPPER(owner_in)
			AND	dc.constraint_type = 'R'
			AND	dc.table_name = c1_rec.table_name
			AND	dcc.constraint_name =
c1_rec.constraint_name
			AND	dc.constraint_name =




dcc.constraint_name;



                        DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR(9)||CHR

(9)||LOWER(t_rec.col_name)||' points to '||t_rec.r_name);



                END IF;


        END LOOP;


        FOR c3_rec IN c3(UPPER(table_name_in), UPPER(owner_in)) LOOP


		DBMS_OUTPUT.PUT_LINE (	RPAD(c3_rec.index_name,30,' ')||
					RPAD('INDEX',15,' ')||
					RPAD(c3_rec.status,10,' '));

		FOR c2_rec IN c2(c3_rec.index_name) LOOP

			DBMS_OUTPUT.PUT_LINE(CHR(9)||CHR(9)||CHR(9)||CHR


(9)||LOWER(c2_rec.column_name));



                END LOOP;


        END LOOP;


END IF;


END;



/




/* NOTE: The following is NOT part of the procedure, but can be run on
its own */



/* run the following to see ALL foreign keys in a given schema


column "THIS TABLE'S" format a20;
column "FOREIGN KEY NAMED" format a25;
column "ON COLUMN  POINTS TO" format a25;
column "THIS PRIMARY KEY" format a25;





select dc.table_name "THIS TABLE'S", dc.constraint_name "FOREIGN KEY
NAMED", column_name "ON COLUMN  POINTS TO", r_constraint_name "THIS
PRIMARY KEY"



from dba_constraints dc, dba_cons_columns c
where dc.owner = 'JDBC' -- change owner as needed
and dc.constraint_type = 'R'


and dc.constraint_name = c.constraint_name;
*/




Sent via Deja.com http://www.deja.com/



Before you buy.
Received on Fri Dec 17 1999 - 11:46:27 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US