WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.
This document describes the SQLs used to:
WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.
WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.
– Replace this name with the database you want to purge
USE [StoreData-ToEmpty]
– DELETE ALL Report Settings
DELETE FROM REPORTMENUITEM
WHERE ID >= 1000
;
– DELETE ALL CUSTOMERS
DELETE FROM ACCOUNT
WHERE ID > 1001 – Leave 10 for Walk-In and 1001 for Test Company
;
– DELETE ALL CONTACTS
DELETE FROM ACCOUNTCONTACT
WHERE ID >= 1 and AccountID NOT IN (SELECT ID FROM Account)
;
– DELETE ALL PHONE NUMBERS FOR ALL COMPANIES
DELETE FROM PHONENUMBER
WHERE PARENTCLASSTYPEID = 2000 AND ParentID NOT IN (SELECT ID FROM Account)
;
– DELETE ALL PHONE NUMBERS FOR ALL CONTACTS
DELETE FROM PHONENUMBER
WHERE PARENTCLASSTYPEID = 3000 AND ParentID NOT IN (SELECT ID FROM AccountContact)
;
– DELETE ALL COMPANY ADDRESS LINKS
DELETE FROM ADDRESSLINK
WHERE PARENTCLASSTYPEID = 2000 AND ParentID NOT IN (SELECT ID FROM Account)
;
– DELETE ALL CONTACT ADDRESS LINKS
DELETE FROM ADDRESSLINK
WHERE PARENTCLASSTYPEID = 3000 AND ParentID NOT IN (SELECT ID FROM AccountContact)
;
– DELETE ALL COMPANY UDFS
DELETE FROM AccountUserField
WHERE ID NOT IN (SELECT ID FROM Account)
;
– DELETE ALL CONTACT UDFS
DELETE FROM AccountContactUserField
WHERE ID NOT IN (SELECT ID FROM AccountContact)
;
– Removes login/lo;ut entries.
DELETE FROM Journal
WHERE JournalActivityType IN (30,31)
;
– Removes part usage entries
DELETE FROM Journal
WHERE JournalActivityType = 17
;
– Removes closeouts
DELETE FROM Journal
WHERE JournalActivityType = 13
;
– Removes CloseOuts
DELETE FROM CLOSEOUT
WHERE ID > 0
;
– Removes company creation entries
DELETE FROM Journal
WHERE JournalActivityType = 6
;
– Removes report activities
DELETE FROM Journal
WHERE JournalActivityType = 27
;
– Removes timecards
DELETE FROM Journal
WHERE JournalActivityType = 1
;
DELETE FROM TimeCard
WHERE ID > 0
;
– Removes Activities
DELETE FROM ContactActivity
WHERE ID > 0
;
– Removes Activities from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 10
;
– Removes CalendarLinks
DELETE FROM CalendarLink
WHERE ID > 0
;
– Removes Payments
DELETE FROM Payment
WHERE ID > 0
;
– Removes CloseOuts
DELETE FROM CLOSEOUT
WHERE ID >= 0
;
– Removes timecards
DELETE FROM Journal
WHERE JournalActivityType = 1
;
DELETE FROM TimeCard
WHERE ID >= 0
;
– Removes CalendarLinks
DELETE FROM CalendarLink
WHERE ID >= 0
;
– Removes Payments
DELETE FROM Payment
WHERE ID >= 0
;
– Removes PartUsage Cards
DELETE FROM PartUsageCard
WHERE ID >= 1000
;
– Removes Activities for Payments from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 2
;
– Removes Activities for Backups from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType IN (14,24)
;
– Removes Activities for Imports from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType IN (11,21)
;
– Removes PartUsage Cards
DELETE FROM PartUsageCard
WHERE ID > 0
;
– Removes Activities for Macros from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 16
;
– Removes Activities for Macro Templates from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 42
;
– Removes Activities for Emails from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 42
;
– Removes Activities for Notes from Journal
DELETE FROM Journal
WHERE ID > 0 AND JournalActivityType = 15
;
– Removes any inventory log entries
DELETE FROM InventoryLog
WHERE ID >= 0
;
– Removes any inventory entries
DELETE FROM Inventory
WHERE ID >= 0
;
– Sets all parts to non-inventory items
UPDATE Part
SET TrackInventory = 0
WHERE ID >= 0
;
DELETE FROM Payroll
WHERE ID >= 1000
;
DELETE FROM PayrollPaycheck
WHERE ID >= 1000
;
DELETE FROM PayrollPaycheckPayItem
WHERE ID >= 1000
;
DELETE FROM PayrollPTO
WHERE ID >= 1000
;
DELETE FROM Payroll
WHERE ID >= 1000
;
– Delete the transheader record first then run the sql statements below
DELETE FROM TransHeader
WHERE ID >= 0
;
DELETE FROM Transdetail
WHERE ID >= 0
;
DELETE FROM Transvariation
WHERE ID >= 0
;
DELETE FROM TransHeaderUserField
WHERE ID >= 0
;
DELETE FROM TransDetailGraphic
WHERE ID >= 0
;
DELETE FROM TransDetailParam
WHERE ID >= 0
;
DELETE FROM TransMod
WHERE ID >= 0
;
DELETE FROM TransPart
WHERE ID >= 0
;
DELETE FROM TransTax
WHERE ID >= 0
;
DELETE FROM VendorTransDetail
WHERE ID >= 0
;
DELETE FROM Ledger
WHERE ID >= 0
;
DELETE FROM Journal
WHERE TransactionID NOT IN
(SELECT id FROM transheader)
AND ID >= 0
;
DELETE FROM Payment
WHERE ID >= 0
;
– Delete Part Calendars
DELETE FROM PricingElement
WHERE classtypeid = 12075
;
– Reset Parts to not use Calendars
UPDATE Part
SET ShowOnProductionSchedule = 0, InstancesAllowed = 0
;
– Delete all Addresses that are not associated with anything.
DELETE FROM ADDRESS
WHERE ID > 0 AND ID NOT IN (
SELECT BillingAddressID FROM
(
SELECT BillingAddressID, CAST('Account BillingAddressID' AS NVarChar(50)) AS SOURCE FROM Account
UNION SELECT ShippingAddressID, CAST('Account ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM Account
UNION SELECT AddressID, CAST('GLAccount AddressID' AS NVarChar(50)) AS SOURCE FROM GLAccount
UNION SELECT BillingAddressID, CAST('AccountContact BillingAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact
UNION SELECT ShippingAddressID, CAST('AccountContact ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact
UNION SELECT PaymentAddressID, CAST('Contact PaymentAddressID' AS NVarChar(50)) AS SOURCE FROM AccountContact
UNION SELECT AddressID, CAST('AddressLink AddressID' AS NVarChar(50)) AS SOURCE FROM AddressLink
UNION SELECT AddressID, CAST('Employee AddressID' AS NVarChar(50)) AS SOURCE FROM Employee
UNION SELECT MailAddressID, CAST('Employee AddressID' AS NVarChar(50)) AS SOURCE FROM Employee
UNION SELECT AddressID, CAST('EmployeeGroup AddressID' AS NVarChar(50)) AS SOURCE FROM EmployeeGroup
UNION SELECT AddressID, CAST('Store AddressID' AS NVarChar(50)) AS SOURCE FROM Store
UNION SELECT BillingAddressID, CAST('Store BillingAddressID' AS NVarChar(50)) AS SOURCE FROM Store
UNION SELECT PickUpAddressID, CAST('Store PickUpAddressID' AS NVarChar(50)) AS SOURCE FROM Store
UNION SELECT ShippingAddressID, CAST('Store ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM Store
UNION SELECT InvoiceAddressID, CAST('TransHeader InvoiceAddressID' AS NVarChar(50)) AS SOURCE FROM TransHeader
UNION SELECT ShippingAddressID, CAST('TransHeader ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM TransHeader
UNION SELECT ShippingAddressID, CAST('TransDetail ShippingAddressID' AS NVarChar(50)) AS SOURCE FROM TransDetail
) WorkingTable
WHERE BillingAddressID IS NOT NULL AND BillingAddressID > 0
)
;
– Delete all Addresslink records that are not associated with an Address
DELETE FROM AddressLink
WHERE ID > 0
AND AddressID NOT IN (SELECT ID FROM Address)
;
– Set Next Company Record
UPDATE SEQUENCE
SET NextNumber = 200
WHERE ClassTypeID = 2000
;
– Set Next Order Record
UPDATE SEQUENCE
SET NextNumber = 1000
WHERE ClassTypeID = 10001
;
– Set Next Estimate Record
UPDATE SEQUENCE
SET NextNumber = 500
WHERE ClassTypeID = 10002
;
WARNING: These queries will PERMANENTLY delete the affected data. There is no way to recover this unless you have a separate backup (and that won't be easy). This should only be carried out by a skilled implementer and SQL practitioner.