Change Script check if exists

• 
  Hi,
  
  In SQL Change  Automation for SSMS, after you generate the script you can verify it, this verification will fail if you have duplicate objects.
  
  There's also a drift check when you create a release using SQL Change Automation Powershell, which produces a drift report.

  Sergio R March 27, 2020 16:22

  - 0 +
• 
  To complement my previous answer if you ensure that your targets are only updated from SQL Change Automation you shouldn't run into any issues.
  
  There's also a Comparison option which adds existence checks if you generate the script by capturing it from the development database: 
  

  <!-- "Add object existence checks" SQL Compare option -->
  <SyncOptionIncludeExistenceChecks>False</SyncOptionIncludeExistenceChecks>

  https://documentation.red-gate.com/sca/developing-databases/concepts/advanced-concepts/comparison-and-script-generation-options
  

  Sergio R March 27, 2020 16:34

  - 0 +
• 
  You might also consider a snippet in SQL Prompt if you don't want something for every single object. This one will create a stub proc if it doesn't exist and then an ALTER will work. You could also change the logic to handle tables or use DROP IF EXISTS

  IF NOT EXISTS( SELECT name FROM sys.objects o WHERE o.name = '$myproc$')
   CREATE PROCEDURE $myproc$
  

    as

    begin

     select 1

   end

  
  

  
  

  way0utwest March 27, 2020 18:39

  - 0 +
• 
  Thank you Sergio!
  

  EricLamontagne March 27, 2020 18:39

  - 0 +

Add comment

Please sign in to leave a comment.