finding a data in my db (700 tbl)

Hi
I have a db that contains ~700 tbl. I am looking for a data in my db.
I don t know which table and column i need to look in, bu i know the
data exist. Is there any way to search my whole db to find the data?
Thanks in advanceYou can search the web and find a script that does this (kondredi may have
one). There was a recent thread on the same issue.
You can also roll your own using sysobjects (type = 'u') and syscolumns and
dynamically create a bunch of select statements with where clauses using
LIKE %searcheddata%. If you are searching for numeric values it is a bit
easier since you can do a straight =.
--
TheSQLGuru
President
Indicium Resources, Inc.
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||On May 22, 11:20 am, esen...@.gmail.com wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
My first thought is to use a cursor to loop through your tables using
this query:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Then using dynamic sql, write a query to find your data.|||One way to approach this is to dump all the tables out via bcp to text files
and then search these text files. You can easily script to bcp the data out
of these 700 tables. I would bet this is easier and faster than an
in-database search if you absolutely have no idea where that piece of data
may be.
Linchi
"esengul@.gmail.com" wrote:
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>|||Hi
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
<esengul@.gmail.com> wrote in message
news:1179850821.259666.263880@.x18g2000prd.googlegroups.com...
> Hi
> I have a db that contains ~700 tbl. I am looking for a data in my db.
> I don t know which table and column i need to look in, bu i know the
> data exist. Is there any way to search my whole db to find the data?
> Thanks in advance
>sql