1

How can one script a User-Defined Table Type with default constraints in SQL Server Management Studio (SSMS) 2016?

I can generate a script, but it does not include these constraints. One cannot script these constraints individually either (whereas this is possible for table constraints). In working with another developer I noted that they were able to generate the script with the constraints using SSMS 2014. Is this a bug in SSMS 2016?

Below is an example of a user-defined table type with constraints. I can execute this, but scripting it using the GUI results in the constraints being removed.

-- Example of UDT with default constraints.

IF  EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDTTest' AND ss.name = N'dbo')
    DROP TYPE [dbo].[UDTTest]
GO

IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'UDTTest' AND ss.name = N'dbo')
CREATE TYPE [dbo].[UDTTest] AS TABLE(
  [ID] [int] NULL,
  [BitTest] [bit] NULL,
  [VarcharTest] [varchar](50) NULL,
  [VarcharDefaultTest] [varchar](50) NULL DEFAULT (''),
  [IntDefaultTest] [int] NULL DEFAULT ((0)),
  [MoneyDefaultTest] [money] NULL DEFAULT ((0.0))
)
GO

Responses to comments

Not sure it's the issue, but something to check is that 'Script defaults' is true under Options -> SQL Server Object Explorer -> Scripting. Possibly 'Script bound defaults and rules' too, although I think they're deprecated features. -- Gareth Lyons

I have tried the 'Script defaults setting' as well as all the scripting options (I set everything to true).

Can anyone confirm that SSMS 2016 allows generating scripts of existing UDT with constraints?

  • Uhm, I'd love to confirm, but I can't. Same problem here. Looks to me like you hit an SSMS 2016 bug. – Marian May 31 '17 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.