USP_DATALIST_CODETABLEOTHERREFERENCES

CREATE procedure dbo.USP_DATALIST_CODETABLEOTHERREFERENCES
(
    @CODETABLEID uniqueidentifier
)
as
    set nocount on;

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

    declare @t table 
    (
        ITEMTYPE integer, 
        ITEMID uniqueidentifier,
        NAME nvarchar(max),
        DESCRIPTION nvarchar(max)
    );

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

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 32, ID, NAME, DESCRIPTION
        from dbo.BATCHTYPEEXTENSIONCATALOG
            cross apply SPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 22, ID, NAME, DESCRIPTION
        from dbo.BUSINESSPROCESSCATALOG
            cross apply BUSINESSPROCESSSPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 31, ID, DISPLAYNAME, DESCRIPTION
        from dbo.CONFIGURATIONDATACATALOG
            cross apply CONFIGURATIONDATASPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 2, ID, UINAME, DESCRIPTION
        from dbo.DATALISTCATALOG
            cross apply DATALISTSPEC.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 24, ID, NAME, DESCRIPTION
        from dbo.DASHBOARDCATALOG
            cross apply SPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 28, ID, DISPLAYNAME, DESCRIPTION
        from dbo.GLOBALCHANGECATALOG
            cross apply GLOBALCHANGESPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 25, ID, NAME, DESCRIPTION
        from dbo.KPICATALOG
            cross apply SPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 26, ID, NAME, DESCRIPTION
        from dbo.MERGETASKCATALOG
            cross apply SPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 7, ID, DISPLAYNAME, DESCRIPTION
        from dbo.QUERYVIEWCATALOG
            cross apply QUERYVIEWSPEC.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 9, ID, UINAME, DESCRIPTION
        from dbo.REPORTCATALOG
            cross apply REPORTSPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 10, ID, UINAME, DESCRIPTION
        from dbo.SEARCHLISTCATALOG
            cross apply SEARCHLISTSPEC.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 11, ID, UINAME, DESCRIPTION
        from dbo.SIMPLEDATALISTCATALOG
            cross apply SIMPLEDATALISTSPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 27, ID, DISPLAYNAME, DESCRIPTION
        from dbo.SMARTFIELDCATALOG
            cross apply SMARTFIELDSPECXML.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 12, ID, UINAME, DESCRIPTION
        from dbo.SMARTQUERYCATALOG
            cross apply SMARTQUERYSPEC.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select 18, ID, NAME, DESCRIPTION
        from dbo.TRANSLATIONFUNCTIONCATALOG
            cross apply TRANSLATIONFUNCTIONSPEC.nodes('//common:CodeTable') as feature(codetables)
        where feature.codetables.value('@CodeTableName', 'nvarchar(100)') = @codeTableName;

    -- now build the results

    select distinct 
        ITEMTYPE, 
        case ITEMTYPE
            when 2 then 'Data list'
            when 7 then 'Query view'
            when 9 then 'Report'
            when 10 then 'Search list'
            when 11 then 'Simple data list'
            when 12 then 'Smart query'
            when 18 then 'Translation function'
            when 22 then 'Business process'
            when 24 then 'Dashboard'
            when 25 then 'KPI'
            when 26 then 'Merge task'
            when 27 then 'Smart field'
            when 28 then 'Global change'
            when 31 then 'Configuration data'
            when 32 then 'Batch type extension'
        end as ITEMTYPENAME,
        ITEMID, 
        NAME, 
        DESCRIPTION,
        case ITEMTYPE
            when 2 then 'res:datalistspec'
            when 7 then 'res:queryviewspec'
            when 9 then 'res:reportspec'
            when 10 then 'res:searchlistspec'
            when 11 then 'res:simpledatalistspec'
            when 12 then 'res:smartqueryspec'
            when 18 then 'res:translationfunctionspec'
            when 22 then 'res:businessprocessspec'
            when 24 then 'res:flashdashspec'
            when 25 then 'res:kpispec'
            when 26 then 'res:mergetaskspec'
            when 27 then 'res:smartfieldspec'
            when 28 then 'res:globalchangespec'
            when 31 then 'res:configurationdataspec'
            when 32 then 'res:batchtypeextensionspec'
        end as IMAGEKEY
    from @t as T