Umbraco - delete version history for all content , Umbraco 7.5+

README.MD

#Tested on an Umbraco 7.5 instalation# If anything goes wrong with your database.. don't balme me.

Modifications include a great post at our.umbrac.com https://our.umbraco.org/forum/core/general/74365-remove-audit-trail-and-version-history

DeleteAllVersions.sql

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)