Stored procedures looking at different databases

Yes, it certainly is possible, easy in fact. The way you reference data in a separate database is with a three-part name. The three parts are the database name, the owner or schema name and the table name. So if your stored procedure is running in the northwind database and you need to access the authors table in the Pubs database you would refer to it as pubs.dbo.authors. Here's an example in a stored procedure:

use northwind
go

CREATE PROCEDURE dbo.usp_Example_CrossDatabase AS

select *
from pubs.dbo.authors
go

EXEC dbo.usp_Example_CrossDatabase
GO

 

---

Do you have comments on this Ask the Expert Q&A? Let us know.