UFN_SCHEMA_GETPARENTTABLENAME

CREATE function [dbo].[UFN_SCHEMA_GETPARENTTABLENAME](@TABLENAME nvarchar(128), @COLUMNNAME nvarchar(128)) returns nvarchar(128)
with execute as caller 
as
    begin

        declare @value nvarchar(128);

/* 

        select @value = KEYCOLUMNUSAGE.table_name
            from INFORMATION_SCHEMA.KEY_COLUMN_USAGE KEYCOLUMNUSAGE inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REFERENTIALCONSTRAINTS on KEYCOLUMNUSAGE.CONSTRAINT_NAME = REFERENTIALCONSTRAINTS.UNIQUE_CONSTRAINT_NAME
            inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CONSTRAINTCOLUMNUSAGE on REFERENTIALCONSTRAINTS.CONSTRAINT_NAME = CONSTRAINTCOLUMNUSAGE.CONSTRAINT_NAME
            where (CONSTRAINTCOLUMNUSAGE.TABLE_NAME = @TABLENAME) and (CONSTRAINTCOLUMNUSAGE.COLUMN_NAME = @COLUMNNAME)

        pdg 8.7.2008 

        Changed to use the sys metadata views instead of INFORMATION_SCHEMA to optimize 
        this function called during TableSpec processing.

*/

        declare @table_object_id int;
        declare @column_id int;

        set @table_object_id=OBJECT_ID(@TABLENAME,'U');

        select @column_id = column_id 
        from sys.columns 
        where 
        [name]=@COLUMNNAME
        and 
        OBJECT_ID=@table_object_id;

        select
        @value=OBJECT_NAME(fkc.referenced_object_id)
        from
        sys.foreign_key_columns as fkc
        where 
        fkc.parent_object_id = @table_object_id
        and 
        fkc.parent_column_id=@column_id;

        return @value;

    end