Using Multiple Databases in SQL

In SQL Server, you can have multiple databases on the same instance of SQL Server.  In SQL Server 2012, you can have over 32,000 (go HERE to read up more maximum capacity specifications for SQL Server) databases in one SQL Server instance.  So, the question is, how do you use/reference all these databases in the same query.

There are two main ways to accomplish this:

1. The use of USE
2. Reference the database in your FROM statement

First, we can use the word USE.  The general syntax for this is:

USE database

Where database is the database you are referencing.  If our Customer table was in the Billing database, it would look something like this:

USE Billing

SELECT *

FROM Customer

Using USE leaves you rather limited, though, because only the one database can be referenced at a time using USE.

Our other option is to specify the database in our FROM statement.  Let's say, for example, that we have our Customer table in the Billing database, while the Orders table is in the Shipping database.  If we wanted to use both tables in the same query, it would look like this

SELECT *

FROM Billing.dbo.Customer

LEFT OUTER JOIN Shipping.dbo.Orders

on Customer.CustomerNumber = Orders.CustomerNumber

In the above, we put the database, a dot (.), the user (in this case dbo, which means database owner), another dot (.) and then the table name.  Using this method, we can query multiple databases at the same time within the same query.

To read more on USE, go here: USE

To read more on selecting your database, go here: Select a Database