Skip to content

Instantly share code, notes, and snippets.

@eriksimonic
Forked from Hendy/DeleteAllVersions.sql
Last active October 23, 2016 10:41
Show Gist options
  • Save eriksimonic/5602ad31b9505901fd5e4c99403b6b16 to your computer and use it in GitHub Desktop.
Save eriksimonic/5602ad31b9505901fd5e4c99403b6b16 to your computer and use it in GitHub Desktop.
Umbraco - delete version history for all content , Umbraco 7.5+
TRUNCATE TABLE umbracoLog
GO
TRUNCATE TABLE umbracoUser2NodePermission
GO
-- 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
-- Reindex tables
DBCC DBREINDEX (cmsPropertyData)
DBCC DBREINDEX (cmsPreviewXml)
DBCC DBREINDEX (cmsContentVersion)
DBCC DBREINDEX (cmsDocument)
DBCC DBREINDEX (cmsContentXml)
DBCC DBREINDEX (umbracoDomains)
DBCC DBREINDEX (umbracoUser2NodePermission)
DBCC DBREINDEX (umbracoNode)
DBCC DBREINDEX (cmsContent)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment