Last active
November 16, 2021 09:39
Star
You must be signed in to star a gist
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/ )
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
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