Scripts for use with cleaning up Sitecore tables.

CleanupFields.sql

 -- Find field data not associated with an item.
 SELECT Id  FROM SharedFields f
 WHERE NOT EXISTS (SELECT ID FROM Items i WHERE i.ID = f.ItemId) 
 SELECT Id  FROM UnversionedFields f
 WHERE NOT EXISTS (SELECT ID FROM Items i WHERE i.ID = f.ItemId) 
 SELECT Id  FROM VersionedFields f
 WHERE NOT EXISTS (SELECT ID FROM Items i WHERE i.ID = f.ItemId)

CleanupOrphans.sql

-- Find items with no parent
SELECT ID 
FROM Items i1 
WHERE i1.ParentID <> '00000000-0000-0000-0000-000000000000' AND NOT EXISTS(SELECT ID FROM Items i2 WHERE i2.ID = i1.ParentID)

CleanupTemplateFields.ps1

# Find and delete field data with no valid template field
$tables = "SharedFields", "UnversionedFields", "VersionedFields"

$database = Get-Database -Name "web"

$createTable = "CREATE TABLE #TemplateFields (ID uniqueidentifier)"
Write-Host $createTable
foreach ($value in [Sitecore.Data.Managers.TemplateManager]::GetTemplates($database).Values)
{
    $fields = $value.GetFields()
    for ($i = 0; $i -lt $fields.Length; $i++)
    {
        $templateField = $fields[$i]
        $insertStatement = [string]::Format("INSERT INTO #TemplateFields VALUES ('{0}')", $templateField.ID)
        Write-Host $insertStatement
    }
}

foreach($table in $tables) {
    $deleteStatement = [string]::Format("DELETE FROM {0} WHERE NOT EXISTS (SELECT 1 FROM #TemplateFields WHERE FieldID = #TemplateFields.ID)", $table)
    Write-Host $deleteStatement
}

$dropTable = "DROP TABLE #TemplateFields"
Write-Host $dropTable

RebuildDescendants.sql

DECLARE @descendants TABLE (
	Ancestor uniqueidentifier NOT NULL,
	Descendant uniqueidentifier NOT NULL,
	PRIMARY KEY(Ancestor, Descendant)
);

WITH TempSet(Ancestor,Descendant)
AS
(
	SELECT
	Items.ParentID,
	Items.ID
	FROM
	Items
	UNION ALL
	SELECT
	Items.ParentID,
	TempSet.Descendant
	FROM
	Items JOIN TempSet
	ON TempSet.Ancestor = Items.ID
)

INSERT INTO @descendants(Ancestor, Descendant)
	SELECT
	TempSet.Ancestor,
	TempSet.Descendant
	FROM
	TempSet
	OPTION (MAXRECURSION 32767)
	
MERGE Descendants WITH (HOLDLOCK) AS Target
USING @descendants AS Source
ON (
	Target.Ancestor = Source.Ancestor
	AND Target.Descendant = Source.Descendant
)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Ancestor, Descendant)
VALUES (NEWID(), Source.Ancestor, Source.Descendant)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;