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".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]