3

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
Damien
  • 4,093
  • 9
  • 39
  • 52
  • [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Feb 03 '15 at 16:46
  • like is a worse habit than select a,b,c from d join e on d.x = e.x and its use is discouraged. Damien you should use >= "j" for searches. – danny117 Feb 03 '15 at 17:12
  • you lost me there danny... what if i'm doing a search for say, United Parcel, what then? – Damien Feb 03 '15 at 17:52

3 Answers3

3

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.

Nick Sandel
  • 102
  • 7
  • How would i go about searching 3 different tables? Do I use union twice? – Damien Feb 03 '15 at 16:41
  • Yes you can use union many times, apparently there is no limit: http://stackoverflow.com/questions/6676970/sql-server-whats-the-limit-on-number-of-unions – Nick Sandel Feb 03 '15 at 16:44
1

Doing a union seems like your best bet here. A union will combine the results of two queries.

select name, 'table1' as fromTbl
from db1.schema.table1
where name like '%j%'

union --or union all depending on what you want

select name, 'table2' as fromTbl
from db2.schema.table2
where name like '%j%'
Becuzz
  • 6,846
  • 26
  • 39
  • How would i go about searching 3 different tables? Do I use union twice? – Damien Feb 03 '15 at 16:39
  • You should also think if you need to use "union" or "union all". The difference is that union tries to remove duplicate rows from different queries. – James Z Feb 03 '15 at 16:42
-1

try this: SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YOUR SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a UNION SELECT * FROM OPENROWSET('SQLNCLI', 'Server=ANOTHER SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a

Langelihle Kwinda
  • 26
  • 1
  • 6