USP_SCHEMA_TABLE_SETCOLUMNCODETABLE

CREATE procedure dbo.USP_SCHEMA_TABLE_SETCOLUMNCODETABLE
    @TABLENAME nvarchar(128),
    @COLUMNNAME nvarchar(128),
    @CODETABLE nvarchar(100)
as
    declare @value nvarchar(100);

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

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