Find which column has the value

Question

Post reply

Find which column has the value

parthi-1705

SSCrazy Eights

Points: 9277
More actions
November 12, 2010 at 6:56 am

#231244

Hi
I am having a table with 50 columns i need will give input as 'Test'.I ned to find which column has 'test' as data in it
sample:
go
Create table temp (i varchar(10), j varchar(10))
declare @i int
set @i=1
While @i<=100
begin
Insert into temp
Select @i,'lin'+Convert(varchar,@i)
set @i=@i+1
end
Declare @input varchar(8000)
Set @input='lin5'
Declare @coalesce varchar(8000)
set @coalesce =''
Select @coalesce=COALESCE(@COALESCE,'',',')+COLUMN_NAME+' in ('''+ @input + ''')or ' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='temp'
if len(@COALESCE)>1 Select @coalesce=Left(@COALESCE,LEN(@COALESCE)-2) Select @coalesce
Exec ('Select * from temp where '+@COALESCE)
Drop table temp
-- Here lin5 is j,i need that here it is 2 columns so it is easy i am having 50 + columns how to get
Thanks
Parthi
Thanks
Parthi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

something like this?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

The code in the link searches all tables. You'll need to limit that to one table.

Pradeep Singh

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

ps. (11/12/2010)
something like this?
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
The code in the link searches all tables. You'll need to limit that to one table.

Can i have Code here not able to visit the site

Thanks

Parth

Thanks
Parthi

ps. One Orange Chip Points: 29422 More actions · Answer 3

i just accessed the site twice.

http://vyaskn.tripod.com

Pradeep Singh

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

ps. (11/12/2010)
i just accessed the site twice.
http://vyaskn.tripod.com

thanks i cant access i am having permission to access in my work place

It is urgent any one

Thanks
Parthi

ps. One Orange Chip Points: 29422 More actions · Answer 5

I cant paste the code. Not sure if it's considered a good practise to paste someone else's code elsewhere.

Pradeep Singh

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

ps. (11/12/2010)
I cant paste the code. Not sure if it's considered a good practise to paste someone else's code elsewhere.

I agree Otherwise let me have the attachement of the page

Thanks
Parthi