Change Script check if exists

EricLamontagne

Hello,

When generating a change script, there is no safeguard to check if the affected object already exists or not.

Is there an option I can set so it does a check before executing the change?

I understand I can add the check my self, but I'm kind of lazy you know

Eric

Sergio R

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

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

way0utwest

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

EricLamontagne

Thank you Sergio!