>I'm currently looking for the best way to search across multiple tables and databases.
You use a union query to join the results from multiple queries from different tables. For example:
Employees Table
1 Peter
2 Paul
3 Mary
Customers Table
1 John
2 Ringo
3 Paul
SELECT 'Employee' AS what, id, name
FROM Employees
WHERE name ~* '^P'
UNION
SELECT 'Customer' AS what, id, name
FROM Customers
WHERE name ~* '^P'
ORDER BY name;
Would return something like:
Employee 2 Paul
Customer 3 Paul
Employee 1 Peter
I can't tell you how you would do something like this accross multiple databases. There may be better ways...