Q
Problem solve Get help with specific problems with your technologies, process and projects.

Stored procedures looking at different databases

Expert Andrew Novick reveals how to execute a store procedure in one database look at tables in another database.

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.
This was last published in August 2005

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close