Solved

How to reference multiple db's in T-sql

Posted on 2011-02-17
4
208 Views
Last Modified: 2012-05-11
I use one db for storing data, it is built from a restore of another db.  We'll call this db REPORT.  Another database I put my actual stored procedures and functions on. I'll call this one SPS

Typically this isn't an issue, we simply reference the appropriate db in the FROM clause, but I want to know how I can make it so I can have T-SQl pointed to look in specific places for functions.  For isntance, I don't want to have to type 'SPS.DBO.FUNCTIONNAME()' everytime I want to use one of those functions.  How can I make it so I just have to type the function name?
0
Comment
Question by:UnderSeven
  • 2
4 Comments
 
LVL 13

Expert Comment

by:devlab2012
ID: 34918082
you can use as three part name as DBName.Schema.Objectname. Objectname is your table or sp etc. You can simply ignore schema

e.g.

select * from MyDB..MyTable
0
 

Author Comment

by:UnderSeven
ID: 34918159
Is there anyway to include it like you would in a c language? So I can avoid writing anything but the function?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 34918162
In your code, you can use USE keyword. For functions, you should use 2-part naming conventions as dbo.functionName().

Use DBName
... select * from dbo.your_function()
exec your_sp
etc....
etc....

Open in new window

0
 

Author Closing Comment

by:UnderSeven
ID: 34933396
Thanks.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Read about achieving the basic levels of HRIS security in the workplace.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now