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 descendantsItem AS(
SELECT ID,TemplateID
FROM [Master].[dbo].Items
WHERE ID = '#MediaRootItemID#'
UNION ALL
SELECT i.ID,i.TemplateID
FROM [Master].[dbo].Items i
INNER JOIN descendantsItem di
ON di.ID = i.ParentID ),LinksItem AS(
SELECT *
FROM [Core].[DBO].Links
WHERE TargetItemID in (
SELECT id
FROM descendantsItem
where descendantsItem.TemplateID != '{FE5DD826-48C6-436D-B87A-7C4210C7413B}'))
SELECT Id
FROM descendantsItem
WHERE descendantsItem.ID NOT IN (
SELECT TargetItemID
FROM LinksItem) AND TemplateID != '{FE5DD826-48C6-436D-B87A-7C4210C7413B}'
GO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s