ALTER proc [dbo].[spASDKGetItemsByType]
           @Culture char(10),
           @TypeName nvarchar(2048)
        as  

create table #asdkTmp
                (Guid uniqueidentifier primary key clustered,
                ClassGuid uniqueidentifier,
                Attributes int
                )
create index asdkTmp_Class on #asdkTmp(ClassGuid)

declare @TypeGuids table
                (ClassGuid uniqueidentifier
                )

insert into @TypeGuids
select Guid from Class where Type = @TypeName

if not exists (select * from @TypeGuids)
begin
                insert into @TypeGuids
                select
                                BaseGuid
                from
                                String
                where 1 = 1
                                and StringRef in ('creatableclass', 'itemtype')
                                and Culture = ''
                                and String = @TypeName
end

insert into #asdkTmp (Guid, ClassGuid, Attributes)
select distinct
                i.Guid,
                i.ClassGuid,
                i.Attributes
from
                vItem i join
                Class on i.ClassGuid=Class.Guid join
                ClassBaseClass CBC on Class.Guid=CBC.ClassGuid left outer join
                vItemFolder ifldr on i.Guid=ifldr.ItemGuid join
                @TypeGuids tg on CBC.BaseClassGuid = tg.ClassGuid

-- table 0  
select
                tmp.Guid Guid,  
                st1.String Name,  
                st2.String Description,  
                Class.Type ClassTypeName,   
                Class.Guid ClassTypeGuid,  
                st3.String ParentFolderName,  
                ifl.ParentFolderGuid ParentFolderGuid,  
                tmp.Attributes,
                case when cbc.BaseClassGuid is not null then 1 else 0 end 
IsPolicy,
                ISNULL(ia.Enabled, 0) Enabled,
                case when ci.InterfaceGuid is not null then 1 else 0 end 
IsSchedulableItem
FROM
                #asdkTmp tmp join
                Class on tmp.ClassGuid=Class.Guid left outer join
                vItemFolder ifl on tmp.Guid=ifl.ItemGuid left outer join
                ItemActive ia on tmp.Guid=ia.Guid left outer join
                vStringCache st1 ON tmp.Guid=st1.BaseGuid
                                and st1.StringRef='item.name'
                                and st1.Culture=@Culture left outer join
                vStringCache st2 ON tmp.Guid=st2.BaseGuid
                                and st2.StringRef='item.description'
                                and st2.Culture=@Culture left outer join
                vStringCache st3 ON ifl.ParentFolderGuid=st3.BaseGuid
                                and st3.StringRef='item.name'
                                and st3.Culture=@Culture left outer join
                ClassBaseClass cbc on tmp.ClassGuid = cbc.ClassGuid
                                and cbc.BaseClassGuid = '10B9F151-82D6-4e00-BFD8-3C75C1DF8BDD' left outer join
                ClassInterface ci on tmp.ClassGuid = ci.ClassGuid
                                and ci.InterfaceGuid = '0E88E0BC-4D27-4421-A302-5336AB3D62BB'

-- table 1
select
                iat.ItemGuid ItemGuid,
                iat.ResourceTargetGuid  
from
                ItemAppliesTo iat join 
                #asdkTmp tmp on tmp.Guid = iat.ItemGuid  

-- table 2  
select
                item.Guid,
                item.State 
from
                vItem item join 
                #asdkTmp tmp on tmp.Guid=item.Guid

-- table 3
select
                item.Guid,
                item.SecurityGuid,
                case when res.Guid is not null then 1 else 0 end IsResource
from
                #asdkTmp tmp join
                vItem item  on tmp.Guid = item.Guid left outer join
                ItemResource res on tmp.Guid = res.Guid

drop table #asdkTmp