Umbraco4.7_DB_Hacks.sql

-- GET RID OF OLD VERSIONS
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
    DROP TABLE #tmp

Select TOP 10000 VersionID into #tmp FROM cmsDocument
WHERE versionID NOT IN  (SELECT D.versionId FROM cmsDocument D 
    WHERE D.versionId IN (SELECT versionId FROM (SELECT CV.versionId, published, newest, RANK() OVER(ORDER BY CV.versionDate DESC) RowNum   
                                                       FROM cmsContentVersion CV JOIN cmsDocument DD ON CV.versionId = DD.versionId    
                                                                                                           WHERE DD.nodeId = D.nodeId) AS tmp     
                                   WHERE tmp.RowNum <= 3 OR tmp.published = 1 OR tmp.newest = 1)  )
                                   


DELETE FROM cmsPreviewXml WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsPropertyData WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsContentVersion WHERE VersionId IN (select #tmp.VersionId from #tmp)
DELETE FROM cmsDocument WHERE VersionId IN (select #tmp.VersionId from #tmp)







-- EMPTY THE RECYCLE BIN
begin transaction
-- Uncomment below to verify the number of nodes returned is the 
-- same as the number of nodes that is in the Recycle Bin
-- select * from umbracoNode where path like '%-20%' and id!=-20
-- Delete all 'related' nodes and table contents...
delete from cmsPreviewXml where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentVersion where contentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from umbracoRelation where parentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from umbracoRelation where childId in (select id from umbracoNode where path like '%-20%' and id!=-20)

ALTER TABLE umbracoNode NOCHECK CONSTRAINT FK_umbracoNode_umbracoNode
delete umbracoNode where id in (select id from umbracoNode where path like '%-20%' and id!=-20)
ALTER TABLE umbracoNode CHECK CONSTRAINT FK_umbracoNode_umbracoNode

-- delete the XML nodes....
delete from umbracoDomains WHERE domainRootStructureID in (SELECT id FROM umbracoNode WHERE path like '%-20%' and id != -20)
delete from umbracoNode where path like '%-20%' and id!=-20
commit transaction