-
Notifications
You must be signed in to change notification settings - Fork 51
/
Re-trust untrusted foreign keys.sql
53 lines (46 loc) · 1.61 KB
/
Re-trust untrusted foreign keys.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- Re-trust untrusted foreign keys
-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox
-- This script
DECLARE @CorrectedCount INT;
DECLARE @FailedCount INT;
DECLARE UntrustedForeignKeysCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT '[' + s.name + '].' + '[' + o.name + ']' AS TableName,
i.name AS FKName
FROM sys.foreign_keys i
INNER JOIN sys.objects o
ON i.parent_object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE i.is_not_trusted = 1
AND i.is_not_for_replication = 0
AND i.is_disabled = 0
ORDER BY o.name;
DECLARE @TableName AS VARCHAR(200);
DECLARE @FKName AS VARCHAR(200);
SET @CorrectedCount = 0;
SET @FailedCount = 0;
OPEN UntrustedForeignKeysCursor;
FETCH NEXT FROM UntrustedForeignKeysCursor
INTO @TableName,
@FKName;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
/*
This try-catch will allow the process to continue when a constaint fails to get re-trusted
*/
EXECUTE ('ALTER TABLE ' + @TableName + ' WITH CHECK CHECK CONSTRAINT [' + @FKName + ']');
SET @CorrectedCount = @CorrectedCount + 1;
END TRY
BEGIN CATCH
SELECT 'Failed table / KEY: ' + @TableName + ' / ' + @FKName;
SET @FailedCount = @FailedCount + 1;
END CATCH;
FETCH NEXT FROM UntrustedForeignKeysCursor
INTO @TableName,
@FKName;
END;
CLOSE UntrustedForeignKeysCursor;
DEALLOCATE UntrustedForeignKeysCursor;
SELECT CAST(@CorrectedCount AS VARCHAR(10)) + ' constraints re-trusted.';
SELECT CAST(@FailedCount AS VARCHAR(10)) + ' constraints unable to be re-trusted.';