Script To Quickly Find Object Info

Here is a script I’ve used to quickly hunt down objects and information about them.

Very useful to find things like: exact spellings, other objects with a similar spelling, or owned by a different user, synonyms, etc.



foinfo.sql:  find object info

Script to find objects and information about them.
By Rodger Lepinsky


accept ls_object_name prompt "Enter the object name to find info about:  " ;

select     object_type  ||  '   '  ||  owner || '.' || object_name   ||
               '    ' || object_id 
          as object_and_object_id
/*  ,  CREATED
  ,   LAST_DDL_TIME    */
from all_objects    /* By Rodger Lepinsky */
/* where upper(object_name) = trim(upper('&ls_object_name'))  */
where upper(object_name) like trim(upper('%&ls_object_name%'))      
/* and object_type IN ('TABLE', 'VIEW')  */ 
order by object_type, owner, object_name

In some environments, the different timestamps have been useful. Ie. Has the object just been recompiled?

You can vary the script as you see fit. Change it to find only with an exact spelling. Restrict it to objects types such as a TABLE or VIEW. Check the timestamps. Etc.



Leave a Reply

Please log in using one of these methods to post your comment: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: