UFN_DATAFORMINSTANCE_PAGEREFERENCES

CREATE function dbo.UFN_DATAFORMINSTANCE_PAGEREFERENCES
(
    @DATAFORMINSTANCEID uniqueidentifier
)
returns @TABLE table
(
    PAGEID uniqueidentifier
)
with execute as caller
as
begin

    -- build a temp table containing the page ID

    declare @t table (ID uniqueidentifier);

    declare @mode smallint;
    set @mode = (select TEMPLATE.MODE  
                 from dbo.DATAFORMTEMPLATECATALOG TEMPLATE inner join dbo.DATAFORMINSTANCECATALOG INSTANCE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
                 where INSTANCE.ID = @DATAFORMINSTANCEID);

    -- for add forms, look for ShowAddDataForm actions

    if @mode = 2
        begin
            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select distinct P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                    cross apply P.PAGEDEFINITIONSPEC.nodes('//common:ShowAddDataForm') as page(dataforminstanceactions)
                where page.dataforminstanceactions.value('@DataFormID', 'uniqueidentifier') = @DATAFORMINSTANCEID;
        end;

    -- for edit/view forms, look for ShowDataForm actions

    if (@mode = 0) or (@mode = 1)
        begin
            -- find pages with page actions that use this data form instance

            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select distinct P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                    cross apply P.PAGEDEFINITIONSPEC.nodes('//common:ShowDataForm') as page(dataforminstanceactions)
                where page.dataforminstanceactions.value('@DataFormID', 'uniqueidentifier') = @DATAFORMINSTANCEID;
        end;

    -- for view forms, look elsewhere

    if @mode = 0
        begin
            -- find page sections that use this data form instance

            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select distinct P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                    cross apply P.PAGEDEFINITIONSPEC.nodes('//tns:DataForm') as page(dataforminstancesections)
                where page.dataforminstancesections.value('@ID', 'uniqueidentifier') = @DATAFORMINSTANCEID;

            -- find data list sections that use this data form instance as the detail form

            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select distinct P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                    cross apply P.PAGEDEFINITIONSPEC.nodes('//tns:DetailViewForm') as page(datalistdetailforms)
                where page.datalistdetailforms.value('@ViewDataFormID', 'nvarchar(max)') like '%' + cast(@DATAFORMINSTANCEID as nvarchar(36)) + '%';                

            -- look at the page expression form

            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                where 
                    P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/@ExpressionDataFormID)[1]', 'uniqueidentifier') = @DATAFORMINSTANCEID;

            -- also look at additional page expression forms

            with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select distinct P.ID
                from dbo.PAGEDEFINITIONCATALOG as P
                    cross apply P.PAGEDEFINITIONSPEC.nodes('tns:PageDefinitionSpec/tns:PageExpressionForms/tns:PageExpressionForm') as page(expressionforms)
                where 
                    page.expressionforms.value('@DataFormInstanceID', 'uniqueidentifier') = @DATAFORMINSTANCEID;
        end;

    -- now build the results

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

    return;

end;