Create A Stored Proc Across All DBs Where That DB Exists in An Avail Group

• gs1975

  Ten Centuries

  Points: 1057

  More actions

  September 2, 2019 at 10:26 am

  #3676301

  I have written some dynamic SQL that executes a stored procedure in all databases it exists in.

  I now need to Create A Stored Proc Across All DBs Where That DB Exists in An Availability Group.

  Ideally I would like to execute the stored proc if it already exists, and create the stored proc if it doesn't exist.

  Thanks in advance for any help.

  Kind regards,

  George

• as1981

  SSCrazy

  Points: 2806

  More actions

  September 2, 2019 at 11:25 am

  #3676308

  I can't give you a full answer but for identifying if the database is part of a group maybe you can query sys.dm_hadr_database_replica_cluster_states (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-availability-group-states-transact-sql?view=sql-server-2017) using the database_name field in a where clause? I think you should get an empty result if the server isn't part of an availability group.

   

• gs1975

  Ten Centuries

  Points: 1057

  More actions

  September 3, 2019 at 9:52 am

  #3676810

  Hi as1981.

  Thank you for your response. I have tried that and that is good for that part of the query.

  I shall try and work on the rest of it.

  Thanks again.

  George

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728164

  More actions

  September 4, 2019 at 9:06 am

  #3677119

  CREATE OR ALTER should be your syntax in 2016+

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com

• September 4, 2019 at 9:20 am

  This was removed by the editor as SPAM