Skip to content

Instantly share code, notes, and snippets.

@ghotz

ghotz/evaluate-non-trusted-constraints.sql

Created May 8, 2024 10:49
Show Gist options
  • Save ghotz/ff4ea3708f0dfbd21964068fd505c662 to your computer and use it in GitHub Desktop.
Save ghotz/ff4ea3708f0dfbd21964068fd505c662 to your computer and use it in GitHub Desktop.
Download ZIP
Evaluate and fix non trusted constraint
Raw
evaluate-non-trusted-constraints.sql
SELECT
QUOTENAME(S1.[name]) AS [schema_name]
, QUOTENAME(O1.[name]) AS [table_name]
, QUOTENAME(C1.[name]) AS [constraint_name]
, N'FOREIGN_KEY_CONSTRAINT' AS [constraint_type]
, 'ALTER TABLE ' + QUOTENAME(S1.[name]) + '.' + QUOTENAME(O1.[name])
+ ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(C1.[name]) AS sql_stmt
FROM sys.foreign_keys AS C1
JOIN sys.objects AS O1 ON C1.parent_object_id = O1.[object_id]
JOIN sys.schemas AS S1 ON O1.[schema_id] = S1.[schema_id]
WHERE C1.is_disabled = 0 AND C1.is_not_trusted = 1
UNION ALL
SELECT
QUOTENAME(S1.[name]) AS [schema_name]
, QUOTENAME(O1.[name]) AS [table_name]
, QUOTENAME(C1.[name]) AS [constraint_name]
, N'CHECK_CONSTRAINT' AS [constraint_type]
, 'ALTER TABLE ' + QUOTENAME(S1.[name]) + '.' + QUOTENAME(O1.[name])
+ ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(C1.[name]) AS sql_stmt
FROM sys.check_constraints AS C1
JOIN sys.objects AS O1 ON C1.parent_object_id = O1.[object_id]
JOIN sys.schemas AS S1 ON O1.[schema_id] = S1.[schema_id]
WHERE C1.is_disabled = 0 AND C1.is_not_trusted = 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment