Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Find the records which cause your not trusted check constraint to fail
/************** 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
You can’t perform that action at this time.