Can I rename a check constraint on a table type in SQL Server 2014?

Question

Through SQL Server 2014, CREATE TYPE ... TABLE does not support named check constraints.

This in turn results in check constraints with cryptic names (e.g. CK__TT_Income__Incom__72BBEAA9).

Such constraints are persisted in sys.check_constraints, but I have been unable to rename one of them using sp_rename.

All attempts to rename the constraint associated with a table type result in the following error message:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 424
Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

Here's my table type:

CREATE TYPE IncomeCodeTable AS
   TABLE(IncomeCode char(1)
        CHECK (IncomeCode IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H',
                              'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
                              'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X',
                              'Y', 'Z', '1', '2', '3', '4'))
                              )*

The "most correct" attempt is first in the following list:

EXECUTE sp_rename N'sys.CK__TT_Income__Incom__72BBEAA9', 
                  N'CK_TT_IncomeCodeTable', 'object'

Next, try the internal form of the the table type

EXECUTE sp_rename   
          N'TT_IncomeCodeTable_71C7C670.CK__TT_Income__Incom__72BBEAA9', 
          N'CK_TT_IncomeCodeTable', 'object'

Finally, try the "user" form of the table type

EXECUTE sp_rename N'IncomeCodeTable.CK__TT_Income__Incom__72BBEAA9', 
                  N'CK_TT_IncomeCodeTable', 'object'

Bottom line, is there a way to rename a check constraint associated with a user-defined table type?

Answer 1

This is speculative...

The documentation doesn't seem very clear on this, but my best guess is that you simply can't rename the constraint as the documentation states that:

The user-defined table type definition cannot be modified after it is created.

and renaming the constraint would alter the type. At least this is how I interpret it. I could be misunderstanding it though, but I haven't found any other reasonable explanation.

Answer 2

First find the constraint and drop it then re-create it using Alter Table which allows you to set the name. Unfortunately this requires you to enter the constraint's definition. Using a tool that can generate DDL from an existing table might be useful for that. E.g.: MSSMS