Search a query in multiple sql server table

Question

i have many tables that contains same column i want to search for a value and return all the line for example

Tab1

col1 col2 col3

val1   val2  val3

val7   val8  val9

Tab2

col1 col2 col3

val4   val2  val5

i want the sql syntax that return if i search in my java code for val2 the two lines

Tab1

col1   col2  col3

val1   val2  val3

and

Tab2

col1   col2  col3

val4   val2  val5

thank you

Welcome to stackoverflow. Please read [ask]. This is not a coding service but a Q&A website. — Zohar Peled, Jan 04 '17 at 09:21
I'd recommend researching [joins](https://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx). — David Rushton, Jan 04 '17 at 09:22
[This answer](http://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008) may be of use to you. — Tony, Jan 04 '17 at 09:30

Tim Biegeleisen · Answer 1 · 2017-01-04T09:32:29.583

0

You can try doing a UNION between two queries each of which selects the records you want:

SELECT col1, col2, col3, 'Tab1' AS table_name
FROM Tab1
WHERE col2 = 'val2'
UNION ALL
SELECT col1, col2, col3, 'Tab2'
FROM Tab2
WHERE col2 = 'val2'

If you want to search for val2 in any column, then you can use this:

SELECT col1, col2, col3, 'Tab1' AS table_name
FROM Tab1
WHERE col1 = 'val2' OR col2 = 'val2' OR col3 = 'val2'
UNION ALL
SELECT col1, col2, col3, 'Tab2'
FROM Tab2
WHERE col1 = 'val2' OR col2 = 'val2' OR col3 = 'val2'

edited Jan 04 '17 at 09:32

answered Jan 04 '17 at 09:22

Tim Biegeleisen

502,043
27
286
360

thank you but i had too much table i can't do it manually is there any procedure that search with information scheme – ANAS KAMIKA Jan 04 '17 at 09:25
I'd add an additional field to both selects to display the table name, such as `'Tab1' AS table_name` so you know which table matched the query. – Tony Jan 04 '17 at 09:26
@Tony Thanks for the input. Not sure if my answer is what the OP is after though. – Tim Biegeleisen Jan 04 '17 at 09:32

score 0 · Answer 2 · answered Jan 04 '17 at 10:07

you also can try sp_MSforeachtable and temporary table.

    CREATE TABLE #tt(col1 INT,col2 INT,col3 int)
    INSERT INTO #tt
    EXEC sp_MSforeachtable 'select  * from ? where col1=''1'' and OBJECT_ID(''?'') in (OBJECT_ID(''table1''),OBJECT_ID(''table1''),OBJECT_ID(''table1'')) ' 
    SELECT * FROM #tt

Search a query in multiple sql server table

2 Answers2