Thursday, November 2, 2017

Deleting Sitecore Items in SQL (PROCEED WITH CAUSION!)

Recently we had to delete a large number of items from Sitecore, and as we all know the task usually takes a long time to run when you kick it off from Content Editor.

If you absolutely must delete items quickly, you can try doing it in SQL. Bare in mind that it is not advisable to manipulate item data directly in the database, and if you are not very experienced in Sitecore, I would advise you not to use this script. However, if you know what you are doing, it might be handy.

So here you go:

DECLARE @parentId as uniqueidentifier;
DECLARE @RowsToProcess  int
DECLARE @CurrentRow     int
DECLARE @SelectCol1     uniqueidentifier
DECLARE @Items2Delete TABLE (RowID int not null primary key identity(1,1),ID uniqueidentifier)

set @parentId = '{38994C59-E601-4823-8377-3903940FCFCC}'

INSERT INTO @Items2Delete (ID)
SELECT [Descendant] as ID
  FROM [dbo].[Descendants]
  where Ancestor = @parentId
SET @RowsToProcess=@@ROWCOUNT
print  @RowsToProcess

INSERT INTO @Items2Delete (ID)
select ID from [dbo].[Items] where ParentID = @parentId
SET @RowsToProcess=@@ROWCOUNT
print  @RowsToProcess

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT
        @SelectCol1=ID
        FROM @Items2Delete
        WHERE RowID=@CurrentRow

declare @id as uniqueidentifier;

set @id = @SelectCol1
print @id

exec sp_executesql N'DELETE FROM [Items]
  WHERE [ID] = @itemId

  DELETE FROM [SharedFields]
  WHERE [ItemId] = @itemId

  DELETE FROM [UnversionedFields]
  WHERE [ItemId] = @itemId

  DELETE FROM [VersionedFields]
  WHERE [ItemId] = @itemId',N'@itemId uniqueidentifier',@itemId=@id

exec sp_executesql N'DELETE FROM [Descendants] WHERE [Descendant] = @itemId',N'@itemId uniqueidentifier',@itemId=@id
exec sp_executesql N' DELETE FROM [Links] WHERE [SourceItemID] = @itemID AND [SourceDatabase] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'
exec sp_executesql N'DELETE FROM [Tasks] WHERE [ItemID] = @itemID AND [Database] = @database',N'@itemID uniqueidentifier,@database nvarchar(6)',@itemID=@id,@database=N'master'

END

If you have done the same thing before and see any issue with this script, don't hesitate to comment. :)