Where my block has been used on server? A question that all back-end developer have in mind

The story started when I had a task to remove old blocks that we don't use any more. From a back-ender's point of view, I just needed to find all references in my code and make sure that it has not been used anywhere. But of course it is not correct when dealing with a CMS like EpiServer since they can be used in a ContentArea.

Yes, I know, we have also [AllowedType] attribute that we use in order to limit contentAreas, but still, there can be some items in blocks tree that has not been used in any page and may cause a problem if an editor click on them.

So what shall we do?

First, Every content in EpiServer will be saved in tblContent. if you want to make sure that there is no instance from your typename it is enough to check that table.Code:

SELECT c.*   FROM [dbo].[tblContent] c
inner join [dbo].[tblContentType] ct on c.fkContentTypeID = ct.pkID
where  [ModelType] like '%TypeName%'

But it is only 10% of the cases, besides where is the fun?!!!
I want to know how many instances of my type exists and where are they now? also I want to know how to find their parent in the tree. So I wrote this code:

Code:

declare @TypeName nvarchar(50)
set @TypeName = 'myBlockType'

select tbl2.contentName as contentName, tbl2.contentSegment as ContentSegment, tbl2.ContentGUID as ContentGUID,
con2pkID ContainerID,con2ContentGUID ContainerGUID, conLang.name as ContainerName,conLang.URLSegment as ContainerURLSegment, contype.Name as ContainerTypeName, 
contype.ModelType as ContainerModelType, con2.fkParentID as ContainersParentId, conContainerParentLanguage.Name as ContainerParentName, conContainerParentType.Name as ContainerParentName

from [dbo].[tblContentProperty] cp  
 inner join 
(SELECT  c.*, cl.name as contentName,  cl.URLSegment as contentSegment FROM [dbo].[tblContent] c
inner join [dbo].[tblContentType] ct on c.fkContentTypeID = ctpkID
inner join tblContentLanguage cL on cL.fkContentID = c.pkID
where  ct.Name like @TypeName)  tbl2 on cp.LongString like '%'+cast( tbl2.ContentGUID as nvarchar(50))+'%'
inner join tblContent con2 on con2.pkID = cp.fkContentID
inner join [dbo].[tblContentType] contype on con2.fkContentTypeID = contypepkID
inner join tblContentLanguage conLang on conLang.fkContentID = con2pkID
inner join tblContent conContainerParent on conContainerParent.pkID = con2fkParentID
inner join tblContentType conContainerParentType on conContainerParent.fkContentTypeID = conContainerParentTypepkID
inner join tblContentLanguage conContainerParentLanguage on conContainerParentLanguage.fkContentID = con2.fkParentID

This code will show you all instances of a type, and where they have been contained including typename and the address that we can find the parent in the tree.


The result will be something like this

But wait a minute! what if I can't find the container it self?!
Well, that is easy :)  the query is 90% the same, but we just need to search for the contentId (instance) instead of the type name.
Of course if you search for the containers type name, you will eventually find it, But it is easier to change 2 lines of code :)

Code:

declare @ContentGUID nvarchar(50)
set @ContentGUID = 'B6845FFC-5475-4FC3-C701-5A5D6FD5F967' -- put you content GUID in here 

select tbl2.contentName as contentName, tbl2.contentSegment as ContentSegment, tbl2.ContentGUID as ContentGUID,
con2pkID ContainerID,con2ContentGUID ContainerGUID, conLang.name as ContainerName,conLang.URLSegment as ContainerURLSegment, contype.Name as ContainerTypeName, 
contype.ModelType as ContainerModelType, con2.fkParentID as ContainersParentId, conContainerParentLanguage.Name as ContainerParentName, conContainerParentType.Name as ContainerParentName

from [dbo].[tblContentProperty] cp
 inner join
(SELECT  c.*, cl.name as contentName,  cl.URLSegment as contentSegment
FROM [dbo].[tblContent] c
inner join tblContentLanguage cL on cL.fkContentID = c.pkID
where c.ContentGUID like @ContentGUID)  tbl2 on cp.LongString like '%'+cast( tbl2.ContentGUID as nvarchar(50))+'%'
inner join tblContent con2 on con2.pkID = cp.fkContentID
inner join [dbo].[tblContentType] contype on con2.fkContentTypeID = contypepkID
inner join tblContentLanguage conLang on conLang.fkContentID = con2pkID
inner join tblContent conContainerParent on conContainerParent.pkID = con2fkParentID
inner join tblContentType conContainerParentType on conContainerParent.fkContentTypeID = conContainerParentTypepkID
inner join tblContentLanguage conContainerParentLanguage on conContainerParentLanguage.fkContentID = con2.fkParentID