Quick and dirty suggestion..
Select * from
(Select Name , 'TableA' as TableName from TableA
union
Select Name , 'TableB' as TableName from TableB
union
Select Name , 'TableC' as TableName from TableC)
where Name = 'JQuery'
If this works for you you could always create a view out of the Union and select from that (assuming this isn't a one-off request)