Scripts for User Defined Type Changes
alecl
Posts: 22
I made a change to a user defined type that I'm using as a TVP on some stored procedures. Sql Compare is finding the difference in the UDT and trying to do a DROP/CREATE, but it's failing because those Stored Procedures reference it. In this case I feel it should be detecting those SP's need to be dropped/recreated without me checking "Synchronize all dependencies" which I never turn on because I've seen it do unexpected things that I didn't want. If you change something in the PK it's smart enough to deal with the chain of indexes/constraints without that checkbox and this seems like the same situation
Comments
Indexes/constraints aren't considered objects in their own right in SQL Compare, and are treated as part of the table object. This is probably why their dependencies are taken into account without using the 'include dependencies' option.
When you don't use the 'include dependencies' option, then only the objects selected for synchronization will be considered and any other unselected objects will not be brought back into the project.
Does it pick up the dependency to the stored procedure if you 'include dependencies'?
Yes, but I've found that can sometimes add unexpected and dangerous changes to a deploy script so I never include that. I wish I had a good example of why I'm afraid to do that. I came to that conclusion literally years ago and I don't recall what led me to it.
It seems like in a situation where the change script will outright fail and with something as innocuous as just recreating the SP's that this situation should be handled without having to check that checkbox for include dependencies.
The reason for having the option to not include dependencies is basically so users can choose that unselected objects are not brought back into the project under any circumstances.
If you need the tool to help bring any connected objects back into the project, then that's when the 'include dependencies' option should be used. If it's performing dangerous or undesirable actions, then that's the issue we need to look into.
A lot had changed in the tools dependency engine in the last few years, so you might find it reliable now.