Commit all objects with 'Add database USE statement'

a.higginsa.higgins Posts: 90 Bronze 2
in SQL Source Control
I was asked to ensure that the "USE Database" command be included at the top of all scripts in our database, and assumed that checking the "Add database USE statement" option at the top of the Comparison Options would allow me to commit all existing artifacts with the new change.

When I checked the option, however, the difference is not showing up in the list of differences on the "Commit" tab. Is there a way to force push all scripts to the repository so that the command is included?
Tagged:
· Share on Twitter

Answers

  • Kendra_LittleKendra_Little Posts: 139 Gold 3
    Hi @a.higgins,

    I'm afraid this setting doesn't do what you are looking for by design -- the setting is around deployment scripts. The object definitions stored in SQL Source Control are often different from the deployment script (for example, the a whole table's definition is stored, if it's deployed to a database where the table already exists, but is different, the deployment script will be some series of ALTER commands, etc.).

    If we stored a database use statement at the top of the scripts in version control, then it might produce unexpected results for users in some cases -- for example, if the user wanted to deploy the schema to an empty database with a different name for validation purposes, the script might make changes to a different database than they expected on that instance. 

    Cheers,
    Kendra
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    @a.higgins - The scripts managed by SQL Source Control aren't sql scripts in the traditional sense. They aren't meant to be run. They are just there to serve as a schema definition, and to provide object level history. I'm sure that the instruction to include "use database" at the top of all scripts was intended to apply to scripts that get executed, and these aren't in that category.


    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • a.higginsa.higgins Posts: 90 Bronze 2
    @Kendra_Little and @David Atkinson, thanks for your responses!  I believe that clarifies the difference between what I was trying to have the option do and what the option was actually doing.

    We were trying to satisfy a request from our data catalog team, who were attempting to import metadata from our source control repository into the Erwin product used to map dependencies between databases - they were having trouble tying definitions to the appropriate databases, and had hoped that a tweak to our source control commit options would give them the extra information they needed.  I'll let them know that isn't an option. 

    Again, thanks for your responses!
    · Share on Twitter
  • Kendra_LittleKendra_Little Posts: 139 Gold 3
    @a.higgins

    Thanks for the further info. 

    There might be a way to accomplish what you need. I think in your situation I might getting the latest copy of the code to a folder, and then use powershell or a similar scripting language to insert the needed USE database statements into the scripts before importing it into Erwin. They are simply stored as text files, so this may be possible to automate if it needs to be done on a regular basis.
    · Share on Twitter
Sign In or Register to comment.