Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query to search all packages for table and/or column

Tags:

oracle

Is there a query I can run to search all packages to see if a particular table and/or column is used in the package? There are too many packages to open each one and do a find on the value(s) I'm looking for.

like image 831
Matt M Avatar asked Jan 31 '11 16:01

Matt M


People also ask

How do you find a value in all column of all table in Oracle?

I know that there is simple way to search a value in all columns by using where condition. Example: Select * from tablename where col1='value' || col2 = 'value' || col3='value' || col4='value'.

How do I search for a package in SQL Developer?

To open the Find Database Object pane, right-click a connection name in the Connections navigator and select Find DB Object. You can also click on VIEW and then on FIND DB Object. Get Oracle SQL Developer now with the O'Reilly learning platform.

How do I list all tables in a specific schema?

The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.


2 Answers

You can do this:

select * from user_source where upper(text) like upper('%SOMETEXT%'); 

Alternatively, SQL Developer has a built-in report to do this under:

View > Reports > Data Dictionary Reports > PLSQL > Search Source Code 

The 11G docs for USER_SOURCE are here

like image 81
Tony Andrews Avatar answered Oct 21 '22 15:10

Tony Andrews


you can use the views *_DEPENDENCIES, for example:

SELECT owner, NAME   FROM dba_dependencies  WHERE referenced_owner = :table_owner    AND referenced_name = :table_name    AND TYPE IN ('PACKAGE', 'PACKAGE BODY') 
like image 45
Vincent Malgrat Avatar answered Oct 21 '22 15:10

Vincent Malgrat