Finds invalid content in the Sitecore database

Sitecore-InvalidContentFieldData.sql

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)
	)

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.