This query will delete all Transactions and their activities that are linked to the 'Test Company'. The 'Test Company' was added to the OOB v2.72 dataset, however changes were made to the database format in OOB v2.78D so this query only works for OOB v2.78D+. Transactions and activities linked to other companies will remain untouched.
For a query that works with OOB v2.72 to OOB v2.78C please visit this link .
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.
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]
;
– Disable Constraints
EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
;
DECLARE @Confirmation VarChar(10);
SET @Confirmation = 'Yes' – Comment this line if you are inserting directly into Control's report setup.
–SET @Confirmation = – Remove comments from this line if you are inserting directly into Control's report setup.
UPDATE Account
SET CreditBalance = 0
WHERE ID = 1001 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 = 1001 AND (@Confirmation = 'Yes')
;
DELETE FROM Journal
WHERE ID > 1000 AND AccountID = 1001 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 = 1001) 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 = 1001)
)
AND (@Confirmation = 'Yes')
;
DELETE FROM TransMod
WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM TransDetail
WHERE ID > 1000 AND TransheaderID IN(SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM TransVariation
WHERE ParentID IN (SELECT ID FROM Transheader WHERE ID > 1000 AND AccountID = 1001) AND (@Confirmation = 'Yes')
;
DELETE FROM Transheader WHERE ID > 1000 AND AccountID = 1001 AND (@Confirmation = 'Yes')
;
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')
;
DECLARE @MaxID INT;
SET @MaxID = (SELECT ISNULL(Max(ID),0)+1 FROM SetupStepsLog)
INSERT INTO SetupStepsLog (ID, ModifiedDate, ModifiedByComputer, ModifiedByUser, Description)
VALUES(
@MaxID
, 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'