Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active November 16, 2021 09:40
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save EitanBlumin/bfa6b2860e9a27bd47657a94d840f864 to your computer and use it in GitHub Desktop.
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/ )
/************** 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