USP_SCHEMA_TABLE_SETCOLUMN_FKTRANSLATIONFIELD

CREATE procedure [dbo].[USP_SCHEMA_TABLE_SETCOLUMN_FKTRANSLATIONFIELD]

    @TABLENAME nvarchar(128),
    @COLUMNNAME nvarchar(128),
    @FIELDNAME nvarchar(128)

as

      set nocount on;

    declare @value nvarchar(128);

    select @value = cast(ep.value as nvarchar(128)) 
    from sys.extended_properties as ep
    inner join sys.columns as c on c.object_id=ep.major_id and c.column_id=ep.minor_id
      where 
      ep.class=1
      and
      ep.major_id=OBJECT_ID(@TABLENAME,'U')
      and 
      ep.minor_id > 0
      and
      ep.[name] = N'BB_FKTRANSLATIONFIELD'
      and 
      c.[name]=@COLUMNNAME
      ;

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

    else if @value <> @FIELDNAME
        -- property already exists, so update it
        exec sys.sp_updateextendedproperty N'BB_FKTRANSLATIONFIELD', @FIELDNAME, N'SCHEMA', N'dbo', N'table', @TABLENAME, N'COLUMN', @COLUMNNAME;

    return 0;