sql to find references to a table

hi

Is there a sql to find out all the store procedures which are using a table?

thanks
royjaydAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
The view you need is DBA_DEPENDENCIES.

http://www.dba-oracle.com/d_dba_dependencies.htm
0
Christoffer SwanströmPartnerCommented:
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'YOUR_TABLE_NAME'
AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

This will show you all packages, procedure and functions that reference your table.

Note that you will NOT be able to find procedures, functions or packages that reference a table using dynamic SQL. E.g. if your procedure contains EXECUTE IMMEDIATE 'TRUNCATE YOUR_TABLE_NAME', the above query will not show this dependency.
0
Christoffer SwanströmPartnerCommented:
If you want to find procedure, functions or packages where the name of the table is hard coded in a dynamic SQL statement you can query all_source:

SELECT
  *
FROM
  all_source
WHERE
  UPPER(text) LIKE '%YOUR_TABLE_NAME%'
;

Of course this will only find the cases where the table name is hard coded. If it is passed as an argument or otherwise generated dynamically you will not find it.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

royjaydAuthor Commented:
thanks

when i do
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'PERSON_DATA'  AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

i get error table does not exist

however when i replace dba_dependencies with all_dependencies
i get some results

why is that
0
slightwv (䄆 Netminder) Commented:
"dba_" level views require DBA level access and show EVERYTHING in the database

"all_" level views are the objects you own and have rights to access

"user_" views are the objects you own.
0
awking00Information Technology SpecialistCommented:
It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.
0
royjaydAuthor Commented:
>>>It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.


what exactly do you mean by dictionary ? Do you mean schema ?


thx all.
0
slightwv (䄆 Netminder) Commented:
The data dictionary are all the system views/tables about things (metadata).  A schema is a user that owns objects.  Two different things.

http://en.wikipedia.org/wiki/Data_dictionary
0
royjaydAuthor Commented:
one quick question

when i run
SELECT * FROM all_dependencies WHERE  referenced_name = 'GET_STATUS' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

i get no data found

But i see 'GET_STATUS' is defined under function which is being used by a procedure body.

any idea why?
0
slightwv (䄆 Netminder) Commented:
Works for me:
create or replace function myfunc return varchar2 
is
begin
	return 'Hello';
end;
/

show errors

create or replace procedure myproc
is
begin
	dbms_output.put_line(myfunc);
end;
/

show errors


SELECT * FROM all_dependencies WHERE  referenced_name = 'MYFUNC' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

Open in new window


Does your setup resemble my test case?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
royjaydAuthor Commented:
>>Does your setup resemble my test case?


Does it have to do something with grants? Do i need to have grants to all the functions ?
0
royjaydAuthor Commented:
and yes,.. my set up is exactly like yours
0
slightwv (䄆 Netminder) Commented:
If it was a permission issue you would get a permissions error.

Remember the ALL_ views are just objects you either own or have been granted access to.

What is the result of:
select owner, object_type from all_objects where object_name='GET_STATUS';
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.