Comment lister toutes les clés étrangères avec "avec NOCHECK" dans SQL Server
est-ce que quelqu'un connaît une requête pour lister toutes les clés étrangères dans une base de données avec la description" avec NOCHECK " appliquée à elle? (les enlever augmentera la performance et la stabilité).
6 réponses
ce qui suit retournera le nom des clés étrangères dans la base de données courante qui sont désactivées i.e. avec NOCHECK
pour SQL Server 2005/2008:
select * from sys.foreign_keys where is_disabled=1
Il y a eu une discussion dans la réponse au sujet de la différence entre les personnes handicapées et les personnes à qui on ne fait pas confiance. Ce qui suit explique la différence Voici un peu de code 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 croit pas que la colonne a été cochée par rapport à la table des clés étrangères.
on ne peut donc pas supposer que la réactivation de la contrainte de la clé étrangère sera optimisée. Pour s'assurer que l'optimiseur fait confiance à la colonne, il est préférable de laisser tomber la contrainte de clé é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érera les déclarations alter qui vérifieront les données existantes et préviendront toute nouvelle Violation pour les clés étrangères qui ne sont pas actuellement fiables ('with nocheck').
L'exécute dans SQL Server Management Studio pour générer les scripts et les copier dans une fenêtre 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
avec NOCHECK ne devrait jamais être appliqué aux FK que temporairement, ou ils deviennent inutiles à l'optimiseur comme votre article lié le souligne. De BOL:
L'optimiseur de requête ne considère pas contraintes définies avec NOCHECK. Ces contraintes sont ignorées jusqu'à ce qu'ils soient réactivés en utilisant Contrainte de vérification de la table D'ALTER TOUT.
ceci identifiera toutes vos clés étrangères: Avec NOCHECK bit...)
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 .
comme mise à part, dans les deux SQL Server 2000 et 2005, vous pouvez vérifier si des données violent une contrainte en utilisant:
DBCC CHECKCONSTRAINTS (table_name)
le code suivant récupère toutes les clés étrangères marquées 'avec 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 de vieilles réponses qui ont de bonnes informations. Cependant, je voulais juste partager un script que j'ai utilisé pour résoudre ce problème dans plusieurs 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 un ensemble D'instructions ALTER pour corriger ce problème" NOCHECK " avec des clés et des contraintes étrangères. Ceci est basé sur quelques requêtes fournies par Brent Ozar mais tweaked par moi pour mon fins et la facilité d'utilisation. Cela pourrait facilement être modifié avec un UNION pour en faire une seule requête.
pour info, je l'ai utilisé exclusivement dans des environnements de base de données SQL Azure. Je ne suis pas sûr qu'il y ait des limitations sur les anciennes versions de SQL Server mais cela fonctionne très bien dans Azure.