Prev: Any database diagramming tools out there ?
Next: Dynamic table comparision issue
From: if_investor on 22 Feb 2010 16:38
Hi,

In a 10g database, I am looking for all ways to list references
to a specific data string in any table of a specific schema, assuming
that the fields are all VARCHAR2.

For example, suppose that there is a schema named OLYMPICS.

That schema has many tables, and I am looking for all tables that
have a data value of 'Vancouver' (Hopefully, case insensitive), in
any field. The fields are all VARCHAR2.

So, the script will, hopefully, generate the following data. The most
important
info. is the tablename... I can live without the fieldname and value.
I would
prefer the case insensitivity, but I can live without that also.

Schema Table Field Data Value

OLYMPICS- HOST Currenthost vancouver
OLYMPICS- CITIES 2010host Vancouver

etc..


Thanks,
QZ
From: John Hurley on 22 Feb 2010 17:06
On Feb 22, 4:38 pm, "if_inves...(a)yahoo.com"
<basis_consult...(a)hotmail.com> wrote:

snip

> Hi,
>
> In a 10g database, I am looking for all ways to list references
> to a specific data string in any table of a specific schema, assuming
> that the fields are all VARCHAR2.
>
> For example, suppose that there is a schema named OLYMPICS.
>
> That schema has many tables, and I am looking for all tables that
> have a data value of 'Vancouver' (Hopefully, case insensitive), in
> any field. The fields are all VARCHAR2.
>
> So, the script will, hopefully, generate the following data. The most
> important
> info. is the tablename... I can live without the fieldname and value.
> I would
> prefer the case insensitivity, but I can live without that also.
>
> Schema       Table      Field                Data Value
>
> OLYMPICS- HOST     Currenthost      vancouver
> OLYMPICS- CITIES    2010host         Vancouver
>
> etc..
>
> Thanks,
> QZ

A bunch of dynamic SQL in a PLSQL procedure could get this done for
you ...

It kinda/sorta sounds an awful lot like a homework assignment for some
class to me.

Maybe a CURSOR loop thru USER_TABLES might be a starting point ...
From: joel garry on 22 Feb 2010 18:14
On Feb 22, 1:38 pm, "if_inves...(a)yahoo.com"
<basis_consult...(a)hotmail.com> wrote:
> Hi,
>
> In a 10g database, I am looking for all ways to list references
> to a specific data string in any table of a specific schema, assuming
> that the fields are all VARCHAR2.
>
> For example, suppose that there is a schema named OLYMPICS.
>
> That schema has many tables, and I am looking for all tables that
> have a data value of 'Vancouver' (Hopefully, case insensitive), in
> any field. The fields are all VARCHAR2.
>
> So, the script will, hopefully, generate the following data. The most
> important
> info. is the tablename... I can live without the fieldname and value.
> I would
> prefer the case insensitivity, but I can live without that also.
>
> Schema       Table      Field                Data Value
>
> OLYMPICS- HOST     Currenthost      vancouver
> OLYMPICS- CITIES    2010host         Vancouver
>
> etc..
>
> Thanks,
> QZ

See
http://asktom.oracle.com/pls/asktom/f?p=100:11:3980172595068555::::P11_QUESTION_ID:37336026927381#tom37395410204420

jg
--
@home.com is bogus.
http://jimhillmedia.com/blogs/jim_hill/archive/2010/02/09/what-happens-when-things-actually-do-sort-of-kind-of-go-horribly-wrong-at-a-disney-theme-park.aspx
From: if_investor on 23 Feb 2010 09:38
On Feb 22, 6:14 pm, joel garry <joel-ga...(a)home.com> wrote:
> On Feb 22, 1:38 pm, "if_inves...(a)yahoo.com"
>
>
>
>
>
> <basis_consult...(a)hotmail.com> wrote:
> > Hi,
>
> > In a 10g database, I am looking for all ways to list references
> > to a specific data string in any table of a specific schema, assuming
> > that the fields are all VARCHAR2.
>
> > For example, suppose that there is a schema named OLYMPICS.
>
> > That schema has many tables, and I am looking for all tables that
> > have a data value of 'Vancouver' (Hopefully, case insensitive), in
> > any field. The fields are all VARCHAR2.
>
> > So, the script will, hopefully, generate the following data. The most
> > important
> > info. is the tablename... I can live without the fieldname and value.
> > I would
> > prefer the case insensitivity, but I can live without that also.
>
> > Schema       Table      Field                Data Value
>
> > OLYMPICS- HOST     Currenthost      vancouver
> > OLYMPICS- CITIES    2010host         Vancouver
>
> > etc..
>
> > Thanks,
> > QZ
>
> Seehttp://asktom.oracle.com/pls/asktom/f?p=100:11:3980172595068555::::P1...
>
> jg
> --
> @home.com is bogus.http://jimhillmedia.com/blogs/jim_hill/archive/2010/02/09/what-happen...- Hide quoted text -
>
> - Show quoted text -

Hi,

Thanks to those who replied. I will check the AskTom script.
This is not a class assignment; I am trying to find references
to the server name in the database.


From: joel garry on 23 Feb 2010 12:37
On Feb 23, 6:38 am, "if_inves...(a)yahoo.com"
<basis_consult...(a)hotmail.com> wrote:
> On Feb 22, 6:14 pm, joel garry <joel-ga...(a)home.com> wrote:
>
>
>
> > On Feb 22, 1:38 pm, "if_inves...(a)yahoo.com"
>
> > <basis_consult...(a)hotmail.com> wrote:
> > > Hi,
>
> > > In a 10g database, I am looking for all ways to list references
> > > to a specific data string in any table of a specific schema, assuming
> > > that the fields are all VARCHAR2.
>
> > > For example, suppose that there is a schema named OLYMPICS.
>
> > > That schema has many tables, and I am looking for all tables that
> > > have a data value of 'Vancouver' (Hopefully, case insensitive), in
> > > any field. The fields are all VARCHAR2.
>
> > > So, the script will, hopefully, generate the following data. The most
> > > important
> > > info. is the tablename... I can live without the fieldname and value.
> > > I would
> > > prefer the case insensitivity, but I can live without that also.
>
> > > Schema       Table      Field                Data Value
>
> > > OLYMPICS- HOST     Currenthost      vancouver
> > > OLYMPICS- CITIES    2010host         Vancouver
>
> > > etc..
>
> > > Thanks,
> > > QZ
>
> > Seehttp://asktom.oracle.com/pls/asktom/f?p=100:11:3980172595068555::::P1...
>
> > jg
> > --
> > @home.com is bogus.http://jimhillmedia.com/blogs/jim_hill/archive/2010/02/09/what-happen...Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> Thanks to those who replied. I will check the AskTom script.
> This is not a class assignment; I am trying to find references
> to the server name in the database.

Don't know if this will work, it's just off the top of my head, but in
the past I've searched export files like so, kinda slow:

strings xyz.exp|awk '{ if (match($0,"CREATE TABLE")>0) {saveline=$0}
{if (match($0,"Abcdefg")>0) {print saveline}}}'|sort -u|more

It will miss it if the export breaks up the string Abcdefg, though, I
can't remember if exp does that in column data.

jg
--
@home.com is bogus.
http://www.ibtimes.com/articles/20100223/developers-arms-over-apples-restrictions.htm
 |  Next  |  Last
Pages: 1 2
Prev: Any database diagramming tools out there ?
Next: Dynamic table comparision issue