Dependencies on User Defined Table Types

debrydebry Posts: 4 New member
User Defined Table Types cannot be altered directly. First, all the dependencies on that type need to be dropped, then the table type is altered, then the dependencies are re-created. It's an annoying limitation of Microsoft SQL.

The way to make this work well in SQL Compare is to check the box that says "Deploy all dependencies". This works in most cases, but we occasionally see objects in the list that are not true dependencies. Specifically, we are seeing tables in the dependency list when only User Defined Table Types are selected for deployment. This causes problems with our deployments.

It seems that only procedures or functions should be dependencies of User Defined Table Types. Is this accurate? Why would tables show up in the list? Thanks.
Tagged:

Answers

  • Alex BAlex B Posts: 1,131 Diamond 4
    edited June 5, 2020 11:09AM
    Hi @debry,

    In theory they would only be included if they were a part of a dependency chain. Maybe the table use a function that uses the UDTT?  So table needs to drop, so function can drop so UDTT can drop.

    Whether that's true in this case I obviously can't say, but to investigate further we would likely need the source and target object creation scripts that are being deployed, as well as the SQL Server version and the SQL Compare version so we can try to reproduce the issue here.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • debrydebry Posts: 4 New member
    edited June 5, 2020 3:49PM
    I don't think it's possible for a table to have a default constraint or a calculated field based on a function that takes a table-valued parameter as an input. How would the UDTT be initialized?

    I'll see if I can re-create the scenario, but either way, one solution to this problem would be to allow users to select / deselect specific objects in the dependency list. That way it's not an "all or nothing" feature. Has this been proposed before?
  • Alex BAlex B Posts: 1,131 Diamond 4
    I've been trying to recreate something like this and I can get a function to include a UDTT but I can't actually get it to use it in any meaningful way or have the table show up as a dependency when changing the UDTT in the function used in the DEFAULT.

    The option to select the dependencies has be suggested on the SQL Compare Uservoice forum here: https://redgate.uservoice.com/forums/141379-sql-compare/suggestions/2923976-option-to-select-individual-dependencies-or-affec

    Do let me know if you are able to share the DDl or a reproduction scenario and I will have a go using that - if needed I can reach out via a support ticket to get the files.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.