Lookup specific string in all-tables

Hi Dave,

May you give me an example of how to do that?

Eric

On 12$B7n(B12$BF|(B, $B2<8a(B10$B;~(B15$BJ,(B, David Kerber
wrote:
> In article <f5a755b8-feed-4c2b-a53c-
> 44e02086b... RemoveThis @e10g2000prf.googlegroups.com>,
> says...
>
> > Hi I am new to sybase,
>
> > May I know how I can find a specific string (filename in one of the
> > column in one of the table) in a database in which the table's name is
> > unknown?
>
> > I've read a simular article, saying that the only way to do this is to
> > open a cursor and run through the sysobjects table.
>
> That's pretty much it. Get a list of the user tables and check each of
> them for the piece of data you're looking for. If you don't know the
> field name either, then you'll also need to scan through the fields list
> for each table.
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
 >> Stay informed about: Lookup specific string in all-tables 

On Dec 12, 8:15 am, David Kerber
wrote:
> In article <f5a755b8-feed-4c2b-a53c-
> 44e02086b... RemoveThis @e10g2000prf.googlegroups.com>,
> says...
>
> > Hi I am new to sybase,
>
> > May I know how I can find a specific string (filename in one of the
> > column in one of the table) in a database in which the table's name is
> > unknown?
>
> > I've read a simular article, saying that the only way to do this is to
> > open a cursor and run through the sysobjects table.
>
> That's pretty much it. Get a list of the user tables and check each of
> them for the piece of data you're looking for. If you don't know the
> field name either, then you'll also need to scan through the fields list
> for each table.
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).


Depending on the size of the database it may be easier to:

1) Dump your data to flat files using, e.g. dbschema.pl & bcp.
2) "grep" / "find" the search string in the dumped data.

Keith
 >> Stay informed about: Lookup specific string in all-tables 

Thanks Keith, using the combine of BCP and GREP shall also do the
trick. Just one more question, regarding to your valuable solution.
What about the table size is huge, would BCP still gonna be the
suitable way to go?

(Sorry that it should be a different topic)

Eric.

On 12$B7n(B13$BF|(B, $B2 wrote:
> On Dec 12, 8:15 am, David Kerber
>
>
>
>
>
> wrote:
> > In article <f5a755b8-feed-4c2b-a53c-
> > 44e02086b....DeleteThis@e10g2000prf.googlegroups.com>,
> > says...
>
> > > Hi I am new to sybase,
>
> > > May I know how I can find a specific string (filename in one of the
> > > column in one of the table) in a database in which the table's name is
> > > unknown?
>
> > > I've read a simular article, saying that the only way to do this is to
> > > open a cursor and run through the sysobjects table.
>
> > That's pretty much it. Get a list of the user tables and check each of
> > them for the piece of data you're looking for. If you don't know the
> > field name either, then you'll also need to scan through the fields list
> > for each table.
>
> > --
> > Remove the ns_ from if replying by e-mail (but keep posts in the
> > newsgroups if possible).
>
> Depending on the size of the database it may be easier to:
>
> 1) Dump your data to flat files using, e.g. dbschema.pl & bcp.
> 2) "grep" / "find" the search string in the dumped data.
>
> Keith- $Bp,i6Ho0zMQJ8;z(B -
>
> - $Bp}<(Ho0zMQJ8;z(B -
 >> Stay informed about: Lookup specific string in all-tables 

Thanks Dave, as long as I can find all the tables' name from
sysobjects, I will be able to write a script and BCP all the tables.
That shall be a lot easier and relatively not time consuming.

Eric.

(PS, I will definitely try combining sp_alltables_rowcount (search
from internet, it gave me an idea of using cursor) and sp_columns
(returns all the column) together)


On 12$B7n(B13$BF|(B, $B2<8a(B9$B;~(B59$BJ,(B, David Kerber
wrote:
> In article <2ae0000d-cb87-4dac-b1d0-47afd911daa9
> @e6g2000prf.googlegroups.com>, says...
>
> > Hi Dave,
>
> > May you give me an example of how to do that?
>
> I would have to do as much research as you would, since I've never had a
> need to do this. Check the help contents of your DB version for the
> names and layouts of the system tables, and that should get you started.
>
> ....
>
> --
> Remove the ns_ from if replying by e-mail (but keep posts in the
> newsgroups if possible).
 >> Stay informed about: Lookup specific string in all-tables 

In article <2ae0000d-cb87-4dac-b1d0-47afd911daa9
@e6g2000prf.googlegroups.com>, says...
> Hi Dave,
>
> May you give me an example of how to do that?

I would have to do as much research as you would, since I've never had a
need to do this. Check the help contents of your DB version for the
names and layouts of the system tables, and that should get you started.

.....

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
 >> Stay informed about: Lookup specific string in all-tables 

On Dec 13, 9:26 am, Eric Fung wrote:
> Thanks Keith, using the combine of BCP and GREP shall also do the
> trick. Just one more question, regarding to your valuable solution.
> What about the table size is huge, would BCP still gonna be the
> suitable way to go?
>
> (Sorry that it should be a different topic)
>
> Eric.
>
> On 12$B7n(B13$BF|(B, $B2 wrote:
>
> > On Dec 12, 8:15 am, David Kerber
>
> > wrote:
> > > In article <f5a755b8-feed-4c2b-a53c-
> > > 44e02086b....DeleteThis@e10g2000prf.googlegroups.com>,
> > > says...
>
> > > > Hi I am new to sybase,
>
> > > > May I know how I can find a specific string (filename in one of the
> > > > column in one of the table) in a database in which the table's name is
> > > > unknown?
>
> > > > I've read a simular article, saying that the only way to do this is to
> > > > open a cursor and run through the sysobjects table.
>
> > > That's pretty much it. Get a list of the user tables and check each of
> > > them for the piece of data you're looking for. If you don't know the
> > > field name either, then you'll also need to scan through the fields list
> > > for each table.
>
> > > --
> > > Remove the ns_ from if replying by e-mail (but keep posts in the
> > > newsgroups if possible).
>
> > Depending on the size of the database it may be easier to:
>
> > 1) Dump your data to flat files using, e.g. dbschema.pl & bcp.
> > 2) "grep" / "find" the search string in the dumped data.
>
> > Keith- $Bp,i6Ho0zMQJ8;z(B -
>
> > - $Bp}<(Ho0zMQJ8;z(B -


You can bcp the rows out of the table, no matter how big it is AFAIK.
Things to look out for:

1) Make sure you have space. The bcp file for a larger table might be
as large as the dataspace for the table, in my experience.
2) Time. Although bcp is very fast, it still takes time.
3) Filesize limitation in your filesystem. Know what it is. Make
sure you don't have ulimit set. You can get around this by
bcp'ing out the data in separate files, but that' s more work.

It sounds like you've got some good help in the right direction: doing
it in-db. I would consider this a last resort.

Keith
 >> Stay informed about: Lookup specific string in all-tables