What Function is Table Used

[szeiss]

I'm using Oracle 9i and I also have TOAD. I want to take a table and list all the functions/prodcedures that table is being used in. I know I can do this in TOAD, but I don't want all the extra info that TOAD generates, I just want the name of the function/prodcedure.

 

[SantaMufasa]

SZeiss,

Here is a script for you that displays all of the PROCEDURE/FUNCTION dependencies that exist upon the TABLES/VIEWS of a particular owner. The script prompts for an OWNER whose table depencencies you would like to see. In this case, you should have DBA privileges to run the script since it accesses DBA_DEPENDENCIES. But if you do not have DBA privileges, then you can change the query to access ALL_DEPENDENCIES, which shows data to which you, as a non-DBA, have access.

Since the script has a SQL*Plus 'ACCEPT...PROMPT' statement, you cannot just copy and paste the code...you must save the code to a script (e.g. "show_tab_refs.sql") then invoke the script from SQL*Plus as I have done in the proof-of-concept frame, below:

(save the following code to "show_tab_refs.sql")
col x heading "Referenced Table" format a40
col y heading "Used By" format a40
set linesize 200
set pagesize 500
accept tabowner prompt "Enter the OWNER NAME of the target tables/views: "
select referenced_owner||'.'||referenced_name||' ('||referenced_type||')' x
      ,owner||'.'||name||' ('||type||')' y
from dba_dependencies
where type in ('PROCEDURE','FUNCTION')
  and referenced_type in ('TABLE','VIEW')
  and referenced_owner = upper('&tabowner')
order by 1
/

SQL> @show_tab_refs
Enter the OWNER NAME of the target tables/views: test

Referenced Table                         Used By
---------------------------------------- --------------------------------------
TEST.BUDDYTAB (TABLE)                    TEST.BUDDY (FUNCTION)
TEST.CLOBBER (TABLE)                     TEST.CLOBSTORE (PROCEDURE)
TEST.CUST_SCRPT (TABLE)                  TEST.GET_CUST_SCRPT_SUM (FUNCTION)
TEST.DDRILLICH (TABLE)                   TEST.GET_AMTS (FUNCTION)
TEST.DSCMIF3C (TABLE)                    TEST.DSCLAB3C (PROCEDURE)
TEST.DSCMOF3C (TABLE)                    TEST.DSCLAB3C (PROCEDURE)
TEST.JE27 (TABLE)                        TEST.STR_CDS (FUNCTION)
TEST.NESKIN (TABLE)                      TEST.GET_RUNNAMES (FUNCTION)
TEST.ODF_CA_PROJECT (TABLE)              TEST.GET_DSTI_PROJ_REF (FUNCTION)
TEST.PASSWORD_HISTORY (TABLE)            TEST.TRIM_PASSWORD_HISTORY (PROCEDURE)
TEST.PROJ_VIEW (VIEW)                    TEST.GET_PROJ_IDS (FUNCTION)
TEST.RAJESH (TABLE)                      TEST.STRINGER (FUNCTION)
TEST.REVIEWEROF (TABLE)                  TEST.GET_REVIEWERS (FUNCTION)
TEST.S_DEPT (TABLE)                      TEST.GET_DEPT (FUNCTION)
TEST.S_EMP (TABLE)                       TEST.GET_DEPT (FUNCTION)
TEST.S_EMP (TABLE)                       DHUNT.SHOWBYROWID (PROCEDURE)
TEST.S_EMP (TABLE)                       DHUNT.STRINGEMPLOYEES (FUNCTION)
TEST.TABLEX (TABLE)                      TEST.SK (PROCEDURE)
TEST.TEMP_CONS (TABLE)                   TEST.PREP_USER_CONSTRAINTS (PROCEDURE)
TEST.TEST (TABLE)                        TEST.GET_TESTS (FUNCTION)
TEST.T_TEST (TABLE)                      TEST.PR_TEST_PROCEDURE (PROCEDURE)

21 rows selected.
*******************************************************************************

If you want the script to run for a single table only, then adjust the script to prompt for just the table you want to feature.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[szeiss]

Thank you so much, just what I needed.