Sync Script Generation with validation

sarathi.vasudevan@verint.

Hi,

Did this SQL compare 8 version supports check for existence and create or modify or drop objects?

This will be very useful, if you run the script again and again.

Thanks in advance.

Example:

---

if exists (select 1 from information_schema.columns
where table_name = 'TABLENAME' and column_name = 'COLUMNNAME')
begin
print 'Column NAME already exists in table TABLENAME. Not added in this migration...'
end
else begin
print 'Adding column COLUMNNAME to table TABLENAME...'
alter table TABLENAME
add COLUMNNAME int NOT NULL
CONSTRAINT CONSTRAINTNAME DEFAULT -1
end
go

---

sebling

An option to generate if exists drop then create for triggers, procedures, functions, etc.
The compare to script option writes the script as create procedure, etc.

I love your tools - keep up the good work!

David Atkinson

We may consider this feature at some point, but the correct use of SQL Compare is to generate a script for a specific upgrade that will run in its entirety. If another upgrade is required, the script should be generated again, rather than trying to use a previously generated script which would of course could well be unreliable.

It would be useful to know under what circumstances you would need to re-run the script?

Kind regards,

David Atkinson
Product Manager
Red Gate Software

sarathi.vasudevan@verint.

Thanks David for the reply.

The reason we need this feature is, we generate the migration script from this tool SQL compare.

We have individual migration files for each version of our schema. In few migration files, the changes are repeated due to some reason (this can be avoided).

If we have this validation and creation feature will help us not to do manual massage after the script generation. This will save lot of our development timings.

-- Sarathi

David Atkinson

Thanks for the explanation. How do the duplicate changes enter into the script?

David

sarathi.vasudevan@verint.

Duplicates are coming due to multiple parallel developments are happening at the same time frame. Schema versions needs the change, both migration scripts has it and first one will not have an issue while creating the object or column, the second one (latter version) needs to validate and if the object is not there or column is not created before then create the object or column.

I hope you understand what I am trying to explain.

Basically the solution is, if the migration file has the repeated same statements (generally this situation will not happen, but if 2 or more modules are working in parallel and trying to create same object). This can be avoided if we have the “if exists” validation statement.