Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Finds invalid content in the Sitecore database
DECLARE @SharedFieldId UniqueIdentifier = '{BE351A73-FCB0-4213-93FA-C302D8AB4F51}' /* Shared checkbox */
DECLARE @UnversionedFieldId UniqueIdentifier = '{39847666-389D-409B-95BD-F2016F11EED5}' /* unversioned checkbox */
DECLARE @TemplateFieldId UniqueIdentifier = '{455A3E98-A627-4B40-8035-E683A0331AC7}' /* Template field */
-- Find all templates WHERE both "Unversioned" AND "Shared" is selected:
-- "Shared" will have precedense, so the "Unversioned" checkbox can be removed
SELECT * FROM SharedFields
WHERE FieldId=@UnversionedFieldId AND [Value] = '1'
AND ItemId IN (SELECT ItemId FROM SharedFields WHERE FieldId=@SharedFieldId AND [Value]='1')
-- Find all Versioned rows for shared fields:
SELECT * FROM VersionedFields
WHERE FieldId IN (SELECT ItemId FROM SharedFields WHERE [Value]='1' AND FieldId=@SharedFieldId)
-- Find all Unversioned rows for shared fields:
SELECT * FROM UnversionedFields
WHERE FieldId IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@SharedFieldId)
-- Find all Versioned rows for unversioned fields:
SELECT * FROM VersionedFields
WHERE FieldId IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@UnversionedFieldId)
-- Find all Shared rows for unversioned fields:
-- If both shared AND unversioned are checked, it should be considered shared
SELECT * FROM SharedFields
WHERE FieldId IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@UnversionedFieldId)
AND FieldId not IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@SharedFieldId)
-- Find all Unversioned rows for versioned fields:
SELECT * FROM UnversionedFields
WHERE FieldId IN (SELECT id FROM Items
WHERE TemplateID=@TemplateFieldId
AND id not IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@SharedFieldId)
AND id not IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@UnversionedFieldId)
)
-- Find all Shared rows for versioned fields:
SELECT * FROM SharedFields
WHERE FieldId IN (SELECT id FROM Items
WHERE TemplateID=@TemplateFieldId
AND id not IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@SharedFieldId)
AND id not IN (SELECT ItemId FROM SharedFields WHERE [Value] = '1' AND FieldId=@UnversionedFieldId)
)
@mikaelnet

This comment has been minimized.

Copy link
Owner Author

mikaelnet commented Nov 14, 2019

This script finds records in the database that are in the wrong table. The DBCleanup tool doesn't fix those rows. This kind of content can cause SPS to publish incorrect data. Replace "SELECT *" with "DELETE" in order to cleanup the invalid content.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.