Umbraco - delete version history for all content

Umbraco-Full-Cleanup.sql

/* This script deletes:
 * 1. Oprhan data
 * 2. All nodes from the trash
 * 3. All previous node versions which are not publish or not newest unpublished
 */

DECLARE @documentNodeObjectType uniqueidentifier = N'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'; 
DECLARE @trashPath varchar(5) = '%-20%';

PRINT 'orphan data'

/* DELETE NON-EXISTING DOCUMENTS */
delete from cmsDocument where nodeId not in (select id from umbracoNode)
;
/* CLEAN UNUSED CONTENT ROWS */
delete from cmsContent where nodeId not in (select id from umbracoNode)
;
/* CLEAN UNUSED VERSIONS */
delete from cmsContentVersion where contentid not in (select nodeId from cmsContent)
;

/* CLEAN UNUSED XML */
delete from cmsContentXml where nodeid not in (select nodeId from cmsContent)
;
/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE NODES */
delete from umbracoUser2NodeNotify where nodeId not in (select id from umbracoNode)
;

/* DELETE ALL NOTIFICATIONS THAT NO LONGER HAVE USERS */
delete from umbracoUser2NodeNotify where userId not in (select id from umbracoUser)
;
/* DELETE UMBRACO NODE DATA THAT IS FLAGGED AS A DOCUMENT OBJECT TYPE THAT DOESN'T EXIST IN THE CONTENT TABLE ANY LONGER */
delete from umbracoNode where id not in
(select nodeId from cmsContent) and nodeObjectType = @documentNodeObjectType
;
/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING USERS */ 
delete from umbracoUser2NodePermission where userId not in (select id from umbracoUser)
;

/* DELETE PERMISSIONS THAT RELATED TO NON-EXISTING NODES */
delete from umbracoUser2NodePermission where nodeId not in (select id from umbracoNode)
;
/* Though this should not have to be run because it's a new install, you need to clean the previews if you've been testing before the RC */
DELETE FROM cmsPreviewXml WHERE VersionID NOT IN (SELECT VersionId FROM cmsContentVersion)
;

PRINT 'recycle bin'
/* Clear recycle bin*/

-- Create a temporary table for all trashed node ids's to delete
CREATE TABLE #TrashNodeIds (id int)
TRUNCATE TABLE #TrashNodeIds
INSERT INTO #TrashNodeIds
	SELECT id FROM umbracoNode WHERE PATH LIKE @trashPath AND id > 0 AND nodeObjectType = @documentNodeObjectType 

delete from cmsPreviewXml where versionID in (select versionid from cmsContentVersion where ContentId in (select nodeId from cmsContent where nodeId in (select id from #TrashNodeIds)))
delete from cmsContentVersion where ContentId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds))
delete from cmsPropertyData where contentNodeId in (select id from #TrashNodeIds)
delete from cmsContentXML where nodeId in (select nodeid from cmsContent where nodeId in (select id from #TrashNodeIds))
delete from cmsDocument where nodeId in (select id from #TrashNodeIds)
delete from cmsContent where nodeId in (select id from #TrashNodeIds)
delete from umbracoUser2NodePermission where nodeId in (select id from #TrashNodeIds)
delete from umbracoRelation where parentId in (select id from #TrashNodeIds)
delete from umbracoRelation where childId in (select id from #TrashNodeIds)
delete from umbracoNode where id in (select id from #TrashNodeIds)

DROP TABLE #TrashNodeIds

PRINT 'unused document verions'
/* clean unused document versions*/

-- 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 where not published and not newest unpublished
INSERT INTO #Versions
    SELECT DISTINCT d.versionId 
    FROM cmsDocument d
	JOIN umbracoNode n on d.nodeId = n.id
    WHERE d.published = 0 AND d.newest = 0 AND d.nodeId > 0
	AND n.nodeObjectType = @documentNodeObjectType

-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
-- from the nodes which are not published and which are newest not published
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