2LUE4wXG

1-- Create a temporary table for all documents which are published and not in the recycle bin
2CREATE TABLE #Nodes (id int)
3-- Delete all rows if the table exists before
4TRUNCATE TABLE #Nodes
5
6-- Insert all nodeIds from all documents which are published and not in the recycle bin
7INSERT INTO #Nodes 
8    SELECT N.id 
9    FROM umbracoNode N
10        INNER JOIN cmsDocument D ON N.ID = D.NodeId
11    WHERE nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972'
12        AND [path] NOT LIKE '%-20%'
13        AND D.Published = 1
14
15-- Create a temporary table for all versionId's to delete
16CREATE TABLE #Versions (id UniqueIdentifier)
17-- Delete all rows if it exists before
18TRUNCATE TABLE #Versions
19
20-- Insert all versionId's from all nodeIds in the #Nodes table 
21-- and where published is set to false and newest is set to false
22INSERT INTO #Versions
23    SELECT versionId 
24    FROM cmsDocument 
25    WHERE nodeId IN (SELECT id FROM #Nodes) 
26        AND published = 0 AND newest = 0
27
28-- DELETE all versions from cmsPropertyData, cmsContentVersion, cmsDocument
29-- from the nodes which are published and which are not in the recycle bin 
30-- and which are not published and which are not the newest
31
32DELETE FROM cmsPreviewXml WHERE versionId IN (SELECT id FROM #Versions)
33DELETE FROM cmsPropertyData WHERE VersionId IN (SELECT id FROM #Versions)
34DELETE FROM cmsContentVersion WHERE VersionId IN (SELECT id FROM #Versions)
35DELETE FROM cmsDocument WHERE VersionId IN (SELECT id FROM #Versions)
36
37DROP TABLE #Versions
38DROP TABLE #Nodes