Uncategorized

Verify all constraints WITH CHECK CHECK and report not trusted remaining

SET NOCOUNT ON 

DECLARE @FKS TABLE ([Name] NVARCHAR(300) NOT NULL PRIMARY KEY, [Stmt] NVARCHAR(MAX) NOT NULL)
DECLARE @Name sysname
DECLARE @Stmt NVARCHAR(MAX)
DECLARE @Msg NVARCHAR(MAX)

INSERT INTO @FKS
	( [Name], [Stmt] )
	SELECT 
		SCHEMA_NAME([schema_id]) + '.' + [name], 'ALTER TABLE ['+ OBJECT_SCHEMA_NAME([parent_object_id]) + '].[' + OBJECT_NAME([parent_object_id]) + '] WITH CHECK CHECK CONSTRAINT ' + QUOTENAME([name]) + ';'
	FROM [sys].[foreign_keys] 
	ORDER BY [name]

WHILE EXISTS (SELECT * FROM @FKS)
BEGIN
    
	SELECT TOP(1)
		@Name = [Name], @Stmt = [Stmt]
	FROM @FKS
	ORDER BY [Name]

	RAISERROR(@Name, 10, 1) WITH NOWAIT

	BEGIN TRY
		
		EXEC sp_executesql @Stmt
	
	END TRY
	BEGIN CATCH
	
		SET @Msg = ERROR_MESSAGE()
	    
		RAISERROR(@Msg, 16, 1)
	
	END CATCH

	DELETE FROM @FKS WHERE [Name] = @Name

END

SELECT * FROM [sys].[foreign_keys] WHERE [is_not_trusted] = 1
GO