Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_AppRespondantFiles_AppRespondants'. 
The conflict occurred in database 'CMS(vanilla)', table 'AppRespondantFiles', column 'AppRespondantsID'.
The statement has been terminated.

/****************************************************************************************************  *********
 Purpose: This script will clear and reset the CMS tables
****************************************************************************************************  *********/
-- Instance ID of the Home Instance
DECLARE @intHomeInstanceID int
SET @intHomeInstanceID = 1
 
-- Start ID from which to begin deleting auto-generated Secuirty Items
DECLARE @intSecurityItemID int
SET @intSecurityItemID = 102
 
-- Start ID from which to begin deleting auto-generated Secuirty Items
DECLARE @intNavMenuItemID int
SET @intNavMenuItemID = 102
 
-- Delete Logs & Security
 
DELETE FROM SecurityUsers_SecurityRoles WHERE SecurityUsersID <> 1
 
DELETE FROM LogEvents
 
DELETE FROM SecurityUsers WHERE ID <> 1
 
-- Delete Versions except home
 
DELETE FROM CMVersionRelationships
 
DELETE FROM  CMVersionAttributeFIles
WHERE CMVersionAttributeID IN 
 (
 SELECT ID 
 FROM CMVersionAttributes  
 WHERE CMVersionID NOT IN 
  (
  SELECT ID 
  FROM CMVersions  
  WHERE CMInstanceID=@intHomeInstanceID
  )
 )
 
DELETE FROM  CMVersionAttributeValues
WHERE CMVersionAttributeID IN 
 (
 SELECT ID 
 FROM CMVersionAttributes  
 WHERE CMVersionID NOT IN 
  (
  SELECT ID 
  FROM CMVersions  
  WHERE CMInstanceID=@intHomeInstanceID
  )
 )
 
DELETE FROM  CMVersionAttributes
WHERE CMVersionID NOT IN 
 (
 SELECT ID 
 FROM CMVersions  
 WHERE CMInstanceID=@intHomeInstanceID
 )
 
DELETE FROM  CMVersionFriendlyURL
WHERE CMVersionID NOT IN 
 (
 SELECT ID 
 FROM CMVersions  
 WHERE CMInstanceID=@intHomeInstanceID
 )
 
DELETE FROM  CMVersionMetaTags
WHERE CMVersionID NOT IN 
 (
 SELECT ID 
 FROM CMVersions  
 WHERE CMInstanceID=@intHomeInstanceID
 )
 
DELETE FROM CMVersions WHERE CMInstanceID<>@intHomeInstanceID
 
-- Delete CMFiles and Folders
 
DELETE FROM CMFileImages
 
DELETE FROM CMFiles
WHERE ID NOT IN
 (
  SELECT DISTINCT CMFileID FROM CMVersionAttributeFiles
 )
 
DELETE FROM CMFolders
WHERE ID NOT IN 
 (
  SELECT DISTINCT CMFolderID FROM CMFiles
 )
 
-- Delete Applications
 
DELETE FROM AppResponses
 
DELETE FROM AppRespondants
 
--DELETE FROM AppQuestionsOptions
 
DELETE FROM AppQuestions
 
DELETE FROM AppQuestionSections
 
DELETE FROM AppApplications
 
-- Delete Instances / Security / Navigation
 
DELETE FROM NavMenuItems_SecurityItems WHERE SecurityItemsID > @intSecurityItemID
 
DELETE FROM CMInstances 
WHERE SecurityItemID > @intSecurityItemID OR SecurityItemID IS NULL AND ID <> @intHomeInstanceID
 
DELETE FROM SecurityItems_SecurityAreas WHERE SecurityItemsID > @intSecurityItemID
 
DELETE FROM SecurityItems WHERE ID > @intSecurityItemID
 
DELETE FROM NavMenuItems WHERE ID > @intNavMenuItemID
 
-- Delete Objects
 
DELETE FROM CMObjectAttributeValues
WHERE CMObjectsAttributeID IN 
 (
 SELECT ID
 FROM CMObjectAttributes
 WHERE CMObjectID NOT IN 
  (
  SELECT CMObjectID
  FROM CMInstances
  )
 )
 
DELETE FROM CMObjectRelationshipAttributes
 
DELETE FROM CMObjectRelationshipObjects
 
DELETE FROM CMObjectRelationships
 
DELETE FROM CMObjectAttributes 
WHERE CMObjectID NOT IN 
 (
 SELECT CMObjectID
 FROM CMInstances
 )
 
DELETE FROM CMObjectTemplates 
WHERE CMObjectID NOT IN 
 (
 SELECT CMObjectID
 FROM CMInstances
 )
 
DELETE FROM CMObjects 
WHERE ID NOT IN 
 (
 SELECT CMObjectID
 FROM CMInstances
 )
 
-- Re-generate Security Lookups
 
exec spxSecurityPopulateLookups
 
-- Re-seed tables
 
/*
 * Resets the identity columns on all tables in the database (handy after you've cleared out a load of data
 * and you want to reuse the old IDs).
 * Note that this script doesnt bother checking whether a table actually has an identity column - it'll just
 * throw an error and move on if there isn't one.
 */
 
DECLARE @table VARCHAR(255)
 
DECLARE tablelist CURSOR FOR
 SELECT [name] FROM dbo.sysobjects where type = 'U'
 
OPEN tablelist
FETCH NEXT FROM tablelist INTO @table    
 
WHILE @@FETCH_STATUS = 0
BEGIN
 DBCC CHECKIDENT (@table, RESEED, 1)
 DBCC CHECKIDENT (@table, RESEED)
 FETCH NEXT FROM tablelist INTO @table
END
 
CLOSE tablelist
DEALLOCATE tablelist