deploy SP to multiple database

• kcj

  Mr or Mrs. 500

  Points: 540

  More actions

  February 20, 2015 at 9:07 am

  #295438

  Is there anyway I can use powershell to deploy a SP to Multiple dabases in same server.

  I have to depoly the same SP to 50+ databases which I can get the list from sys table in the same server.I am wondering if i can do it using powershell to save deployment time? i am using sql server 2008 r2 and 2014

• Lowell

  SSC Guru

  Points: 323527

  More actions

  February 20, 2015 at 9:13 am

  #1778977

  i would custom cursor for specific databases, sicne you wouldn't deploy to model/master/ReportServer etc.

  it's actually a specific list, or some sort of pattern right?(all databases with 'APP_' or something like that?)

  USE MASTER

  declare

  @isql varchar(2000),

  @dbname varchar(64)

  declare c1 cursor for

  select name from master.sys.databases

  where name not in ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')

  and (recovery_model_desc <> 'SIMPLE' OR is_auto_close_on =1)

  open c1

  fetch next from c1 into @dbname

  While @@fetch_status <> -1

  begin

  begin

  select @isql = 'CREATE PROCEDURE @dbname.dbo.X'

  select @isql = replace(@isql,'@dbname',quotename(@dbname))

  print @isql

  exec(@isql)

  fetch next from c1 into @dbname

  end

  close c1

  deallocate c1

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738337

  More actions

  February 20, 2015 at 11:48 am

  #1779032

  I'd use SQLCMD and run a .SQL file against multiple databases/instances.

  I like out of SQL Server processes for this, because I want to deploy easily, or push out, without being depending on a db being on an instance. I have moved them before for load/consolidation issues, and I like flexibility.

  SQLCMD -s instance -d database -i mysql.sql

  Use a loop or parameters, or even just Excel to build a series of these for the files you need to deploy.