Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Deploying stored procedures to all databases. Expand / Collapse
Posted Friday, March 14, 2014 5:13 PM


Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 24, Visits: 232
Let me start with that I am an accidental DBA. I'm just curious how most deploy a stored procedure to multiple databases. For instance we have a server with roughly 70 databases. I have to create / alter stored procedures that exist in all 70 databases. So far I've been able to do this by using sp_msforeachdb and doing some dynamic sql. This works but is there a better way or a best practice? I've done some searching and don't see a clear answer but I'm sure this has to come up with other DBAs.
Post #1551411
Posted Friday, March 14, 2014 6:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 4, 2014 11:08 AM
Points: 7, Visits: 158
In my opinion there is no other better way. There is Central Management Server, but it will help you if you had multiple "servers" with multiple databases. If it's just one server with a lot of databases the method you are using works pretty well.

Post #1551423
Posted Saturday, March 15, 2014 4:13 AM



Group: General Forum Members
Last Login: Yesterday @ 12:29 PM
Points: 17,173, Visits: 32,140
Going to all databases on a server, you have to do some type of cursor. sp_msforeachdb is a good as anything.

If you have to go to lists of specific databases, cursors are still the way to go, but I'd move the cursor into PowerShell and make the calls that way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1551459
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse