purge_orders_and_customers_using

**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:
PURGE all Orders, Estimates, POs, RDs, Bills
PURGE all Customers & Contacts
PURGE all Customized Report Settings
PURGE all Addresses and Phone Numbers for Companies, Contact, Orders
PURGE all Activities (Journals) including Payments, Contact Activities, etc.
PURGE all GL Entries
PURGE all Time Cards
PURGE all Inventory Entries
RESET all products to NOT track inventory
RESET the next order, estimate, and estimate numbers
RESET all parts to not use calendars
SQL

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

Updated 2016-07.