Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active November 16, 2021 09:39
  • Star 0 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save EitanBlumin/d98727de0bdb157fdf8f53525c099b04 to your computer and use it in GitHub Desktop.
Find and check untrusted Check Constraints in all active databases (more info: https://eitanblumin.com/2018/11/06/find-and-fix-untrusted-foreign-keys-in-all-databases/ )
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, FullTableName AS QUOTENAME(SchemaName) + N'.' + QUOTENAME(TableName), UntrustedObject SYSNAME);
DECLARE @CMD NVARCHAR(MAX)
SET @CMD = N'SELECT DB_NAME(), OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id), [name]
FROM sys.check_constraints
WHERE is_not_trusted = 1 AND is_not_for_replication = 0 AND is_disabled = 0;'
IF CONVERT(varchar(300),SERVERPROPERTY('Edition')) = 'SQL Azure'
BEGIN
SET @CMD = REPLACE(@CMD, '?', DB_NAME())
INSERT INTO #tmp(DBName, SchemaName, TableName, UntrustedObject)
EXEC(@CMD)
END
ELSE
BEGIN
SET @CMD = N'IF EXISTS (SELECT * FROM sys.databases WHERE state_desc = ''ONLINE'' AND name = ''?'' AND DATABASEPROPERTYEX(''?'', ''Updateability'') = ''READ_WRITE'')
BEGIN
USE [?];
' + @CMD + N'
END'
INSERT INTO #tmp(DBName, SchemaName, TableName, UntrustedObject)
EXEC sp_MSforeachdb @CMD
END
SELECT
*
, CommandToRemediate = N'USE ' + QUOTENAME(DBName) + N'; ALTER TABLE ' + FullTableName + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(UntrustedObject) + N';'
FROM #tmp
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment