How do I search the entire database for a value and return the
tablenames and column names that contain this value? For argument
sake, let's say I'm searching for the text "the quick brown fox".

I think someone posted this question before but it never got any
responses--since it's been awhile, i thought i might post it again.

In case your wondering why I want to do this, I'm using a software
package that stores all it's data in a database. It shows me the
data, but I have no idea what tables and column names contain these
values so it's hard to change the data through sql scripting.

thanks a lot,
Sean.

On 2007-02-10, seannakasone@yahoo.com <seannakasone@yahoo.com> wrote:
> How do I search the entire database for a value and return the
> tablenames and column names that contain this value? For argument
> sake, let's say I'm searching for the text "the quick brown fox".
>
> I think someone posted this question before but it never got any
> responses--since it's been awhile, i thought i might post it again.
>
> In case your wondering why I want to do this, I'm using a software
> package that stores all it's data in a database. It shows me the
> data, but I have no idea what tables and column names contain these
> values so it's hard to change the data through sql scripting.

I have written something that might be helpful for you on
my website at http://www.adp-gmbh.ch/ora/misc/find_in_tables.html

hth,
Rene


--
Rene Nyffenegger
http://www.adp-gmbh.ch

On Feb 10, 5:55 am, Rene Nyffenegger <rene.nyffeneg...@gmx.ch> wrote:
> On 2007-02-10, seannakas...@yahoo.com <seannakas...@yahoo.com> wrote:
>
> > How do I search the entire database for a value and return the
> > tablenames and column names that contain this value? For argument
> > sake, let's say I'm searching for the text "the quick brown fox".
>
> > I think someone posted this question before but it never got any
> > responses--since it's been awhile, i thought i might post it again.
>
> > In case your wondering why I want to do this, I'm using a software
> > package that stores all it's data in a database. It shows me the
> > data, but I have no idea what tables and column names contain these
> > values so it's hard to change the data through sql scripting.
>
> I have written something that might be helpful for you on
> my website athttp://www.adp-gmbh.ch/ora/misc/find_in_tables.html
>
> hth,
> Rene
>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch

Rene,

Nice code. I was wondering if it can be made faster by filtering out
columns
whose data_length is smaller than the length of the string being
searched.
One of course needs to be careful with char length semantics / utf8

Cheers
Anurag

seannakasone@yahoo.com schreef:
> How do I search the entire database for a value and return the
> tablenames and column names that contain this value? For argument
> sake, let's say I'm searching for the text "the quick brown fox".
>
> I think someone posted this question before but it never got any
> responses--since it's been awhile, i thought i might post it again.
>
> In case your wondering why I want to do this, I'm using a software
> package that stores all it's data in a database. It shows me the
> data, but I have no idea what tables and column names contain these
> values so it's hard to change the data through sql scripting.
>
> thanks a lot,
> Sean.
>
I am with Anurag - nice piece of code.

But - it does not support LOBs, and Text Indexes do.
You can create an index, that works over several columns,
in different tables.
Drawback however, it's not dynamically; you'll have to
know what to index.

Sean, do you really mean to search the entire database?
It seems such a waste of resources - your data will most
likely be in tables of one schema (which resembles a "database"
in MS speak).
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

On Feb 10, 5:04 am, Frank van Bortel <frank.van.bor...@gmail.com>
wrote:
> Sean, do you really mean to search the entire database?
> It seems such a waste of resources - your data will most
> likely be in tables of one schema (which resembles a "database"
> in MS speak).
> --
> Regards,
> Frank van Bortel

Thanks for a your input, it's really helpful.
You are correct, I just want to search one schema.
thanks,
Sean.

seannakasone@yahoo.com wrote in news:1171096816.180163.16760
@s48g2000cws.googlegroups.com:

> How do I search the entire database for a value and return the
> tablenames and column names that contain this value? For argument
> sake, let's say I'm searching for the text "the quick brown fox".
>
> I think someone posted this question before but it never got any
> responses--since it's been awhile, i thought i might post it again.
>
> In case your wondering why I want to do this, I'm using a software
> package that stores all it's data in a database. It shows me the
> data, but I have no idea what tables and column names contain these
> values so it's hard to change the data through sql scripting.
>
> thanks a lot,
> Sean.
>

for dbf in `ls -1 *dbf`
do
strings ${dbf} | grep -l "the quick brown fox"
done

will quickly narrow down to files (tablespaces) for detailed search

On Feb 10, 12:40 am, seannakas...@yahoo.com wrote:
> How do I search the entire database for a value and return the
> tablenames and column names that contain this value? For argument
> sake, let's say I'm searching for the text "the quick brown fox".
>
> I think someone posted this question before but it never got any
> responses--since it's been awhile, i thought i might post it again.
>
> In case your wondering why I want to do this, I'm using a software
> package that stores all it's data in a database. It shows me the
> data, but I have no idea what tables and column names contain these
> values so it's hard to change the data through sql scripting.
>
> thanks a lot,
> Sean.

Another variant on Ana's answer is to export the schema and run grep -
b
for the string. The run grep -b for "CREATE TABLE" and you may have
enough information to figure out the table and column names.

Niall's and Rene's code are quite impressive, though, not to mention
already written.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniont...s_1b13ams.html

thanks for the grep options, that sounds like the fastest method.
i believe the databases are on windows xp machines though so hopefully i
can get the necessary files to my local pc where i can use cygwin's grep.