question

Naina 4 avatar image
Naina 4 asked

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.

sqlsql-server-2000server
2 comments
10 |1200 characters needed characters left characters exceeded

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

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 Likes 0 ·
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 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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 characters needed characters left characters exceeded

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

Naina avatar image
Naina answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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