USP_SCHEMA_TABLE_SETTABLETRANSLATIONFIELD

CREATE procedure dbo.USP_SCHEMA_TABLE_SETTABLETRANSLATIONFIELD
    @TABLENAME nvarchar(128),
    @TRANSLATIONFIELD nvarchar(255)
as

    declare @value nvarchar(255);

    select @value = cast(ep.value as nvarchar(255)) from 
    sys.extended_properties as ep 
    where ep.[class]=1  and ep.major_id=OBJECT_ID(@TABLENAME) and ep.minor_id=0 and ep.[name] = N'BB_TRANSLATIONFIELD';


    if @value is null
        -- property doesn't exist, so add it
        exec sys.sp_addextendedproperty N'BB_TRANSLATIONFIELD', @TRANSLATIONFIELD, N'SCHEMA', N'dbo', N'table', @TABLENAME, null, null;

    else if @value <> @TRANSLATIONFIELD
        -- property already exists, so update it
        exec sys.sp_updateextendedproperty N'BB_TRANSLATIONFIELD', @TRANSLATIONFIELD, N'SCHEMA', N'dbo', N'table', @TABLENAME, null, null;