Skip to content

Instantly share code, notes, and snippets.

@Hendy
Last active October 28, 2020 10:10
Show Gist options
  • Save Hendy/1686288f7242cf4fdfab to your computer and use it in GitHub Desktop.
Save Hendy/1686288f7242cf4fdfab to your computer and use it in GitHub Desktop.
Umbraco - delete version history for all content
-- Create a temporary table for all documents which are published and not in the recycle bin
CREATE TABLE #Nodes (id int)
-- Delete all rows if the table exists before
TRUNCATE TABLE #Nodes
-- Insert all nodeIds from all documents which are published and not in the recycle bin
INSERT INTO #Nodes
SELECT N.id
FROM umbracoNode N
INNER JOIN cmsDocument D ON N.ID = D.NodeId
WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
AND [path] NOT LIKE '%-20%'
AND D.Published = 1
-- Create a temporary table for all versionId's to delete
CREATE TABLE #Versions (id UniqueIdentifier)
-- Delete all rows if it exists before
TRUNCATE TABLE #Versions
-- Insert all versionId's from all nodeIds in the #Nodes table
-- and where published is set to false and newest is set to false
INSERT INTO #Versions
SELECT versionId
FROM cmsDocument
WHERE nodeId IN (SELECT id FROM #Nodes)
AND published = 0 AND newest = 0
-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
-- from the nodes which are published and which are not in the recycle bin
-- and which are not published and which are not the newest
DELETE FROM cmsPreviewXml WHERE versionId IN (SELECT id FROM #Versions)
DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
DROP TABLE #Versions
DROP TABLE #Nodes
@Nicholas-Westby
Copy link

Why not just truncate the cmsPreviewXml table?

@ccasalicchio
Copy link

Worked for 7.5.2

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment