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) = ;

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

;

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

;

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'

  )

;

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 : 6/2/2016

• Backlinks include_pagepage_componentbacklinks