UFN_CODETABLE_DATAFORMREFERENCES

create function dbo.UFN_CODETABLE_DATAFORMREFERENCES
(
    @CODETABLEID uniqueidentifier
)
returns @TABLE table
(
    DATAFORMINSTANCEID uniqueidentifier
)
with execute as caller
as
begin

    -- build a temp table containing the data form instance ID

    declare @t table (ID uniqueidentifier);

    declare @codeTableName nvarchar(100);
    select @codeTableName = C.DBTABLENAME
        from dbo.CODETABLECATALOG as C 
        where C.ID = @CODETABLEID;

    -- find forms that use this search list

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select distinct INSTANCE.ID
        from dbo.DATAFORMINSTANCECATALOG as INSTANCE
            inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            cross apply TEMPLATE.TEMPLATESPECXML.nodes('//common:CodeTable') as dataform(codetablefields)
        where 
            codetablefields.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    -- now build the results

    insert into @TABLE
        select distinct T.ID
        from @t as T

    return;

end;