Manually edit the auto-generated object scripts without causing commit issues?

Uji2

I'm working with a test database and using a working folder as the repo. After I performed the first commit, I went into the folder and re-formatted the auto-generated table scripts. I then updated the table definition in ssms. Source Control picked up the table definition change, but throws an error on Commit because it thinks the change is out of date.

Even though I did not make any functional changes by manually editing the script (just pretty-printing) Source Control still doesn't like it. So is there any way to be able to manually edit the generated scripts and not throw Source Control out of whack? Or even better, push the changes in reverse - from script to db?

Uji2

Yeah it will work, but it's a bummer that the scripts themselves can't really be edited. It would be a really cool feature to be able to edit the scripts and have the ability in the commit dialogue to sync the db code with the script. So in the Commit screen where it has the Database Version -> Latest Source Control Version, it would be nice if you could reverse that arrow and push your changes the other way.

David Atkinson

Well.. I think you can do this, but just not for tables. You'll need to edit the comparison options in the Setup tab to uncheck "ignore white space" so that formatting changes are registered as differences. You can get do a get latest against your database to apply the formatting changes. If in the meantime you've changed the procedure you will be presented with a conflict, and you can solve this in the normal way by choosing one version or the other.

David Atkinson

It's just occurred to me that you're reformatting table scripts and not the programmable objects. The formatting of tables isn't saved by SQL Server so the formatting will always be reset each time the tables are scripted out. Your best bet is to leave them as they are and format them in SSMS as and when you need them, accepting that their formatting in version control will be out of your control.

Uji2

Ok, table scripts I can understand - no problem there. But what about stored procedures? I'm getting the same issue if I edit the generated proc script, compile it, then without committing right clicking on the proc in SSMS and going to Modify. If I compile those changes and THEN try to Commit I get an error "The list of changes to commit is out of date. To update the list, in the Commit tab, click Refresh". I get this error even though the Diff screen shows the proper changes between database and source control version.

David Atkinson

The reality is that the schema scripts were only ever designed to be modified by SQL Source Control. If you need to "fix" your installation, simply unlink and relink to your working folder. In future when you need to format your stored procedures, please do this in SSMS on your actual database objects and check these in. Is this something that will work for you?

Uji2

Now we're talking! Unchecking "ignore white space" did the trick with stored procedures. I was even able to edit the table scripts  - it didn't keep the formatting of the actual table definition but it kept the comments at the top and the if exists/drop statement.

David Atkinson

Glad it worked out for you. I had to do a fair amount of experimenting to work it out, but I'm glad I did as it felt like there had be a way to answer this question! I didn't think this would fix the table issue. That's an added bonus