Does DropAndCreateInsteadofAlter work for CREATES also?

fpdave100fpdave100 Posts: 12 Bronze 1
in SQL Compare
We have a scenario where the target db might already have the new SP (done as an emergency fix), but the scripts generated dont know that (they are generated between 2 versions of the schema from TFS), so they use a CREATE.

Will DropAndCreateInsteadofAlter also work for CREATE, or if not is there an alternative?

NB.  This is from the command line
Tagged:
· Share on Twitter

Answers

  • David_DDavid_D Posts: 26 Bronze 2
    Hi @fpdave100,

    Thanks for reaching out! You've mentioned SP, are you referencing stored procedure? Also, can you help to clarify what you are trying to achieve with SQL Compare or what is the exact issue?

    Regarding the command DropAndCreateForReRunnableScripts (Legacy aliases: DropAndCreateInsteadOfAlter), SQL Compare replaces ALTER statements with CREATE or DROP statements so I'm not sure if there will be any impact running against CREATE statements. 
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    In H1 2021 we're considering adding an option for SQL Compare to allow the use of CREATE OR ALTER for "programmable objects" (eg functions, procs, triggers) as an alternative to CREATE and ALTER statements, which is a feature that would work only in SQL Server 2016 SP1 and above. 

    Would this be useful to you? (and what version of SQL Server are you using?)
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • fpdave100fpdave100 Posts: 12 Bronze 1
    We are on a range of SQL Server versions, but general keep a year behing microsoft releases, so will be on at least that for recent deployments, and my team have been using that for some internal deployments (eg to test department) also.

    I have done some testing, and even if its creating a sproc, with the DropAndCreateInsteadOfAlter option it now does IF EXIST -> DROP -> CREATE, which is fine for me.

    The reason we wanat it is not so much for re-runnable scripts, but that someone may have put that sproc on to a server already as a out of version hot fix (yes, horrid!!), and so it would otherwise fail.  Also, we definitely want to override the current version with out proper version.
    · Share on Twitter
Sign In or Register to comment.