How to execute SP from master db in current db

Question

Post reply

How to execute SP from master db in current db

Aaron N. Cutshall

SSCrazy Eights

Points: 9015
More actions
December 6, 2007 at 8:36 am

#201533

I have a stored procedure that I would like to be able to call from any database on my server so I have it stored in the master database. When I want to execute it, I call it using the following syntax:
EXEC master.dbo.sp_ResetDB.
My problem is that even though I am invoking it from my target database, it only executes within the master database. I considered passing the calling database in as an argument, but I cannot switch databases with the USE statement. Is there an alternative?
The stored procedure uses the INFORMATION_SCHEMA views to obtain data about the current database. If I define the SP within the current database and execute it, everything's OK, but I don't want to have a copy of the same SP within every database.
Thanks for your help,
Aaron
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tim Januario SSChasing Mays Points: 609 More actions · Answer 1

Have you tried dynamic sql:

create procedure someprocedurename

(@dbname sysname)

as

begin

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT * FROM [' + @dbname + '].INFORMATION_SCHEMA.CHECK_CONSTRAINTS'

EXEC sp_executesql @sql

end

Aaron N. Cutshall SSCrazy Eights Points: 9015 More actions · Answer 2

I was trying to avoid that if possible because the entire SP relies on being in the correct database. If it's not possible to switch the database within an SP, is it possible to execute an SP within the context of the current database yet resides in the master database?

If I'm in the Northwind database and call an SP that resides in master, I would use the following syntax:

EXEC master.dbo.MyStoredProcedure

But, unfortunately, the database context for the execution of the SP changes to master and not Northwind. Am I just missing something?

Aaron

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Have you tried executing like this: exec sp_ResetDB

Aaron N. Cutshall SSCrazy Eights Points: 9015 More actions · Answer 4

I just did and you wouldn't believe what I discovered! :hehe: Apparently, if the SP begins with anything else other than "sp_", then I can't run it in the current DB context. Because one of my other SP began with something else, it failed and so I assumed that the sp_ResetDB would also fail.

Thanks for your simple suggestion that I obviously overlooked.

LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Actually, since your stored proc sp_ResetDB, was created in the master database, by just saying EXEC sp_ResetDB, SQL Server looked first in the master database and finding it there ran it in the database where you issued the EXEC command.

This is why you should not name stored procedures in user databases with the sp_ prefix. They may work, but if Microsoft, or some one else were to create a stored procedure in master with the same name, your stored proc in the user database will no longer get executed.

😎