Search database to find some particular value available in all tables


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

more ▼

asked May 14, 2010 at 09:59 AM in Default

avatar image

Naina 4
1 1 1 1

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")?

May 14, 2010 at 11:47 AM John Franklin

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.

May 14, 2010 at 12:12 PM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 14, 2010 at 12:35 PM

avatar image

15.6k 22 57 38

(comments are locked)
10|1200 characters needed characters left


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

more ▼

answered May 15, 2010 at 05:17 AM

avatar image

63 13 13 16

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.

May 15, 2010 at 10:53 AM John Franklin
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 14, 2010 at 09:59 AM

Seen: 2977 times

Last Updated: May 14, 2010 at 02:12 PM

Copyright 2018 Redgate Software. Privacy Policy