Yes you would have to union them together and use AS statements to obtain consistent column names.
SELECT content_name AS searchable_field FROM contents
UNION SELECT category_name AS searchable_field FROM categories
UNION SELECT product_name AS searchable_field FROM products
Then you could do a search based on searchable field.
Either that or you could add three separate recordsets and three separate DataAssist search Server behaviors onto the page with three separate repeat regions.