Contact us on: +44 (0)1432 617 006

The Site Doctor Blog

Footprints in the snow of a warped mind

< Back to Blog

Quickly delete Umbraco nodes by document type in SQL

Ever needed to delete all nodes by a document type or delete all nodes from the recycle bin in Umbraco? This little script should help.

Delete Umbraco nodes by Document Type Alias

BEGIN TRAN
 
DECLARE @Nodes TABLE (NodeId int)
 
INSERT INTO @Nodes (NodeId)
SELECT top 1000 n.id
FROM cmsContent C 
	INNER JOIN cmsContentType CT ON C.contentType = CT.nodeId 
	INNER JOIN umbracoNode N ON C.nodeId = N.id
WHERE CT.alias = '[Your Document Type Alias Here]'
 
select id, [text] from umbracoNode where id in (select NodeId from @Nodes)
 
delete from cmsPreviewXml where nodeId in (select NodeId from @Nodes)
delete from cmsContentVersion where contentId in (select NodeId from @Nodes)
delete from cmsDocument where nodeId in (select NodeId from @Nodes)
delete from cmsContentXML where nodeId in (select NodeId from @Nodes)
delete from cmsContent where nodeId in (select NodeId from @Nodes)
delete from cmsPropertyData where contentNodeId in (select NodeId from @Nodes)
delete from umbracoRelation where parentId in (select NodeId from @Nodes) OR childId in (select NodeId from @Nodes)
delete from cmsTagRelationship where nodeId in (select NodeId from @Nodes)
delete from umbracoNode where id in (select NodeId from @Nodes)
 
DELETE FROM @Nodes
 
ROLLBACK TRAN

Delete Umbraco nodes from recycle bin

BEGIN TRAN
 
DECLARE @Nodes TABLE (NodeId int)
 
INSERT INTO @Nodes (NodeId)
SELECT top 1000 n.id
FROM umbracoNode n
WHERE n.path like '%-20%' and id!=-20
 
select id, [text] from umbracoNode where id in (select NodeId from @Nodes)
 
delete from cmsPreviewXml where nodeId in (select NodeId from @Nodes)
delete from cmsContentVersion where contentId in (select NodeId from @Nodes)
delete from cmsDocument where nodeId in (select NodeId from @Nodes)
delete from cmsContentXML where nodeId in (select NodeId from @Nodes)
delete from cmsContent where nodeId in (select NodeId from @Nodes)
delete from cmsPropertyData where contentNodeId in (select NodeId from @Nodes)
delete from umbracoRelation where parentId in (select NodeId from @Nodes) OR childId in (select NodeId from @Nodes)
delete from cmsTagRelationship where nodeId in (select NodeId from @Nodes)
delete from umbracoNode where id in (select NodeId from @Nodes)
 
DELETE FROM @Nodes
 
ROLLBACK TRAN
Author: Tim on

Liked this post? Got a suggestion? Leave a comment