mjXDJxSf

1IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'DetectOrphanDataBeforeUpgrade')
2DROP PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
3GO
4
5CREATE PROCEDURE [dbo].[DetectOrphanDataBeforeUpgrade]
6
7@TenantId   INT = NULL
8
9AS
10BEGIN
11
12DECLARE @OrphanAuditItems NVARCHAR(MAX)
13DECLARE @OrphanAuditAnswers NVARCHAR(MAX) 
14DECLARE @OrphanAuditQuestion NVARCHAR(MAX)
15
16------------------------------------------------------------------------------------------------
17/* Throw an error if the TenantId is NULL or Invalid.               */
18------------------------------------------------------------------------------------------------
19IF @TenantId IS NULL OR @TenantId NOT IN (SELECT Tenants FROM [Application].[dbo].[TenantIdNumber])
20
21BEGIN
22    THROW 51000, '@TenantId is invalid because it is NULL or does not exist.', 1;
23END
24
25
26------------------------------------------------------------------------------------------------
27/* Checks for Orphan records related to the Audits table                */
28------------------------------------------------------------------------------------------------
29SET @OrphanAuditItems = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems] 
30                                                    WHERE FK_Audit NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'
31
32SET @OrphanAuditAnswers = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditAnswers] 
33                                                        WHERE AuditItemId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditItems])'
34
35SET @OrphanAuditQuestion = N'SELECT COUNT(*) FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[AuditQuestion] 
36                                                        WHERE AuditId NOT IN (SELECT SystemID FROM [Dynamic].['+ CAST (@TenantId AS NVARCHAR) +'].[Audits])'
37
38EXEC (@OrphanAuditItems)
39EXEC(@OrphanAuditAnswers)
40EXEC(@OrphanAuditQuestion)
41
42END;