I'm not sure how you want to mark the blocks for deletion, but you can identify unreferences blocks with the following SQL query.

SELECT pkID
FROM tblContent
WHERE 
   fkContentTypeId IN (SELECT pkId FROM tblContentType WHERE ContentType = 1) AND     -- all blocks 
   ContentGUID NOT IN (SELECT fkReferencedContentGUID FROM tblContentSoftLink)        -- that are not references

Hi Dan,

Here are some links that will help you

https://stackoverflow.com/questions/56867221/fetch-all-episerver-blocks-type-usages-programmatically

https://world.episerver.com/forum/developer-forum/-Episerver-75-CMS/Thread-Container/2020/3/list-all-by-content-type/

In tblContentType ContentType is 1 for all block types and 0 for all page types.

If you want just your custom block types, you could filter by start of namespace, likt this:

SELECT pkID
FROM tblContent
WHERE 
   fkContentTypeId IN (SELECT pkId FROM tblContentType WHERE ContentType = 1 AND ModelType LIKE 'Alloy.%') AND
   ContentGUID NOT IN (SELECT fkReferencedContentGUID FROM tblContentSoftLink)

Here are two ways to get the Unused blocks.

• Install the PowerSlice and filter out Unused Blocks

• Implement below code and pass the block type e.g. EventBlock

public void RemoveUnUsedBlocks()
{
            var contentLoader = ServiceLocator.Current.GetInstance<IContentLoader>();
            var contentTypeRepository = ServiceLocator.Current.GetInstance<IContentTypeRepository>();
            var contentModelUsage = ServiceLocator.Current.GetInstance<IContentModelUsage>();

            // get all usages of EventBlock, including versions
            var contentType = contentTypeRepository.Load<EventBlock>();
            var contentUsages = contentModelUsage.ListContentOfContentType(contentType);

            // get distinct content references without version
            var contentReferences = contentUsages
                .Select(x => x.ContentLink.ToReferenceWithoutVersion())
                .Distinct()
                .ToList();

            // fetch data from DB and filter out the block there reference 
            var instances = contentReferences
                .Select(contentReference => contentLoader.Get<IContent>(contentReference))
                .Where(IsNotReferenced)
                .ToList();

            foreach (var block in instances)
            {
                //Todo : locate old cms block templates here
            }
 }       

private bool IsNotReferenced(IContent content)
{
            return !_contentRepository.GetReferencesToContent(content.ContentLink, false).Any();
}