There may be a time that you want to upload a database but would prefer not to share all of your data. This script will leave all of the SalesLogix customizations intact but will remove all but 500 accounts. There is a section at the end of this script where you can add your custom tables. I have seen this script run for more then 2 hours so plan to wait quite a while.
This script is designed for SalesLogix prior to version 7.2.2 – proceed at your own risk.
–Custom Tables to be cleared need to be added at the bottom of the script
–********************************************************************************
–**—*** WARNING THIS QUERY CAN TAKE A LONG TIME TO RUN ***—***
–**—*** WARNING THIS QUERY IS NOT SLX SYNC AWARE ***—***
–********************************************************************************
–********************************************************************************
–**This query is what is used to limit what accounts will be kept on the DB.
–**All tables will drill down from this query.
–**To modify, put your condition(s) in the nested select statement in place of
–**(select top 500 accountid from account).
–********************************************************************************
–********************************************************************************
delete
from account where accountid not in (select top 500 accountid from account)Go
–********************************************************************************
–********************************************************************************
delete
from contact where accountid not in (select accountid from account)Go
delete
from opportunity where accountid not in (select accountid from account)Go
delete
from address where addressid not in (select addressid from address whereentityid
in (select accountid from account) orentityid
in (select contactid from contact) orentityid
in (select userid from userinfo) oraddressid in (select addressid from branchoptions))Go
delete from history where historyid not in(select historyid from history whereaccountid
in (select accountid from account) orcontactid
in (select contactid from contact) oropportunityid
in (select opportunityid from contact))Go
delete
from activity where activityid not in(select activityid from activity whereaccountid
in (select accountid from account) orcontactid
in (select contactid from contact) oropportunityid
in (select opportunityid from contact))Go
Delete
from user_activity where activityid not in (select activityid from activity)Go
Delete
from accountsummary where accountid not in (select accountid from account)Go
Delete
from accountproduct where accountid not in (select accountid from account)Go
Delete
from ACT_Userdef where contactid not in (select contactid from contact)Go
Delete
from association where associationid not in (select associationid from association wherefromid
in (select accountid from account) andtoid
in (select accountid from account))Go
Delete
from attachment where attachid not in(select attachid from attachment whereaccountid
in (select accountid from account) orcontactid
in (select contactid from contact) oropportunityid
in (select opportunityid from contact))Go
Delete
from campaigncontact where contactid not in (select contactid from contact)Go
Delete
from competitor where accountid not in (select accountid from account)Go
Delete
from competitor_contact where contactid not in (select contactid from contact)Go
Delete
from competitor_details where competitorid not in (select competitorid from competitor)Go
Delete
from competitorcustomer where competitorid not in (select competitorid from competitor)Go
Delete
from competitorproduct where competitorid not in (select competitorid from competitor)Go
Delete
from contact_leadsource where contactid not in (select contactid from contact)Go
Delete
from contactresponse where contactid not in (select contactid from contact)Go
Delete
from contract where accountid not in (select accountid from account)Go
Delete
from contractincident where contractid not in (select contractid from contract)Go
Delete
from contractitem where contractid not in (select contractid from contract)Go
Delete from GM_Profile where gm_profileid not in (select gm_profileid from gm_profile whereaccountid
in (Select accountid from account) orcontactid
in (select contactid from contact))Go
Delete
from litrequest where contactid not in (select contactid from contact)Go
Delete
litrequestitem where litreqid not in (select litreqid from litrequest)Go
Delete
from opportunity_campaign where opportunityid not in (select opportunityid from opportunity)Go
Delete
from opportunity_contact where opportunityid not in (select opportunityid from opportunity)Go
Delete
from opportunity_product where opportunityid not in (select opportunityid from opportunity)Go
Delete
from opportunity_salesstep where opportunityid not in (select opportunityid from opportunity)Go
Delete
from opportunity_user where opportunityid not in (select opportunityid from opportunity)Go
Delete
from resourceschedule where activityid not in (select activityid from activity)Go
Delete
from RMA where accountid not in (select accountid from account)Go
Delete
from rmareceivedproduct where rmaid not in (select rmaid from rma)Go
Delete
from rmashippedproduct where rmaid not in (select rmaid from rma)Go
Delete
from ticket where accountid not in (select accountid from account)Go
Delete
from ticketaccountproduct where ticketid not in (select ticketid from ticket)Go
Delete
from ticketactivity where ticketid not in (select ticketid from ticket)Go
Delete
from ticketactivityitem where ticketactivityid not in (select ticketid from ticket)Go
Delete
from tickethistory where ticketid not in (select ticketid from ticket)Go
Delete
from ticketproblem where ticketid not in (select ticketid from ticket)Go
Delete
from ticketsolution where ticketid not in (select ticketid from ticket)Go
–Custom Tables
Delete
from account2 where accountid not in (select accountid from account)Go
Delete
from contact2 where contactid not in (select contactid from contact)Go
Delete
from contactextension where contactid not in (select contactid from contact)Go
Print “All Finished, please run a full Database Mainteneance Rountine to Shrink DB”
