August 30, 2010 at 12:43 pm
HI,I have an issue in Sql Server 2005.I want to create a common search like type any word in the box and it will find out these word in selected 9 to 10 tables and gives me return as a row with their column name and column data with table.I have a stored procedure which connects this 9 table with 51 different columns.So on the basis of this search it will display the number of rows.I want to know how do i write query for this with stored procedure.
Please reply me at hozefaunwala@hotmail.com.
If you can do this,I appreciated your help.Thanks.
August 31, 2010 at 7:55 am
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 value. The result set should be rows containing the name of the column in which the search value was found and the search value itself from ANY of the 51 columns?
Can you share the code you have used to JOIN your 9 tables?
August 31, 2010 at 9:06 am
Do all the tables have different structure?
Dynamically create your queries using sys.tables and sys.columns.
You'll probably have to do something like this.
SELECT
CASE WHEN COL1 = @KEYWORD THEN COL1 + '|TABLE1.COL1'
WHEN COL2 = @KEYWORD THEN COL2 + '|TABLE1.COL2'
...
END
FROM TABLE1
Where the colname and table name are from the sys views.
You'll have to parse the returned value by '|'
August 31, 2010 at 9:17 am
Thanks for your reply But I have 9 to 10 tables and almost each table has 10 to 12 columns. So just think how much big query it is? and What about the performance when it's going to execute.
August 31, 2010 at 9:26 am
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 + '] = '+ @searchVar FROM sys.columns C
INNER JOIN
sys.tables T
on
C.object_id=T.object_id
WHERE
(T.name IN ('T1','T2','T3','T4'))
August 31, 2010 at 9:27 am
Thanks.This is my join
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 join table3
on table2.table2SN=table3.SERIALNUMBER
left outer join table4
on table1.col1=table4.col1
left outer join table9
on table1.col1=table9.col1ID
Now what i want to do is search a value and at the end of this join I will put as a column name and column value with where condition.
August 31, 2010 at 9:29 am
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 join table3
on table2.table2SN=table3.SERIALNUMBER
left outer join table4
on table1.col1=table4.col1
left outer join table9
on table1.col1=table9.col1ID
August 31, 2010 at 9:32 am
Are you online right now? my hotmail id is hozefaunwala@hotmail.com.If possible I want to do chat with you.Thanks
August 31, 2010 at 9:37 am
It make query for each column.I don't want this thing.
Eg. If I want to search computer from selected 9 to 10 tables. It can be anywhere like any columns.How can I?
The result you sent me is just make a query.
Thanks.
August 31, 2010 at 9:48 am
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 + '] = '+ @searchVar + ' OR' FROM sys.columns C
INNER JOIN
sys.tables T
on
C.object_id=T.object_id
INNER JOIN
sys.schemas S
ON
S.schema_id = T.schema_id
WHERE
(T.name IN ('T1','T2','T3'))
August 31, 2010 at 11:16 am
This query works but My search would be anything.for e.g. my data is white and my search is just wh then "=" is not worked so i think i need to put like operator but when i put it then it gives me an error invalid column name.Can you help me?
Thanks for all your reply.
August 31, 2010 at 11:45 am
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 AND' FROM sys.columns C
INNER JOIN
sys.tables T
on
C.object_id=T.object_id
INNER JOIN
sys.schemas S
ON
S.schema_id = T.schema_id
WHERE
(T.name IN ('T1','T2','T3'))
August 31, 2010 at 12:32 pm
Thanks for your help.I have made little bit change in your query.
1) I pur "or" as against "and"
2) If my search is with int datatype then your query is perfect but if my search is with varchar data type then it gives me an error that is invalid column so I just change your query with quotation marks.
Final query is just like this.
Create procedure [dbo].[SearchAllTables_SecondStyle]
@searchVar varchar(max)
as
SELECT '['+S.name+'].['+T.name+'].['+C.name + '] LIKE '''+@searchVar+''' or' FROM sys.columns C
INNER JOIN
sys.tables T
on
C.object_id=T.object_id
INNER JOIN
sys.schemas S
ON
S.schema_id = T.schema_id
WHERE
(T.name IN ('T1','T2','T3','T4'))
Finally you gave me the path to reach my way.Thanks a lot. Keep in touch.Bye
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply