March 14, 2014 at 5:13 pm
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.
March 14, 2014 at 6:52 pm
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.
-Shishir
March 15, 2014 at 4:13 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply