mikensu12
asked on
data dictionary
Want to search packages, views, table, functions, etc for an object name 'company_search' . What is the query from data dictionary?
Thanks
Thanks
select * from all_objects where object_name = 'COMPANY_SEARCH';
select * from all_objects where OBJECT_NAME = 'company_search'
Are you asking if the object is used in source code?
There is a DBA_DEPENDENCIES view:
select owner, name, type from dba_dependencies where referenced_name='COMPANY_S EARCH';
That will work if the procedure code doesn't use dynamic SQL. IF if might then:
select name from user_source where upper(text) like '%COMPANY_SEARCH%';
There is a DBA_DEPENDENCIES view:
select owner, name, type from dba_dependencies where referenced_name='COMPANY_S
That will work if the procedure code doesn't use dynamic SQL. IF if might then:
select name from user_source where upper(text) like '%COMPANY_SEARCH%';
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i will second johnsone comment that while searching, you should query DBA_OBJECTS view.
So can you try the below 2 queries and you should get some output if such object exists from one of the queries.
select * from DBA_OBJECTS where object_name = 'COMPANY_SEARCH';
--> this will help to get you the object if it is created with just company_search as the object name without quotes enclosing the name.
or
select * from DBA_OBJECTS where object_name = 'company_search'; --> this will help to get you the object if it is created with "company_search" as the object name with quotes enclosing the obect name to preserve its exact name in lower case letters.
So can you try the below 2 queries and you should get some output if such object exists from one of the queries.
select * from DBA_OBJECTS where object_name = 'COMPANY_SEARCH';
--> this will help to get you the object if it is created with just company_search as the object name without quotes enclosing the name.
or
select * from DBA_OBJECTS where object_name = 'company_search'; --> this will help to get you the object if it is created with "company_search" as the object name with quotes enclosing the obect name to preserve its exact name in lower case letters.
This data is recorded in the systemtables and a full list can be found at:
http://www.techonthenet.co m/oracle/s ys_tables/
http://www.techonthenet.co
The techonthenet link above is is not a full list of the dictionary tables.
In fact those aren't tables at all, but views.
It is however, as stated in the link, a set of commonly used views.
However the critical views have already been mentioned above.
In fact those aren't tables at all, but views.
It is however, as stated in the link, a set of commonly used views.
However the critical views have already been mentioned above.
if you're looking for a procedure called "company_search" you can look in dba_procedures/all_procedu res to see where it is defined whether it is a stand alone procedure or defined within a package.