x

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.

more ▼

asked May 14 '10 at 09:59 AM in Default

Naina 4 gravatar 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 '10 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 '10 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 '10 at 12:35 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

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

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

more ▼

answered May 15 '10 at 05:17 AM

Naina gravatar image

Naina
63 12 13 14

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 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x672
x472
x84

asked: May 14 '10 at 09:59 AM

Seen: 2490 times

Last Updated: May 14 '10 at 02:12 PM