How do I query tables located in different database?

Question

My original question was about whether to keep separate ASPNETDB.MDF from the application database or merge all the tables in one database. Checking the previous questions/answers, I learned that it depends on whether the membership data would be shared across several applications.

Now, my question is this. In case I decide to keep ASPNETDB.MDF separate from the application DB, how can I query 2 tables located in 2 different databases?

Thanks for helping.

Answer 1

If you have two databases/schemas on the same database server, you can query across databases with the following syntax:

select *
from database1.dbo.table1 t1 join database2.dbo.table2 t2 on
  t1.field1 = t2.field2

If they are on physically separate servers, you can still do a cross-database query, but you need to link the servers first:

http://msdn.microsoft.com/en-us/library/aa213778(v=sql.80).aspx