Home > oracle, Scripts, SQL > Indiana Jones and the Lost Table

Indiana Jones and the Lost Table


Let’s put it this way: you have some data that you want to find in the database, but you have no idea of what table it is. You don’t even know the name of the column, so you have to search a single number IN EVERY COLUMN OF EVERY TABLE. At least you know the schema where it is…

So, we have to find where is the data 1662 only knowing the name of the schema. The name of the schema is Absys. Don’t worry, we can still do it, but it won’t be an easy task.

We can do one of these two things:

1. Search manually on every table, looking in every column for a coincidence of the numer 1161.

2. Create a script that does it.

I chose the nº2, so there is my script in case it may help you:

sqlplus “/as sysdba”
set lines 200;
set pages 50000;
spool search.sql
SELECT ‘SELECT * FROM ABSYS.’|| TABLE_NAME||’ WHERE ‘||COLUMN_NAME||’=1161;’ FROM ALL_TAB_COLUMNS WHERE OWNER=’ABSYS’;
spool off;
spool search_findings.txt
@search.sql
spool off;

Ét voilà! You will have the coincidences of this data in every column of every table of the schema named ABSYS. Although the execution of your script may take a while 🙂

Advertisements
Categories: oracle, Scripts, SQL Tags: , ,
  1. Neeraj Vasudeva
    May 13, 2010 at 3:02 am

    simple, clear, and informative. keep it up.

  2. May 13, 2010 at 5:50 am

    Thanks Neeraj, I’ll do it!

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: