DACBuilder – Data Access objects generation tool based on XML and XSL templates transformation

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