spGetDependancies

            CREATE      Procedure [dbo].[spGetDependancies]
(
@ID int,
@FilterBy int
)
AS
/*
    @FilterBy:
        1 = LayoutID
        2 = TemplateID
        3 = PageID
        4 = ContentID
        5 = StylesheetID
*/

if @FilterBy = 1 -- Layout ID
    select Name, ID, ObjectType, ClientSitesID, ParentObjectId, ParentObjectType from (
        -- Templates linked to the layout
        select distinct SP.PageName as Name, SP.ID as ID, 3 as ObjectType, SP.ClientSitesID as ClientSitesID, -1 as ParentObjectId, -1 as ParentObjectType, 1 as SORT
        from CMSTEMPLATELAYOUT CTL
        inner join SitePages SP on SP.ID = CTL.TEMPLATEID
        where CTL.SITELAYOUTSID = @ID and SP.DELETED = 0

        union all -- Layout's template's page
        select distinct SP.PageName as Name, SP.ID as ID, 2 as ObjectType, SP.ClientSitesID as ClientSitesID, SPT.ID as ParentObjectId, 3 as ParentObjectType, 2 as SORT 
        from CMSTEMPLATELAYOUT CTL
        inner join SitePages SPT on SPT.ID = CTL.TEMPLATEID
        inner join SitePages SP on SP.TemplatePageID = SPT.ID

where CTL.SITELAYOUTSID = @ID  and SPT.DELETED = 0 and SP.DELETED = 0
    ) A 
    order by SORT asc;

else if @FilterBy = 2 -- Template ID
    select Name, ID, ObjectType, ClientSitesID, ParentObjectId, ParentObjectType from (
        -- Pages linked to the template
        select distinct SP.PageName as Name, SP.ID as ID, 2 as ObjectType, SP.ClientSitesID as ClientSitesID, -1 as ParentObjectId, -1 as ParentObjectType, 1 as SORT
        from SitePages SP
        where SP.TemplatePageID = @ID and SP.DELETED = 0                    
    ) A 
    order by SORT asc;            

else if @FilterBy = 4 -- Content ID
    select Name, ID, ObjectType, ClientSitesID, ParentObjectId, ParentObjectType from (
        -- Templates directly using part
        select distinct SP.PageName as Name, SP.ID as ID, 3 as ObjectType, SP.ClientSitesID as ClientSitesID, -1 as ParentObjectId, -1 as ParentObjectType, 1 as SORT
        from SitePages SP 
        inner join PageContent PC on PC.SitePagesID = SP.ID

where (PC.SiteContentID = @ID or PC.SiteContentID in 
    (select TC.SiteContentID from dbo.TargetedContentPart TC inner join dbo.TargetedContentChecks TCC 
        on TCC.TargetedPartID = TC.ID where TCC.ContentID=@ID))
        and SP.IsTemplatePage = 1 and SP.DELETED = 0

        union all  -- Pages from templates directly using part
        select distinct SP.PageName as Name, SP.ID as ID, 2 as ObjectType, SP.ClientSitesID as ClientSitesID, SPT.ID as ParentObjectId, 3 as ParentObjectType, 2 as SORT
        from SitePages SPT 
        inner join SitePages SP on SP.TemplatePageID = SPT.ID
        inner join PageContent PC on PC.SitePagesID = SPT.ID
        where (PC.SiteContentID = @ID or PC.SiteContentID in 
        (select TC.SiteContentID from dbo.TargetedContentPart TC inner join dbo.TargetedContentChecks TCC 
        on TCC.TargetedPartID = TC.ID where TCC.ContentID=@ID)) and SPT.DELETED = 0 and SP.DELETED = 0

        union all -- Pages directly using part
        select distinct SP.PageName as Name, SP.ID as ID, 2 as ObjectType, SP.ClientSitesID as ClientSitesID, 
-1 as ParentObjectId, -1 as ParentObjectType, 3 as SORT
        from SitePages SP 
        inner join PageContent PC on PC.SitePagesID = SP.ID
        where (PC.SiteContentID = @ID or PC.SiteContentID in 
        (select TC.SiteContentID from dbo.TargetedContentPart TC inner join dbo.TargetedContentChecks TCC 
        on TCC.TargetedPartID = TC.ID where TCC.ContentID=@ID))
        and SP.IsTemplatePage <> 1 and SP.DELETED = 0
    ) A 
    order by SORT asc;

else if @FilterBy = 5 --- Stylesheet ID
    select Name, ID, ObjectType, ClientSitesID, ParentObjectId, ParentObjectType from (
        -- Site's using style sheet in Theme
        select distinct CS.Name as Name, CS.ID as ID, 8 as ObjectType, CS.ID as ClientSitesID, -1 as ParentObjectId, -1 as ParentObjectType, 1 as SORT
        from THEMESTYLESHEET TS
        inner join THEME T on T.ID = TS.THEMEID
        inner join ClientSites CS on CS.ID = T.CLIENTSITESID
        where TS.STYLESHEETID = @ID

        union all -- Layout Stylesheets
        select distinct SL.Name as Name, SL.ID as ID, 17 as ObjectType, SL.ClientSitesID as ClientSitesID, 
-1as ParentObjectId, -1 as ParentObjectType, 2 as SORT
        from SiteLayouts SL
        where SL.STYLESHEETID = @ID and SL.DELETED = 0

        union all -- Layout's templates
        select distinct SP.PageName as Name, SP.ID as ID, 3 as ObjectType, SP.ClientSitesID as ClientSitesID, SL.ID as ParentObjectId, 17 as ParentObjectType, 3 as SORT
        from SiteLayouts SL
        inner join CMSTEMPLATELAYOUT CTL on CTL.SITELAYOUTSID = SL.ID
        inner join SitePages SP on SP.ID = CTL.TEMPLATEID
        where SL.STYLESHEETID = @ID and SP.DELETED = 0 and SL.DELETED = 0

        union all -- Layout's template's page
        select distinct SP.PageName as Name, SP.ID as ID, 2 as ObjectType, SP.ClientSitesID as ClientSitesID, SPT.ID as ParentObjectId, 3 as ParentObjectType, 4 as SORT 
        from SiteLayouts SL
        inner join CMSTEMPLATELAYOUT CTL on CTL.SITELAYOUTSID = SL.ID
        inner join SitePages SPT on SPT.ID = CTL.TEMPLATEID
        inner join SitePages SP on SP.TemplatePageID = SPT.ID
        where SL.STYLESHEETID = @ID and SL.DELETED = 0 and SPT.DELETED = 0 
and SP.DELETED = 0
    ) A 
    order by SORT asc;