This query will delete all Transactions and their activities that are linked to the 'Test Company' which an Account.ID = 1001. If your test company has a different ID you can change the @AccountID parameter accordingly.

HIGH. This is a series of delete queries which will permanently delete data from your database. Be sure to create a backup of your database before running these queries.

BEGIN TRANSACTION;
BEGIN TRY
 
    IF EXISTS (SELECT *
      FROM sys.foreign_keys
      WHERE object_id = OBJECT_ID(N'dbo.FK_TransPart_TransDetail')
      AND parent_object_id = OBJECT_ID(N'dbo.TransPart')
    )
    ALTER TABLE TransPart DROP CONSTRAINT [FK_TransPart_TransDetail]
    ;
 
    DECLARE @AccountID INT = 1001;
    DECLARE @Confirmation VARCHAR(10) = 'Yes';
    --DECLARE @Confirmation VarChar(10) = ;
 
	-- Disable Constraints
    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
    ;
    UPDATE ACCOUNT
    SET CreditBalance = 0
    WHERE ID = @AccountID AND (@Confirmation = 'Yes')
    ;
    UPDATE JOURNAL
    SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Journal.TransactionID)
    WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes')
    ;
    UPDATE LEDGER
    SET AccountID = (SELECT AccountID FROM Transheader WHERE ID = Ledger.TransactionID)
    WHERE TransactionID IS NOT NULL AND (@Confirmation = 'Yes')
    ;
    DELETE FROM Ledger
    WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes')
    ;
    DELETE FROM Journal
    WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ContactActivity
    WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes')
    ; 
    DELETE FROM Payment
    WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Journal) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransPart
    WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransDetailParam
    WHERE ID > 1000 
        AND ParentID IN( SELECT ID FROM TransDetail
                            WHERE ID > 1000 AND TransheaderID IN 
                        (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID)
                        )
         AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransMod
    WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransDetail
    WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransVariation
    WHERE ParentID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkGroupTransDetailLink WHERE TransDetailID NOT IN (SELECT ID FROM TransDetail) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkGroup WHERE TransHeaderID NOT IN (SELECT ID FROM TransHeader WHERE ID > 1000) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkGroupStatusHistory WHERE ArtworkGroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkLog WHERE ArtworkGroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkNotificationHistory WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkItem WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkComment WHERE ArtworkItemID NOT IN (SELECT ID FROM ArtworkItem) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkProofFile WHERE ArtworkItemID NOT IN (SELECT ID FROM ArtworkItem) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkDigestEntry WHERE GroupID NOT IN (SELECT ID FROM ArtworkGroup) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM Transheader WHERE ID > 1000 AND AccountID = @AccountID AND (@Confirmation = 'Yes')
    ;
    DELETE FROM ArtworkPlayer WHERE TransHeaderID NOT IN (SELECT ID FROM TransHeader WHERE ID > 1000 AND ('Yes' = 'Yes'))
    ;
    DELETE FROM ArtworkPlayerRoleLink WHERE PlayerID NOT IN (SELECT ID FROM ArtworkPlayer)
    ;
    DELETE FROM VendorTransDetail
    WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransDetailGraphic
    WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transdetail) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransHeaderUserField
    WHERE ID > 1000 AND ID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM TransTax
    WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM Shipments
    WHERE ID > 1000 AND TransHeaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM PartUsageCard
    WHERE ID > 1000 AND TransheaderID NOT IN(SELECT ID FROM Transheader) AND (@Confirmation = 'Yes')
    ;
    DELETE FROM InventoryLog
    WHERE ID > 1000 AND TransDetailID NOT IN (SELECT ID FROM TransDetail) AND (@Confirmation = 'Yes')
    ;
	IF @Confirmation = 'Yes' 
	BEGIN
		EXEC dbo.[PrCA.Job.ReLinkAllTransactions];
	END
	ELSE 
		SELECT 'Yes'
	;
    DECLARE @NextID INT;
    SET @NextID = (SELECT COALESCE(MAX(ID)+1,1) FROM SetupStepsLog);
 
    INSERT INTO SetupStepsLog
    VALUES(
      @NextID
      , GetDate()
      , HOST_Name()
      , SUSER_NAME()
      , 'Purge All Info Related to Test Company'
      )
    ;
-- Enable Constraints
	EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 
	;
	SELECT 'Yes'
	; 
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
 
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;
 
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
  • Revised : 11/28/2017
You could leave a comment if you were logged in.