By
Published: 04 Aug 2005
Is it possible to have a stored procedure in database 1 look at tables in database 2?
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.
Dig Deeper on Microsoft SQL Server Installation
If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started.
Continue Reading
Development expert explains the simplest way to store an image in SQL Server.
Continue Reading
Expert Andrew Novick determines whether a stored procedure is also a remote procedure call.
Continue Reading