Alterian CRC formally Immediacy : Wiping the Database Clean

Today’s post can be pretty dangerous, so use with EXTREME caution. When you are developing, it’s really useful to be able to reset your database, for example before you hand it over to the client. If you want to completely delete everything from your database but still have a working site then use this script :

 /* Run this SQL script to create a completely blank Immediacy database. */
DELETE Abbreviation
DELETE AccessCheck
DELETE AuditTrail
DELETE content_items
DELETE cust_Forms
DELETE deleted_pages
DELETE Editor_Favourites
DELETE eh_history
DELETE ImageCat
DELETE ImageCategory
DELETE immMailToSend
DELETE [index]
DELETE indexQueue
DELETE License
DELETE Members
DELETE Membership
DELETE Messages
DELETE META_Default
DELETE META_Default_Edited
DELETE META_Extended
DELETE META_Extended_Edited
DELETE META_Description WHERE MetaDataID NOT IN (1,2,5)
IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('META_Delete')) DELETE META_Delete
DELETE ObjectDef
DELETE ObjectDeleted
DELETE Objects
DELETE ObjectTypes
DELETE [Option]
DELETE page_changes
DELETE page_changes_eh
DELETE page_data WHERE p_page_id <> 0
UPDATE page_data SET p_version=1,p_content = ' blank.httWelcome to Immediacy
]]>' WHERE p_page_id = 0 DELETE PageLock DELETE PageMessages DELETE PageObjects DELETE PageRoles DELETE pageurls DELETE PageXMLArchive DELETE Session DELETE SessionData DELETE Sitetracking DELETE Submissions DELETE submitted_changes DELETE submitted_changes_eh DELETE subscriptions UPDATE p_system SET nPageID = 1 UPDATE updatedata SET locked = 0, lastupdatedate = GetDate() DELETE UploadState DELETE Upload_Changes DELETE Userlock IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('WebKeys'))DELETE WebKeys IF EXISTS (SELECT * FROM sysobjects WHERE id=OBJECT_ID('WebPerm'))DELETE WebPerm DELETE Workflow DELETE WorkflowProgress DELETE XUGPage DELETE XUGUser DELETE XUserGroup DELETE XUserPage

If you do not want to completely wipe all your data but just the AUDIT archive (non essential stuff), then use this one instead :

DELETE AccessCheck
DELETE AuditTrail
DELETE deleted_pages
DELETE Editor_Favourites
DELETE eh_history
DELETE immMailToSend
DELETE messages
DELETE page_changes
DELETE page_changes_eh
UPDATE page_data set p_version = '1'
DELETE PageLock
DELETE PageMessages
DELETE PageXMLArchive
DELETE Session
DELETE SessionData
DELETE Submissions
DELETE submitted_changes
DELETE submitted_changes_eh
UPDATE updatedata SET locked = 0, lastupdatedate = GetDate()
DELETE Upload_Changes
DELETE UploadState
DELETE Userlock
DELETE WebKeys
DELETE WebPerm
DELETE WebUpload
DELETE WorkFlowProgress

Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge

More Posts