rbarryyoung (8/5/2008)

---

Andras Belokosztolszki (8/5/2008)

---

One solution is to add the stored procedures to the master database, and add sp_ prefix to their names

(bad practise)

Well, this works in the sense that SP_FOO will find the proc in master.dbo, no matter what database you are in. However, it does NOT work in the sense that it will act like it is running in your current database. Like all other stored procedures (except the MS supplied Master.sys procs) Master.dbo.sp_* procs are complied and bound against the database that they are stored in, as the following demonstrates:

USE MASTER

Go

create proc sp_RBY_check_db_loc

as

Select distinct TABLE_CATALOG

From INFORMATION_SCHEMA.TABLES

Go

exec sp_RBY_check_db_loc

Select distinct TABLE_CATALOG

From INFORMATION_SCHEMA.TABLES

Go

USE model

Go

exec sp_RBY_check_db_loc

Select distinct TABLE_CATALOG

From INFORMATION_SCHEMA.TABLES

Go

As you will note, no matter which DB you USE, the "exec" always returns "master".