3

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?

2 Answers 2

3

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
0

Code for the Type

EXECUTE sys.sp_addextendedproperty @name=N'MS_Description', @value=N'<Table Type Description>.' , @level0type=N'SCHEMA',@level0name=N'<Schema Name>', @level1type=N'TYPE',@level1name=N'<Type Name>';

Code for the Column(s)

EXECUTE sys.sp_addextendedproperty @name=N'MS_Description', @value=N<Column Description>.' , @level0type=N'SCHEMA',@level0name=N'<Schema Name>', @level1type=N'TYPE',@level1name=N'<Table Type Name>', @level2type=N'COLUMN',@level2name=N'<Column Description>';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.