UFN_SCHEMA_TABLE_GETCOLUMNCOMMENT

CREATE function dbo.UFN_SCHEMA_TABLE_GETCOLUMNCOMMENT(@TABLENAME nvarchar(128), @COLUMNNAME nvarchar(128)) returns nvarchar(4000)

with execute as caller

as

/*
Revised 11.6.2009 to use sys.extended_properties instead of fn_listextendedproperty
*/

begin
    declare @value nvarchar(4000);

    -- select @value = cast(value as nvarchar(4000))
    -- from fn_listextendedproperty(N'MS_Description', N'SCHEMA', N'dbo', N'table', @TABLENAME, N'COLUMN', @COLUMNNAME);

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

    select @value = case when @value is null then '' else @value end;

    return @value;

end