How to run stored procedures in database A while logged into database B?

HI,
I need some help setting up an SQL Server environment. I have a database that is chock full of my favorite utility procedures and functions that I use all the time at different client sites.  As it stands, in order to run those utilities, I install them in the client database and all is fine.  However, that creates some administrative/logistic headaches for me:
  --I install the procs/functions is with a CREATE PROC script, which means I need to maintain that every time I add a new procedure or one of them changes.
  --some clients understandably resist the intrusion of my scripts into their databases.  Frankly, I think it's an obnoxious practice myself.
  --purging the objects when I leave is a hassle

It would be much easier if I could just attach my database to the client's server and run the scripts from there:  1. Simple installation/de-installation  2. No introduction of my objects into the client database.

But, I can't get it work.  Suppose my utilities are in database U, which is attached to the client's server S with client database C.  I want to run my utilities in database U against the tables in database C.   I want to do this without explicitly naming database C in my utilities, since I want them to be portable.  

To simplify by example, see the code snippet.

When that runs, I want it to see the tables from C, but it shows the tables from U, instead.  In other words, my database context is the database where the proc is stored, not the one I am connected to or running against.

Specifically, my questions are:
1.  Is there something simple that I am overlooking?  Can I get my example to show the results from the context of the database C (without hardcoding the database name)?

2.  Is there a better way to accompish my goal of a easily porting a package of utilities? What's the best practice?





 

 


 



Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

use u
go
create proc dbo.ShowTables as select name from sys.tables
go
create table dbo.Utable (i int)
go
 
use c
go
create table dbo.Ctable (i int)
go
 
exec u.dbo.showtables
go
 
 
Name
-----------------------------------------
UTable