Search database to find some particular value available in all tables

Question

question

Naina 4 asked May 14, '10

Search database to find some particular value available in all tables

Hello,

Please let me know script or procedure to serach full database for a column value available in all tables.

sql sql-server-2000 server

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John Franklin commented · May 14, 2010 at 11:47 AM

Are you asking for a physical value search (i.e. - the word "apple" in any text column of any table) or for a listing of all tables that contain a similarly named column (i.e. - all tables that contain a column name like "fruit")?

0 ·

Oleg commented · May 14, 2010 at 12:12 PM

Please provide more details. Would you like to look for occurence of a specific string value across all suitable columns of all available tables in the database? If so I can write you a select statement generating the queries you need.

0 ·

Answer 1 · 2010-05-14T12:35:45Z

TimothyAWiseman answered May 14, '10

You may want to look at http://www.sqlservercentral.com/articles/Search/64877/ It is an article I wrote some time ago that addresses this topic.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-05-15T05:17:53Z

Naina answered May 15, '10

Hello,

Sorry for confusion..... Thanks for ur answers....

Im looking for a script or procedure for physical values serach.......

John u rite.....im looking for physical values serach.......

eg the word "apple" in any text column from all tables available in a database.

Thanks Geeta

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

John Franklin commented · May 15, 2010 at 10:53 AM

Tim's article is your solution... Just loop through the function example he has given and pass it each of your tables... He is using the newer INFORMATIONSCHEMA objects that I don't believe are supported in SQL 2000... However, you can substitute them for the similar SYSOBJECTS and SYSCOLUMNS tables that are supported in SLQ 2000.

2 ·

question