How to get sitecore unused media item using SQL Query

As we all know, Sitecore use link database to store referrers of item and using those referrers, we can identify unused media item.

Below are the steps which we need to execute to get unused media item.

Step 1: 

Identify database in which all the media item referrers are stored. For that, we need to check below configuration node in web.config/Sitecore.config file (default sitecore use core database to store referrers).

<!-- LINK DATABASE -->
<LinkDatabase type="Sitecore.Data.$(database).$(database)LinkDatabase, Sitecore.Kernel">

<param connectionStringName="core"/>

</LinkDatabase>

Step 2:

Below SQL query to get unused image/file item list.

Note:

Replace below tokens as per requirement.

#MediaRootItemID#: This token use to provide root media item (result of this query return all the unused descendant media items of given root media item).

#TemplateID#: if user exclude some of the items, which was created using specific template then replace those template ID in this token.

WITH descendantItems AS(
              SELECT ID,TemplateID
              FROM [Master].[dbo].Items
              WHERE ID like '#MediaRootItemID#'
              UNION ALL
              SELECT i.ID,i.TemplateID
              FROM [Master].[dbo].Items i
              INNER JOIN descendantItems di
              ON di.ID = i.ParentID
         ),LinksItem AS(
                         SELECT *
                         FROM [Core].[DBO].Links
                         WHERE SourceDatabase like 'master'
AND TargetItemID in (
                                                SELECT id
                                                FROM descendantItems
                                                 WHERE descendantItems.TemplateID != '#TemplateID#'
                                               )
                        )
         select Id from descendantItems where descendantItems.ID not in (select TargetItemID from LinksItem) and descendantItems.TemplateID != '#TemplateID#'
GO
Advertisements