IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'fnGetExtendedProperty')
BEGIN
DROP FUNCTION fnGetExtendedProperty
END
GO
/*
name: fnGetExtendedProperty
type: scalar function
returns sql_variant
parameters:
@property_name sysname, -- extended property name (usually, N'MS_Description')
@table_name varchar(128), -- table name for specified column to retrieve extended property
@column_name varchar(128) -- column name to retrieve extended property
scope: uses global function ::fn_listextendedproperty to retrieve a
value from an extended property added to a column in a table
*/
CREATE FUNCTION fnGetExtendedProperty(
@property_name sysname, -- extended property name (usually, N'MS_Description')
@table_name varchar(128), -- table name for specified column to retrieve extended property
@column_name varchar(128) -- column name to retrieve extended property
)
RETURNS SQL_VARIANT
AS
BEGIN
DECLARE @value SQL_VARIANT
SELECT @value = VALUE FROM ::fn_listextendedproperty(
@property_name, 'USER', 'dbo', 'table', @table_name, 'COLUMN', @column_name
)
RETURN @value
END
GO