Is there a way to query 135 distinct tables in one single query?

Question

In our database, we have 135 tables that have a column named EquipmentId. I need to query each of those tables to determine if any of the them have an EquipmentId equal to a certain value. Any way to do this in a single query, instead of 135 separate queries?

Thanks very much.

Answer 1

You are looking at either Dynamic SQL to generate queries to all of the tables and perhaps union the results, or using something like the undocumented sp_MSforeachtable stored procedure.

sp_msforeachtable 'select * from ? where equipmentid = 5'

Answer 2

You could use a query to build a query:

select  'union all select * from ' + name + 
             ' where EquipmentId = 42' + char(13) + char(10) 
from     sys.tables

Copy the result, strip the first union all, and run the query :)