CODEWITHSUNDEEP
sqlsql-servert-sql
Michael
Michael

Reputation: 2657

T SQL Select tables based on values from another Select

I have the below select that retrieves all tables that begin with 'ABC_':

SELECT *
FROM information_schema.tables
WHERE table_name  LIKE 'ABC_%'

All of the 'ABC_' tables contain a field called 'SKU'.

I have another SELECT that retrieves a list of SKUs:

SELECT SKU from tblProducts

How do I return a list of 'ABC_' tables that contain at least one SKU from tblProducts?

Upvotes: 1

Views: 117

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

A little dynamic SQL magic is all it takes:

Create a union all query with the tables you need, each of the inner queries with a where exists to make sure at least one row in them has a sku value that's also in the tblproduct table:

DECLARE @Sql nvarchar(max) = '';

SELECT @Sql = @Sql +N'UNION ALL SELECT '''+ table_name +N''' As TableName FROM '+ QUOTENAME(table_name) +
N' As P WHERE EXISTS (SELECT 1 FROM tblProducts WHERE tblProducts.Sku = P.Sku) '
FROM information_schema.tables
WHERE table_name  LIKE 'ABC_%'

-- Remove the first "UNION ALL"
SELECT @Sql = STUFF(@Sql, 1, 10, '')

-- When dealing with Dynamic SQL, Print is your best friend
PRINT @Sql

-- Unremark once you check the printed SQL is what you wanted
--EXEC(@SQL)

Upvotes: 2

Related Questions