Mark items as unpublished in Tridion through database

TcmSetUnpublished.sql

-- This query uses the default stored procedure to set the publish state for any published items to unpublished
-- Note: Do NOT run this on a production SDL Tridion instance, as this activity is not officially supported by SDL Tridion.

DECLARE @publicationTargetId INT
DECLARE @publicationId INT
DECLARE @referenceId INT
DECLARE @itemType INT
DECLARE @state INT
DECLARE @trusteeId INT
DECLARE @templateReferenceId INT
DECLARE @templateItemType INT
DECLARE @stateChangeDate DATETIME

DECLARE @publicationTitle nvarchar(200)
DECLARE @publicationTargetTitle nvarchar(200)

DECLARE itemCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [PUBLICATION_TARGET_ID], [PUBLICATION_ID], [REFERENCE_ID], [ITEM_TYPE], [TRUSTEE_ID], [TEMPLATE_REFERENCE_ID], [TEMPLATE_ITEM_TYPE]
    FROM [dbo].[PUBLISH_STATES]
    WHERE [STATE] = 1
	
OPEN itemCursor
FETCH NEXT FROM itemCursor INTO @publicationTargetId, @publicationId, @referenceId, @itemType, @trusteeId, @templateReferenceId, @templateItemType
WHILE @@FETCH_STATUS = 0 BEGIN

	SELECT  @publicationTitle = TITLE
	FROM PUBLICATIONS
	WHERE ID = @publicationId

	SELECT @publicationTargetTitle = TITLE
	FROM PUBLICATION_TARGETS
	WHERE ID = @publicationTargetId
	
    PRINT 'Unpublishing tcm:' + 
		CAST(@publicationId AS nvarchar(30)) + '-' + 
		CAST(@referenceId AS nvarchar(30)) + '-' +
		CAST(@itemType AS nvarchar(30)) + 
		'from ' + 
		@publicationTitle + ' (' + @publicationTargetTitle + ')'
	
    EXEC [EDA_SYSTEM_SETPUBLISHSTATE]  @publicationTargetId, @publicationId, @referenceId, @itemType, 0, @trusteeId, @templateReferenceId, @templateItemType, NULL

    FETCH NEXT FROM itemCursor INTO @publicationTargetId, @publicationId, @referenceId, @itemType, @trusteeId, @templateReferenceId, @templateItemType
END

CLOSE itemCursor
DEALLOCATE itemCursor