Remember to remove the last AND from the WHERE clause.

Can you run this code and post the results?

DECLARE @searchVar char(4)

SET @searchVar = '7'

SELECT '['+S.name+'].['+T.name+'].['+C.name + '] LIKE @searchVar ...

yes, I'm online now. messenger has you listed as offline.

I've modified the first query I sent to write your complex where clause.

DECLARE @searchVar char(4)

SET @searchVar = '7'

SELECT '['+S.name+'].['+T.name+'].['+C.name +...

Just for clarity's sake:

select distinct

table1.col1,table1.col2,table1.col3,

table1.col4,

table1.col5,table1.col6,table1.col7,

table1.col8,table1.col8,

table1.col9,table1.col10,table1.col11,table1.col12,

table2.col1,table2.col2,table2.col3,

table2.col4,table2.col5,

table3.col1,

table3.col2,table3.col3,table3.col4,table3.col5,table3.col6,

table4.col1,table4.col2,table4.col3,table4.col4,

table5.col1,table5.col2,table5.col3,

table6.col1,table6.col2,table6.col3,

table6.col4,table6.col5,

table7.col1,table7.col2,

table8.col1,table8.col2,

table9.col1,table1.col2

from

table1

left outer join table8

on table1.col1=table8.col1

left outer join table7

on table1.col1=table7.col1

left outer join table6

on table1.col1=table6.col1

left outer join table5

on table1.col1=table5.col1

left outer join table2

on table1.col1=table2.col1

left outer join table3

on table2.table2SN=table3.table2_ChildSN

left outer...

Ok. So you want to search every column right?

Use brucla's sys tables solution. e.g.

DECLARE @searchVar CHAR

SET @searchVar = 7

SELECT 'SELECT '''+T.name+''',['+C.name+'] FROM ['+T.name+'] WHERE ['+C.name + '] = '+...

This sounds doable.

Let's see if I understand your requirement first.;-)

You have 9 different tables. From the 9 tables you have 51 columns you want to search with a single...

I just recently found this thread when faced with a similar problem. Thanks to everyone that posted suggestions, solutions, and advice. It helped me discover I was facing...