[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

creating one central sp for multiple databases

Hi.  This is a  "related question" about a situation as follows:
I have multiple databases with identical structures.  I want to have one central stored procedure that can be used in any of my databases. What I would really want to do is have one sp and in it say "use DB1" or "use DB2" based on a parameter or something.  But the use statement cannot be used in an sp.

There is already a (Very long) post about this.  I am interested in this answer from creeds0001::
"... All   of that being said, put the stored porcedure in the MASTER database.  If you put them anywhere else calling them will cause them to use the data in their resident database.  The MASTER database is different.  You might want to give them some special kind of name to help keep track of them, but this will allow you to call the procedure useing your current databases data."

I don't understand how this works.  Based on the above post, I tried the following:  

create procedure MasterTestSP as
select * from mytable

I created the procedure MasterTestSP in the Master Database. Master db does not have  a  table "mytable".  
I went into one of the user databases, StandardDB which has a table "mytable" and ran
exec master.dbo.MasterTestSP

I got the message "Invalid object name mytable".
Which makes sense because it's looking at the master database, not at the child database.

So basically, I don't understand the post. How do I store one central sp in Master and point it to the calling database at runtime?


  • 2
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
try creating that on Master database and make that system procedure
EXEC sys.sp_MS_marksystemobject  MasterTestSP
AlizaNAuthor Commented:
use master
create procedure MasterTestSP as
select * from mytable

exec sys.sp_ms_marksystemobject mastertestsp

use simpledb
exec mastertestsp
"could not find stored procedure MasterTestSP"

exec master.dbo.mastertestsp
"invalid object name mytable"

what am i doing wrong? did i set it up correctly? how do i call it?

AlizaNAuthor Commented:
ok i got it you have to preface it with sp_ for some reason
sp_mastertestsp works
mastertestsp doesn't.

thanks so much, this is really terrific!
Anthony PerkinsCommented:
>>ok i got it you have to preface it with sp_ for some reason <<
Right.  You have just defined it as a system Stored Procedure, so it has to use the prefix sp_

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now