SQL statement to select from 2 different tables, from two different databases (same server)

Question

How do I select from multiple tables in different databases on the same server?

Also, Is there a way to have an identifying marker so I could see where the results came from?

So for example:

SELECT db1.table1.name, db2.table2.name, fromTbl
FROM db1.table1, db2.table2
WHERE db1.table1.name LIKE '%j%' OR db2.table2.name LIKE '%j%'

So in this case, I'm selecting the names from 2 different databases and tables. I'm doing a wildcard search on those names and the fromTbl would let me know where the results came from?

4    john smith    4    555.555.5555    table1
17   joe schmoe    17   555.555.5555    table2
11   james doe     11   555.555.5555    table1

I'm using SQL Server 2005.

Answer 1

You could use a UNION ALL and add in the database name like:

SELECT [columns_list], 'db1.schema.table1.name' AS [fromTbl]
FROM db1.schema.table1
WHERE db1.schema.table1.name LIKE '%j%' 
UNION ALL
SELECT [columns_list], 'db2.schema.table2.name' AS [fromTbl]
FROM db2.schema.table2
WHERE db2.schema.table2.name LIKE '%j%'

This will only work if the columns in the tables have the same column types (as your example suggests) else UNION will not work.