CREATE PROCEDURE [Common].[uspDeleteBaseContent] @EmployeeId INT, @BaseId UniqueIdentifier AS BEGIN SET NOCOUNT ON DROP TABLE IF EXISTS #ChangesetsToBeDeleted PRINT 'Library Sanitization Started.' BEGIN TRANSACTION; BEGIN TRY -- Creation of temporary tables to be used within the SQL CREATE TABLE #ChangesetsToBeDeleted ( ChangesetID UNIQUEIDENTIFIER NOT NULL ) -- Identify the Changesets that should be deleted from all the tables INSERT INTO #ChangesetsToBeDeleted (ChangesetID ) SELECT CC.ID FROM Common.Changesets CC WHERE CC.Status = 'A' AND BaseId=@BaseId --************************************************HARD DELETING RECORDS FROM QUESTIONNAIRE TABLES************************************* DELETE AA FROM Questionnaire.ClearAnswerActions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.SingleSignOffActions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.SetDefaultValueActions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.ScopeConditions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.FormulaConditions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.FormattingActions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.ItemActions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.RowFieldAnswerOptionValueTranslations AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.RowFieldAnswerOptionValues AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.RowFieldPropertyValueTranslations AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.RowFieldPropertyValues AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.QuestionConditions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.RowFieldAnswerOptions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.QuestionnaireExpressions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.TableRowFields AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.QuestionnaireFormulas AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.TableColumnValueTranslations AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.TableColumnValues AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.TableColumns AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.TableElements AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.FieldValueTranslations AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.FieldValues AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Fieldtags AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Actions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.AnswerOptionValueTranslations AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.AnswerOptionValues AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.AnswerOptions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Conditions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Rules AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Fields AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM Questionnaire.Questionnaires AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId --************************************************HARD DELETING RECORDS FROM AMT SCHEMA TABLES************************************* DELETE FROM DUH FROM AMT.DocumentUploadHistory DUH JOIN AMT.ItemVersions IV ON DUH.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM TVH FROM Common.TagVersionHistory TVH JOIN #ChangesetsToBeDeleted BB ON TVH.ChangesetId = BB.ChangesetId DELETE FROM DTP FROM AMT.DocumentTranslationProperties DTP INNER JOIN AMT.ItemVersionTranslations IVT ON DTP.ItemVersionTranslationId = IVT.Id JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM IT FROM AMT.ITEMTAGS IT JOIN AMT.ItemVersions IV ON IT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM RT FROM AMT.Relationships RT JOIN AMT.ItemVersions IV ON RT.SourceItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM RT FROM AMT.Relationships RT JOIN AMT.ItemVersions IV ON RT.TargetItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM DU FROM AMT.DocumentUpload DU JOIN AMT.ItemVersions IV ON DU.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM IVT FROM AMT.ItemVersionTranslations IVT JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM IVT FROM AMT.ItemVersionMasterTranslations IVT JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId DELETE FROM IVT FROM Amt.ItemVersionTranslations IVT JOIN Common.LanguageVersions LV ON IVT.LanguageVersionId = LV.Id JOIN #ChangesetsToBeDeleted BB ON LV.ChangesetId = BB.ChangesetId --************************************************HARD DELETING RECORDS FROM COMMON SCHEMA TABLES************************************************* DELETE FROM TR FROM Common.TagRelationships TR JOIN Common.TagVersions TV ON TR.SourceTagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId DELETE FROM TR FROM Common.TagRelationships TR JOIN Common.TagVersions TV ON TR.TargetTagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId DELETE FROM TR FROM Common.TagVersionTranslations TR JOIN Common.TagVersions TV ON TR.TagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId DELETE FROM TR FROM Common.TagVersionTranslations TR JOIN Common.LanguageVersions LV ON TR.LanguageVersionId = LV.Id JOIN #ChangesetsToBeDeleted BB ON LV.ChangesetId = BB.ChangesetId --************************************************HARD DELETING RECORDS FROM MAIN TABLES******************************************************* DELETE AA FROM AMT.ItemVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId UPDATE LVA SET LVA.PreviousLanguageVersionId = NULL FROM Common.LanguageVersions LVA JOIN Common.LanguageVersions LVB ON LVA.PreviousLanguageVersionId = LVB.Id JOIN #ChangesetsToBeDeleted BB ON LVB.ChangesetId = BB.ChangesetId DELETE AA FROM Common.LanguageVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId UPDATE TVA SET TVA.PreviousTagVersionId = NULL FROM Common.TagVersions TVA JOIN Common.TagVersions TVB ON TVA.PreviousTagVersionId = TVB.Id JOIN #ChangesetsToBeDeleted BB ON TVB.ChangesetId = BB.ChangesetId DELETE AA FROM Common.TagVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId DELETE AA FROM AMT.BaseSettings AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId UPDATE Common.Changesets SET Status = 'D', ModifiedAt = GETUTCDATE(), ModifiedBy = @EmployeeId WHERE Id IN ( SELECT ChangesetId FROM #ChangesetsToBeDeleted ) --************************************************HARD DELETION ENDED****************************************************************** --************************************************CLEANING UNWANTED DATA****************************************************************** DELETE AA FROM Common.ValidationResults AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId PRINT 'Library Sanitization Completed.' END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH IF @@TRANCOUNT > 0 BEGIN COMMIT TRANSACTION; --drop all temp tables DROP TABLE #ChangesetsToBeDeleted END SET NOCOUNT OFF END; GO