Sql-server – How to add an extended property to a table type

sql server

According to the MSDN documentation, 'TABLE_TYPE' is a valid value for the @level1type parameter as shown below.

EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'An INT value.', 
@level0type = N'SCHEMA', @level0name = dbo, 
@level1type = N'TABLE_TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
GO

In Visual Studio 2013 in an SSDT project, the @level1type parameter name is underscored and has the error

SQL70539: Invalid argument (@level1type)

Trying to execute the statement in a query window results in the error

Msg 15600, Level 16, State 5, Procedure sp_addextendedproperty, Line 37
An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.

How can I add an extended property to a table type?

Best Answer

The MSDN documentation:

http://msdn.microsoft.com/en-us/library/ms180047.aspx

appears to be incorrect. TABLE_TYPE is not a valid level1type value.

Documentation is also missing here:

http://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

but a comment at the bottom provided the answer.

Extended properties may be specified for a table type by using a leve1type of 'TYPE'

EXEC sys.sp_addextendedproperty 
@name = N'MS_Description', 
@value = N'An INT value.', 
@level0type = N'SCHEMA', @level0name = dbo, 
@level1type = N'TYPE',  @level1name = AllTypesRecord,
@level2type = N'COLUMN', @level2name = 'Id'
Go
Related Question