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.

code_formatsql

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';

1. -DECLARE @Confirmation VarChar(10) = ;

1. - 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;

code

• Revised : 11/28/2017

• Backlinks include_pagepage_componentbacklinks