Forum OpenACS Q&A: Response to How do you find foreign keys in Oracle?
Posted by
Michael Bryzek
on 05/14/02 03:23 AM
Here's a small oracle package with a few procedures to print out random info... After loading the package, you can run the following command to get the tables (and constraint names) for tables that reference your table... Hope it helps.
set serveroutput on
exec bryzek_util.references_to_table('FOO');
You'll get a list of all the tables and constraint names that reference FOO. You can then look at the SQL for references_to_table and should be able to quickly modify it if you need more info.
Sample output:
18:25:29 bryzek@ora8i> create table foo (id integer primary key);
Table created.
18:25:30 bryzek@ora8i> create table bar (bar_id references foo(id));
Table created.
18:25:37 bryzek@ora8i> exec bryzek_util.references_to_table('BAR');
References to BAR
- No references found
PL/SQL procedure successfully completed.
18:25:42 bryzek@ora8i> exec bryzek_util.references_to_table('FOO');
References to FOO
- BAR (SYS_C0044410)
PL/SQL procedure successfully completed.
18:25:45 bryzek@ora8i>
Oracle package:
-- bryzek-util.pls
--
-- Random oracle utilities
-- @author Michael Bryzek
-- @id $Id: bryzek-util.pls,v 1.1.1.1 2002/01/29 17:58:24 mbryzek Exp $
--
-- Copyright (C) 2001 Michael Bryzek
--
-- This program is free software; you can redistribute it and/or
-- modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2
-- of the License, or (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
--
create or replace package bryzek_util AUTHID CURRENT_USER as
/**
* Disables all the constraints on the specified table, including
* the referential constraints from tables that reference this one.
*
* @author Michael Bryzek
* @creation-date 2001-08-06
**/
procedure disable_constraints (
p_table_name user_tables.table_name%TYPE
);
/**
* Enables all the constraints on the specified table, including
* the referential constraints from tables that reference this one.
*
* @author Michael Bryzek
* @creation-date 2001-08-06
**/
procedure enable_constraints (
p_table_name user_tables.table_name%TYPE
);
/**
* Drops the specified table only if it exists.
*
* @author Michael Bryzek
* @creation-date 2001-08-06
**/
procedure drop_table (
p_table_name user_tables.table_name%TYPE
);
/**
* Displays all tables that reference the specified table.
*
* @author Michael Bryzek
* @creation-date 2001-08-21
**/
procedure references_to_table (
p_table_name user_tables.table_name%TYPE
);
/**
* Returns 1 if a table with the specified name exists
* (case-insensitive). Returns 0 otherwise
*
* @author Michael Bryzek
* @creation-date 2001-08-21
**/
function table_exists (
p_table_name user_tables.table_name%TYPE
) return integer;
/**
* Attempts to drop the user schema by first dropping all
* constraints, then dropping all objects from the
* user_objects table.
*
* @author Michael Bryzek
* @creation-date 2001-09-21
**/
procedure drop_user_schema;
/**
* Drops all constraints in the current user's schema.
*
* @author Michael Bryzek
* @creation-date 2001-09-21
**/
procedure drop_user_constraints;
end bryzek_util;
/
show errors;
create or replace package body bryzek_util as
--------------------------------------------------------------------
-- PRIVATE METHODS
--------------------------------------------------------------------
-- Returns 1 if an object with the specified name and type exists
function object_exists (
p_object_type user_objects.object_type%TYPE,
p_object_name user_objects.object_name%TYPE
)
return integer
is
v_result integer;
begin
select count(*) into v_result
from user_objects
where object_name = upper(trim(p_object_name))
and object_type = upper(trim(p_object_type));
return v_result;
end;
procedure output (
p_message VARCHAR,
p_sql VARCHAR
)
is
p_display integer := 0;
begin
if (p_display = 1) then
dbms_output.put_line('----------------------------------------------------------------------');
dbms_output.put_line(p_message);
dbms_output.put_line(p_sql);
end if;
end;
procedure drop_constraint (p_table user_tables.table_name%TYPE,
p_constraint_name user_constraints.constraint_name%TYPE)
is
v_sql varchar(4000);
begin
begin
v_sql := 'alter table ' || p_table || ' drop constraint ' || p_constraint_name;
dbms_output.put_line(v_sql);
execute immediate v_sql;
exception when others then
dbms_output.put_line('Error dropping constraint ' || p_constraint_name || ': ' || SUBSTR(SQLERRM,1,100));
end;
end;
--------------------------------------------------------------------
-- PUBLIC API
--------------------------------------------------------------------
procedure drop_table (
p_table_name user_tables.table_name%TYPE
)
is
v_result integer := 0;
begin
execute immediate 'select count(*) ' ||
'from user_tables ' ||
'where table_name=upper(trim(:table_name))' into v_result using p_table_name;
if (v_result > 0) then
execute immediate 'drop table ' || p_table_name;
end if;
end;
procedure constraint_toggle_foreign (
p_status user_constraints.status%TYPE,
p_table_name user_tables.table_name%TYPE
)
is
TYPE row_cursor_type IS REF CURSOR;
row_cursor row_cursor_type;
v_sql varchar(4000);
v_table_name user_tables.table_name%TYPE;
v_constraint_name user_constraints.constraint_name%TYPE;
v_constraint_type user_constraints.constraint_type%TYPE := 'R';
begin
-- query to pull out constraints from tables that reference this one
v_sql := 'select constraint_name, table_name ' ||
'from user_constraints ' ||
'where constraint_type=:1 ' ||
'and status not like ''' || p_status || '%'' ' ||
'and r_constraint_name in (select constraint_name ' ||
'from user_constraints ' ||
'where constraint_type in (''P'',''U'') ' ||
'and table_name=upper(trim(:2)))';
output('Retrieving foreign key constraints to table', v_sql);
open row_cursor for v_sql using v_constraint_type, p_table_name;
LOOP
fetch row_cursor into v_constraint_name, v_table_name;
exit when row_cursor%NOTFOUND;
v_sql := 'alter table ' || v_table_name || ' ' || p_status || ' constraint ' || v_constraint_name;
output('Query to ' || p_status || ' this constraint', v_sql);
execute immediate v_sql;
end loop;
end;
procedure constraint_toggle_table (
p_status user_constraints.status%TYPE,
p_table_name user_tables.table_name%TYPE
)
is
TYPE row_cursor_type IS REF CURSOR;
row_cursor row_cursor_type;
v_sql varchar(4000);
v_table_name user_tables.table_name%TYPE;
v_constraint_name user_constraints.constraint_name%TYPE;
v_constraint_type user_constraints.constraint_type%TYPE := 'R';
begin
-- query to pull out remaining enabled constraints on our table
-- This actually doesn't need dynamic sql, but i wanted to learn
-- how to declare my own row cursor
v_sql := 'select constraint_name, table_name ' ||
'from user_constraints ' ||
'where status not like ''' || p_status || '%'' ' ||
'and table_name=upper(trim(:1))';
output('Retrieving remaining enabled constraints on table', v_sql);
open row_cursor for v_sql using p_table_name;
LOOP
fetch row_cursor into v_constraint_name, v_table_name;
exit when row_cursor%NOTFOUND;
v_sql := 'alter table ' || v_table_name || ' ' || p_status || ' constraint ' || v_constraint_name;
output('Query to ' || p_status || ' this constraint', v_sql);
execute immediate v_sql;
end loop;
end;
procedure disable_constraints (
p_table_name user_tables.table_name%TYPE
)
is
begin
constraint_toggle_foreign('DISABLE', p_table_name);
constraint_toggle_table('DISABLE', p_table_name);
end;
procedure enable_constraints (
p_table_name user_tables.table_name%TYPE
)
is
begin
constraint_toggle_table('ENABLE', p_table_name);
constraint_toggle_foreign('ENABLE', p_table_name);
end;
procedure references_to_table (
p_table_name user_tables.table_name%TYPE
)
is
v_found INTEGER := 0;
begin
if table_exists(p_table_name) = 0 then
raise_application_error(-20000, 'There is no table named: ' || p_table_name);
end if;
dbms_output.put_line('References to ' || p_table_name);
for row in (select constraint_name, table_name
from user_constraints
where r_constraint_name in (select constraint_name
from user_constraints
where table_name = upper(trim(p_table_name)))) loop
dbms_output.put_line(' - ' || row.table_name || ' (' || row.constraint_name || ')');
if v_found = 0 then
v_found := 1;
end if;
end loop;
if v_found = 0 then
dbms_output.put_line(' - No references found');
end if;
end;
function table_exists (
p_table_name user_tables.table_name%TYPE
)
return integer
is
begin
return object_exists('table', p_table_name);
end;
procedure drop_user_constraints
is
v_sql varchar(4000);
v_exists integer;
begin
for row in (select cols.table_name, con.constraint_name
from user_constraints con, user_cons_columns cols
where con.constraint_name = cols.constraint_name
and con.constraint_type = 'R') loop
drop_constraint(row.table_name, row.constraint_name);
end loop;
for row in (select cols.table_name, con.constraint_name
from user_constraints con, user_cons_columns cols, user_tables tabs
where con.constraint_name = cols.constraint_name
and cols.table_name = tabs.table_name
and (tabs.iot_name is null or con.constraint_type <> 'P')) loop
drop_constraint(row.table_name, row.constraint_name);
end loop;
end;
procedure drop_user_schema
is
v_sql varchar(4000);
v_exists integer;
begin
drop_user_constraints;
for row in (select object_type, object_name
from user_objects
where object_type in ('PACKAGE', 'VIEW', 'TABLE', 'SEQUENCE')
and (object_name <> 'BRYZEK_UTIL')) loop
v_sql := 'drop ' || row.object_type || ' ' || row.object_name;
dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
end;
end bryzek_util;
/
show errors;
