Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Scripts for use with cleaning up Sitecore tables.
-- 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)
-- 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)
# 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
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment