USP_SCHEMA_TABLE_SETCOLUMNTRANSLATIONFUNCTION

CREATE procedure dbo.USP_SCHEMA_TABLE_SETCOLUMNTRANSLATIONFUNCTION

  @TABLENAME nvarchar(128),
  @COLUMNNAME nvarchar(128),
  @TRANSLATIONFUNCTION nvarchar(255)

as

  declare @value nvarchar(255);

  select @value = cast(ep.value as nvarchar(255)) 
  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_TRANSLATIONFUNCTION'
  and 
  c.[name]=@COLUMNNAME
  ;


  if @value is null
    -- property doesn't exist, so add it
    exec sys.sp_addextendedproperty N'BB_TRANSLATIONFUNCTION', @TRANSLATIONFUNCTION, N'SCHEMA', N'dbo', N'table', @TABLENAME, N'COLUMN', @COLUMNNAME ;
  else if @value <> @TRANSLATIONFUNCTION
    -- property already exists, so update it
    exec sys.sp_updateextendedproperty N'BB_TRANSLATIONFUNCTION', @TRANSLATIONFUNCTION, N'SCHEMA', N'dbo', N'table', @TABLENAME, N'COLUMN', @COLUMNNAME ;