Create a stored procedure on multiple databases

Question

Create a stored procedure on multiple databases

Guras

SSCertifiable

Points: 6518
More actions
August 29, 2013 at 12:37 pm

#277451

How can I create sps on multiple database?

Viewing 12 posts - 1 through 11 (of 11 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

Depending on the complexity of your procedure, something like this could work:D-E-C-L-A-R-E @Text varchar(1000)

SELECT @Text = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE YourProc AS <<Your fancy code here>>'') END'

EXEC sp_MSforeachdb @Text

Or you could download Idera's SQL admin Toolset, which includes Multi-Query...

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Guras SSCertifiable Points: 6518 More actions · Answer 2

Thank yoy for the reply, actually this is what I am trying to do. I have bunch of objects that I need to create on multiple databases. I was thinking of creating a script file and calling that from a stored procedure on every database? Does that make sense?

Jay980 SSC Veteran Points: 260 More actions · Answer 3

We use RedGate SQL Multi Script.

It works very well.

Guras SSCertifiable Points: 6518 More actions · Answer 4

Jay Purvis (8/29/2013)
We use RedGate SQL Multi Script.
It works very well.

Thank you and will take a look at it. I do not have multiservers. Just one server but many databases.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

Guras (8/29/2013)
Thank yoy for the reply, actually this is what I am trying to do. I have bunch of objects that I need to create on multiple databases. I was thinking of creating a script file and calling that from a stored procedure on every database? Does that make sense?

Yes this "should" work just fine

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ScottPletcher SSC Guru Points: 101103 More actions · Answer 6

I was thinking of creating a script file and calling that from a stored procedure on every database?

Yes, that should work, although it's a lot of extra work and hassle that's not really required to do what you want to do.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Guras (8/29/2013)
How can I create sps on multiple database?

This will sound like a silly question but I have to make absolutely sure, first... Will the stored procedure be [font="Arial Black"]exactly [/font]the same on all of the databases?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 8

Also, if adding the SPs to all databases on your instance, be sure to add them to the model database so you do not have to go through this process again when new databases are created.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 9

Personally, this sounds like a job for PoweShell.

You can easily walk through the databases on a server, or from a list you provide, and then run your T-SQL script on each. It's very simple to use the invoke-sqlcmd command to get this done. Here's the intro to managing databases through scripting. Here's an old, and probably kind of poor, example on my blog of walking structures[/url] using PowerShell..

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Guras SSCertifiable Points: 6518 More actions · Answer 10

SQLSteve (8/30/2013)
Also, if adding the SPs to all databases on your instance, be sure to add them to the model database so you do not have to go through this process again when new databases are created.

Yes it's the same sps .

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Guras (8/30/2013)
SQLSteve (8/30/2013)
Also, if adding the SPs to all databases on your instance, be sure to add them to the model database so you do not have to go through this process again when new databases are created.
Yes it's the same sps .

There will be a flurry of people that disagree with me but I will typically put common utilities in the Master database so that they're available to all databases. The really good thing about this is that you only need to change it in one place and the change will instantly be available in all the databases.

There are some caveats, though, and these are the reasons why some will strongly disagree with me.

1. The stored procedure name must start with "sp_" without the quotes. I take the extra step to make sure they look like they're "Microsoft Owned" so they can overwrite them should a CU or SP demand it. That never happens because of the next thing below...

2. The name should be very strongly named because Microsoft can add names that might match yours in which case, yours will lose the battle and be replaced if a CU or SP so deems it.

3. You MUST have any proc that you put in Master under very good source control because the entire master database could be replace by Microsoft at the drop of a hat, any hat. In fact, I maintain a "MasterProcs" script and, provided a CU or SP didn't overwrite my stored proc (it never does because of #2 above), I rerun the script after any and all updates just to be sure.

4. I wouldn't deploy whole systems this way because it becomes to unwieldy. If just one customer DB (for example) requires customization, it can become a real pain. It also causes some pretty severe bloat of the master database and will PO the resident DBA, even me. I only put simple common utilities in master.

If that sounds a little too frightening, then use the sp_MSForEachDB or dynamic SQL Solution. As for the PowerShell solution, I personally don't use it for such simple things as promoting code across databases especially since the MAX datatypes came out in 2005. Still, if that makes it easier for you, then I strongly recommend the method. Just don't ask me to write it for you. 😛

EDIT: added clarification as to what kind of procs I put in master.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)