Skip to content

Instantly share code, notes, and snippets.

@mikaelnet
Last active November 2, 2022 00:47
Show Gist options
  • Save mikaelnet/a57faa9a7f9b35b9bba1118787b4266d to your computer and use it in GitHub Desktop.
Save mikaelnet/a57faa9a7f9b35b9bba1118787b4266d to your computer and use it in GitHub Desktop.
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
Copy link
Author

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