--For a user schema's constraints:
select a.table_name,a.constraint_name,decode (constraint_type,'P',
'Primary',
'R', 'Foreign Key') type, delete_rule, column_name from
user_constraints a, user_cons_columns b where
constraint_type in ('P','R') and
a.owner=b.owner and
a.constraint_name=b.constraint_name
order by table_name,constraint_type asc
/
--For a count of all constraints by type:
select
owner
,decode(constraint_type,'U','Unique','R','Foreign Key','P','Primary
Key','C','Check','Other') cons_type
,count(*) num
from
dba_constraints
group by owner, decode(constraint_type,'U','Unique','R','Foreign
Key','P','Primary Key','C','Check','Other')
/
To document constraints...
Note, you can change this script to look at all_constraints or
dba_constraints and add the owner plus the other necessary changes to fully
document the entire database.
However, I would avoid documenting the sys/system/default accounts.
---------------------------------------------------
-- a table to hold your info:
CREATE TABLE cons_record (
owner VARCHAR2(30),
constraint_name VARCHAR2(30),
constraint_type VARCHAR2(11),
search_condition VARCHAR2(2000),
table_name VARCHAR2(30),
referenced_owner VARCHAR2(30),
referenced_constraint VARCHAR2(30),
delete_rule VARCHAR2(9),
constraint_columns VARCHAR2(2000),
con_number NUMBER,
date_detected date default sysdate,
date_retired date default to_date('99991231','YYYYMMDD')
) ;
--truncate if already present:
TRUNCATE TABLE cons_record;
---------------------------------------------------
create or replace procedure get_fk_refs is
CURSOR cons_cursor IS
SELECT owner, constraint_name, DECODE(constraint_type,'P','Primary
Key','R','Foreign Key','U','Unique','C','Check','D','Default'),
search_condition, table_name, r_owner, r_constraint_name, delete_rule
FROM all_constraints
WHERE owner NOT IN
('ODM','OLAPSYS','SYS','SYSTEM','DBAUTIL','OUTLN','ORACLE','OPS$ORACLE','SCO
TT','RMAN','WEBDB','DBSNMP','OAS_PUBLIC','PUBLIC','PERFSTAT','WMSYS','XDB');
CURSOR cons_col (cons_name in VARCHAR2) IS
SELECT owner, constraint_name, column_name
FROM all_cons_columns
WHERE owner NOT IN
('ODM','OLAPSYS','SYS','SYSTEM','DBAUTIL','OUTLN','ORACLE','OPS$ORACLE','SCO
TT','RMAN','WEBDB','DBSNMP','OAS_PUBLIC','PUBLIC','PERFSTAT','WMSYS','XDB')
AND constraint_name = UPPER(cons_name);
CURSOR get_cons (tab_nam in VARCHAR2) IS
SELECT DISTINCT owner, table_name, constraint_name, constraint_type
FROM cons_record
WHERE table_name=tab_nam AND constraint_type='Foreign Key'
ORDER BY owner, table_name, constraint_name;
CURSOR get_tab_nam is
SELECT DISTINCT table_name
FROM cons_record
WHERE constraint_type='Foreign Key'
ORDER BY table_name;
tab_nam all_constraints.table_name%TYPE;
cons_owner all_constraints.owner%TYPE;
cons_name all_constraints.constraint_name%TYPE;
cons_type VARCHAR2(11);
cons_sc all_constraints.search_condition%TYPE;
cons_tname all_constraints.table_name%TYPE;
cons_rowner all_constraints.r_owner%TYPE;
cons_rcons all_constraints.r_constraint_name%TYPE;
cons_dr all_constraints.delete_rule%TYPE;
cons_col_own all_cons_columns.owner%TYPE;
cons_col_nam all_cons_columns.constraint_name%TYPE;
cons_column all_cons_columns.column_name%TYPE;
cons_tcol_name all_cons_columns.table_name%TYPE;
all_columns VARCHAR2(2000);
counter INTEGER:=0;
cons_nbr INTEGER;
BEGIN
delete cons_record where 1=1;
commit;
OPEN cons_cursor;
LOOP
FETCH cons_cursor INTO cons_owner, cons_name, cons_type, cons_sc,
cons_tname, cons_rowner, cons_rcons, cons_dr;
EXIT WHEN cons_cursor%NOTFOUND;
all_columns :='';
counter := 0;
OPEN cons_col (cons_name);
LOOP
FETCH cons_col INTO cons_col_own, cons_col_nam, cons_column;
EXIT WHEN cons_col%NOTFOUND;
IF cons_owner = cons_col_own AND cons_name=cons_col_nam
THEN
counter := counter+1;
IF counter = 1 THEN
all_columns := all_columns||cons_column;
ELSE
all_columns := all_columns||', '||cons_column;
END IF;
END IF;
END LOOP;
CLOSE cons_col;
INSERT INTO cons_record VALUES (cons_owner, cons_name, cons_type,
cons_sc, cons_tname, cons_rowner, cons_rcons, cons_dr, all_columns, 0);
COMMIT;
END LOOP;
CLOSE cons_cursor;
COMMIT;
BEGIN
OPEN get_tab_nam;
LOOP
FETCH get_tab_nam INTO tab_nam;
EXIT WHEN get_tab_nam%NOTFOUND;
OPEN get_cons (tab_nam);
cons_nbr:=0;
LOOP
FETCH get_cons INTO cons_owner, cons_tname, cons_name, cons_type;
EXIT WHEN get_cons%NOTFOUND;
cons_nbr:=cons_nbr+1;
UPDATE cons_record
SET con_number=cons_nbr
WHERE
constraint_name=cons_name AND
constraint_type=cons_type AND
owner=cons_owner;
END LOOP;
CLOSE get_cons;
COMMIT;
END LOOP;
CLOSE get_tab_nam;
COMMIT;
END;
END;
/
---------------------------------------------------
exec get_fk_refs;
---------------------------------------------------
-- A report to see what you got:
SET FEEDBACK OFF TERMOUT OFF ECHO OFF
SET VERIFY OFF
SET PAGES 0 LINES 2000 TRIMSPOOL ON
SPOOL fklist.txt
SELECT distinct
trim(b.owner) ||'.'||
trim(b.table_name) ||'.'||
RTRIM(b.constraint_columns) ||' is referenced by '||
trim(a.owner) ||'.'||
trim(a.table_name) ||'.'||
RTRIM(a.constraint_columns) REFD_BY
FROM
cons_record a,
cons_record b
WHERE
a.referenced_constraint=b.constraint_name
ORDER BY
trim(b.owner) ||'.'||
trim(b.table_name) ||'.'||
RTRIM(b.constraint_columns) ||' is referenced by '||
trim(a.owner) ||'.'||
trim(a.table_name) ||'.'||
RTRIM(a.constraint_columns)
/
SPOOL OFF
SET VERIFY ON FEEDBACK ON TERMOUT ON PAGESIZE 22 LINES 80
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
-- end of code
-- hope this gets you what you need...
---------------------------------------------------
----- Original Message -----
From: "edwinuy" <oracledba-ezmlmshield-x20900728.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Tuesday, August 17, 2004 8:59 PM
Subject: Views or stored procedure and foreign keys
> Hi all,
>
> Just asking for your opinions here. Does anyone have a stored procedure
that will check which schemas/tables have foreign keys? Is it better to use
stored procedures or views to control what your users can and cannot have
access to?
>
>
> Cheers,
>
> EDWIN ONG UY
> UNIT 4,64 GRANGE ROAD
> MT EDEN 1003
> AUCKLAND, NEW ZEALAND
> Email: [Email address protected]
> Mobile: 64-021-622092
>
> -------------------------------------
> This email is intended solely for the named recipient/s and as such is
confidential and may contain information that is privileged. If you have
received it by mistake, please notify the sender. You must not use, copy,
distribute or disclose its contents to anyone other than the addressee. This
email and any attachments might not be free from computer viruses or other
defects.
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>
Oracle LazyDBA home page