Comment faire pour répertorier toutes les foreign keys avec "NOCHECK" dans SQL Server

Ce qui suit va returnner le nom des foreign keys dans la database en cours qui sont désactivées, c'est-à-dire WITH NOCHECK

Pour SQL Server 2005/2008:

 select * from sys.foreign_keys where is_disabled=1 

Il y avait une discussion dans la réponse à propos de la différence entre handicapés et non fiables. Ce qui suit explique la différence Voici un code pour clarifier la différence entre is_disabled et isnotrusted.

 -- drop table t1 -- drop table t2 create table t1(i int not null, fk int not null) create table t2(i int not null) -- create primary key on t2 alter table t2 add constraint pk_1 primary key (i) -- create foriegn key on t1 alter table t1 add constraint fk_1 foreign key (fk) references t2 (i) --insert some records insert t2 values(100) insert t2 values(200) insert t2 values(300) insert t2 values(400) insert t2 values(500) insert t1 values(1,100) insert t1 values(2,100) insert t1 values(3,500) insert t1 values(4,500) ---------------------------- -- 1. enabled and trusted select name,is_disabled,is_not_trusted from sys.foreign_keys GO -- 2. disable the constraint alter table t1 NOCHECK CONSTRAINT fk_1 select name,is_disabled,is_not_trusted from sys.foreign_keys GO -- 3. re-enable constraint, data isnt checked, so not trusted. -- this means the optimizer will still have to check the column alter table t1 CHECK CONSTRAINT fk_1 select name,is_disabled,is_not_trusted from sys.foreign_keys GO --4. drop the foreign key constraint & re-add -- it making sure its checked -- constraint is then enabled and trusted alter table t1 DROP CONSTRAINT fk_1 alter table t1 WITH CHECK add constraint fk_1 foreign key (fk) references t2 (i) select name,is_disabled,is_not_trusted from sys.foreign_keys GO --5. drop the foreign key constraint & add but dont check -- constraint is then enabled, but not trusted alter table t1 DROP CONSTRAINT fk_1 alter table t1 WITH NOCHECK add constraint fk_1 foreign key (fk) references t2 (i) select name,is_disabled,is_not_trusted from sys.foreign_keys GO 

is_disabled signifie que la contrainte est désactivée

isnottrusted signifie que SQL Server ne isnottrusted pas que la colonne a été vérifiée par rapport à la table de key étrangère.

Ainsi, on ne peut pas supposer que la réactivation de la contrainte de key étrangère sera optimisée. Pour s'assurer que l'optimiseur approuve la colonne, il est préférable de supprimer la contrainte de key étrangère et de la recréer avec l'option WITH CHECK (4.)

 SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1 

Le script suivant génère les instructions alter qui vont vérifier datatables existantes et empêcher toute nouvelle violation pour les foreign keys qui ne sont pas actuellement approuvées ('with nocheck').

Exécutez-le dans SQL Server Management Studio pour générer les scripts, puis copyz-les dans une window de requête pour les exécuter.

 select 'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' with check check constraint ' + fk.name +';' from sys.foreign_keys fk inner join sys.tables t on fk.parent_object_id = t.object_id inner join sys.schemas s on t.schema_id = s.schema_id where fk.is_not_trusted = 1 

WITH NOCHECK ne doit être appliqué que temporairement à FK, ou il devient inutile pour l'optimiseur, comme l'indique votre article lié. De BOL:

L'optimiseur de requête ne prend pas en count les contraintes définies avec NOCHECK. Ces contraintes sont ignorées jusqu'à ce qu'elles soient réactivées à l'aide de la table ALTER TABLE CHECK CONSTRAINT ALL.

Ceci identifiera toutes vos foreign keys: (travaillant sur le bit WITH NOCHECK …)

 SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], C.TABLE_NAME [PKTABLE_NAME], KCU.COLUMN_NAME [PKCOLUMN_NAME], C2.TABLE_CATALOG [FKTABLE_QUALIFIER], C2.TABLE_SCHEMA [FKTABLE_OWNER], C2.TABLE_NAME [FKTABLE_NAME], KCU2.COLUMN_NAME [FKCOLUMN_NAME], RC.UPDATE_RULE, RC.DELETE_RULE, C.CONSTRAINT_NAME [FK_NAME], C2.CONSTRAINT_NAME [PK_NAME], CAST(7 AS SMALLINT) [DEFERRABILITY] FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' 

Ref .

En passant, dans SQL Server 2000 et 2005, vous pouvez vérifier si des données enfreignent une contrainte en utilisant:

 DBCC CHECKCONSTRAINTS (table_name) 

Le code suivant récupère toutes les foreign keys marquées "WITH NOCHECK" et utilise ensuite une instruction ALTER pour les corriger:

 -- configure cursor on all FKs with "WITH NOCHECK" DECLARE UntrustedForeignKeysCursor CURSOR STATIC FOR SELECT f.name, t.name FROM sys.foreign_keys AS f LEFT JOIN sys.tables AS t ON f.parent_object_id = t.object_id Where Is_Not_Trusted = 1 OPEN UntrustedForeignKeysCursor -- loop through the untrusted FKs DECLARE @FKName varchar(100) DECLARE @TableName varchar(100) FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName WHILE @@FETCH_STATUS = 0 BEGIN -- Rebuild the FK constraint WITH CHECK EXEC ('ALTER TABLE ' + @TableName + ' WITH CHECK CHECK CONSTRAINT ' + @FKName) -- get next user FETCH NEXT FROM UntrustedForeignKeysCursor INTO @FKName, @TableName END -- cleanup CLOSE UntrustedForeignKeysCursor 

Je sais que c'est une vieille question avec quelques vieilles réponses qui ont de bonnes infos. Cependant, je voulais juste partager un script que j'ai utilisé pour résoudre ce problème dans un certain nombre de bases de données différentes pour nous:

 -- Foreign Keys SELECT 'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name + ';' AS AlterStatement from sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0; GO -- Constraints SELECT 'ALTER TABLE ' + o.name + ' WITH CHECK CHECK CONSTRAINT ' + i.name + ';' AS AlterStatement from sys.check_constraints i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0; GO 

Cela générera une collection d'instructions ALTER pour résoudre ce problème "NOCHECK" avec des keys et des contraintes étrangères. Ceci est basé sur quelques requêtes fournies par Brent Ozar mais ajusté par moi pour mes buts et facilité d'utilisation. Cela pourrait facilement être modifié avec UNION pour en faire une seule requête.

Pour info, je l'ai utilisé exclusivement dans les environnements Azure SQL Database. Je ne suis pas sûr s'il existe des limitations sur les anciennes versions de SQL Server, mais cela fonctionne très bien dans Azure.