Last active
November 16, 2021 09:40
-
-
Save EitanBlumin/bfa6b2860e9a27bd47657a94d840f864 to your computer and use it in GitHub Desktop.
Find the records which cause your not trusted check constraint to fail (more info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/ )
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/************** Find Invalid Records *************** | |
Author: Eitan Blumin | |
****************************************************/ | |
DECLARE | |
@Constraint SYSNAME = 'CK_Name' | |
, @PrintOnly BIT = 0 | |
DECLARE | |
@TableID INT, | |
@CheckDefinition NVARCHAR(MAX), | |
@CMD NVARCHAR(MAX) | |
SELECT | |
@TableID = parent_object_id, | |
@CheckDefinition = [definition] | |
FROM sys.check_constraints | |
WHERE name = @Constraint | |
IF @CheckDefinition IS NULL | |
BEGIN | |
RAISERROR(N'Check constraint %s was not found in current database.', 16, 1, @Constraint); | |
GOTO Quit; | |
END | |
SET @CMD = N'SELECT * | |
FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(@TableID)) + '.' + QUOTENAME(OBJECT_NAME(@TableID)) + N' | |
WHERE NOT | |
(' + @CheckDefinition + N')' | |
PRINT @CMD | |
IF @PrintOnly = 0 | |
EXEC (@CMD); | |
Quit: |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment