Sitecore CMS - SQL for Removing Non Image Blobs from a Sitecore Database Blob table

gistfile1.sql

-- Large blob data in test/uat environments is just wastefull use of disk space 
SP_SPACEUSED [Blobs]
  
-- FINDING ItemIds FOR RELEVANT FIELDS
-- File > Information > Extension Field
select * from Items where ID like 'C06867FE-9A43-4C7D-B739-48780492D06F'
-- File > Media > Blob Field
select * from Items where ID like '40E50ED9-BA07-4702-992E-A912738D32DC'

-- Examining the data location of the fields
select * from UnversionedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from SharedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from VersionedFields where Value like 'pdf' and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select * from UnversionedFields where  FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
select * from SharedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'
select * from VersionedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC'

-- viewing how many datatypes are stored in the database 
select distinct Value from SharedFields where FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select count(Value) from SharedFields where Value in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'

-- evaluating if removing should be done by including or excluding specific media extension
select * from SharedFields where Value in ('pdf','zip','xls','psd','pptx','wmv','ppt','pps','ppsx') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'

-- Find all BlobId's related to files that are not of of extension jpeg, jpg, png or gif
select Value from SharedFields where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC' 
	and ItemId in (select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F')

-- Select the bloatted Blobs (Notice that we cast to varchar and remove excessive { } from value source prior to comparing)
select COUNT(*) from [blobs] 
	where Cast(BlobId AS varchar(200)) in (
		select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') from SharedFields 
			where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC' 
			and ItemId in (
				select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
			)
	)

-- READY TO REMOVE THE BLOATED BLOBS THAT ARE NOT image FILES / Two different approaches
/*

-- DELETE ONLY FOUND FILE BLOBS 
DELETE FROM [blobs] 
	where Cast(BlobId AS varchar(200)) in (
		select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') from SharedFields 
			where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC' 
			and ItemId in (
				select ItemId from SharedFields where Value not in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
			)
	)

-- DELETE EVERYTHING EXCEPT IMAGE FILES (uses left outer join for better speed)
DELETE FROM [blobs] where Id IN ( 
	select Id from [blobs] AA
	LEFT OUTER JOIN (
		select Replace(Replace(Cast(Value AS varchar(200)),'{',''),'}','') as ValidBlobId from SharedFields 
			where FieldId like '40E50ED9-BA07-4702-992E-A912738D32DC' 
			and ItemId in (
				select ItemId from SharedFields where Value in ('jpeg','jpg','png','gif') and FieldId like 'C06867FE-9A43-4C7D-B739-48780492D06F'
			)) BB ON Cast(AA.BlobId AS varchar(200)) = BB.ValidBlobId 
	WHERE BB.ValidBlobId IS NULL
	)

*/

-- Files left in blobs
Select COUNT(*) from blobs

-- Large blob data is now gone :) disk space has been recovered for a better world 
SP_SPACEUSED [Blobs]