Search database objects with a variable in oracle

Something I really often use is this select:

SELECT distinct type,line,name,text
FROM user_source
WHERE lower(Text) LIKE(‘%’||lower(:search_source)||’%’)
SELECT distinct ‘column’ type,null line,table_name||’.’|| COLUMN_NAME name,null
FROM user_tab_columns
WHERE lower(COLUMN_NAME) LIKE(‘%’||lower(:search_source)||’%’)
SELECT  DISTINCT ‘table’ type,null line,object_name name,null
from user_objects
WHERE object_type IN  (‘TABLE’,’VIEW’)
AND lower(object_name) LIKE(‘%’||lower(:search_source)||’%’)
ORDER BY type,name,line

This select will show you most of the database objects in a schema(columns, table,view,code), where this ‘search_source’ string is used.

You can add this into you sqldeveloper as well, as a user defined report.
To accomplish this you will have to do the following steps:
1) Open sqldeveloper
2) go to Reports tab(If you don’t see this tab go to the Menu choose view->Reports
3) right-click ‘User Defined Reports’ -> add Report -> give name and put the sql code as above into SQL input screen(you can leave the rest as default)
You can use this Search in sqldeveloper now.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

About Frederik Vallaeys