Home » SQL & PL/SQL » SQL & PL/SQL » how to get list of objects which are using a table
how to get list of objects which are using a table [message #19534] Tue, 26 March 2002 00:39 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,
What is the script to see all the objects(like procedures,packages,views etc) which use a particular table which i want.
eg: if i have a table test1,i want to know what all objects use this table.
What is the sql statement to find out this.

any help is appreciated

Report message to a moderator

Re: how to get list of objects which are using a table [message #19535 is a reply to message #19534] Tue, 26 March 2002 05:07 Go to previous messageGo to next message
Epe
Messages: 99
Registered: March 2002
Member
Hello,

you can use the table all_source (or user_source or dba_source). You'll see that this dictionary view has a couple of interesting attributes, like name and text.
SQL> select distinct name
from all_source
where upper(text) like '%TEST1%';

Check Oracle Manuals :
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76961/ch277.htm

Succes !!

Epe

Report message to a moderator

Re: how to get list of objects which are using a table [message #19539 is a reply to message #19534] Tue, 26 March 2002 09:19 Go to previous message
Grant
Messages: 578
Registered: January 2002
Senior Member
Just drop the table and see what breaks! Kidding.

For TYPE

You would "select owner,name,type from dba_source where text like '%TEST1%';

For VIEWS:

select owner,view_name from dba_views where text like '%TEST1%';

As you can see there is no easy way. You would have to build queries on DBA_TRIGGERS, DBA_SEQUENCES, and your constarint views to get all the info. You might be better off getting a script that creates all the objects then do a search on it (http://www.orsweb.com/downloads/source/74.html or http://www.oracle.com/oramag/code/ has a schema build somewhere). This uses an export file and is free C:Program FilesDataBeeHelpmanualhome.htm

You could also download TOAD. It will give you "USES" and "USED BY".

Report message to a moderator

Previous Topic: Transferring data from a remote table to a local one
Next Topic: UN -ENTERABLE FIELD
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Apr 20 06:36:06 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.