Searching a keyword in multiple tables and returning which tables contain it

Question

A pretty common query to use is

SELECT distinct ex1.Column1
FROM ExampleTable1 as ex1
WHERE ex1.Column1 like '%KW1%'

Now this will return all the distinct values from that table, if it's there, but that keyword could be in a number of other tables, sometimes several at once. I was wondering If I could replicate this search to return which tables from a list I have contained this keyword in the desired column, then I can go on from there.

Right now I have to take this query copy and paste it several times just changing the table name to say ExampleTable2, ExampleTable3 and so on.

Answer 1

You still have to search each table with a distinct SELECT query, but you could just run it once and union the results together:

SELECT distinct 'ExampleTable1' as tablename, ex1.Column1
FROM ExampleTable1 as ex1
WHERE ex1.Column1 like '%KW1%'
UNION ALL
SELECT distinct 'ExampleTable2' as tablename, ex1.Column1
FROM ExampleTable2 as ex1
WHERE ex1.Column1 like '%KW1%'
UNION ALL
SELECT distinct 'ExampleTable3' as tablename, ex1.Column1
FROM ExampleTable3 as ex1
WHERE ex1.Column1 like '%KW1%';

That will spit the distinct tablename in the first column that the distinct Column1 value was found.