Change Script check if exists

EricLamontagne · March 26, 2020 3:38PM

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 · March 27, 2020 4:22PM

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 4:34PM

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:

<SyncOptionIncludeExistenceChecks>False</SyncOptionIncludeExistenceChecks>

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

way0utwest · March 27, 2020 6:39PM

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 · March 27, 2020 6:39PM

Thank you Sergio!

Change Script check if exists

Best Answers

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk