Today’s script comes about because of a recent discussion about check constraints. The discussion was about the use of WITH NOCHECK and the fact that I don’t like it being used. My view is that if you are adding a check constraint then you are doing so for a reason so existing data should be validated. I know it is quicker to add the constraint WITH NOCHECK but you then have a half-arsed solution.
WITH NOCHECK can lead to performance and consistancy problems first off the constraint can’t be used by the Query Optimiser to know what data might exist in the column(s). Second if there is data that invalidates the constraint then queries updating the column to the same value (UPDATE tablename SET columname = columname) will fail. The errors you see are also often poor making finding the cause of the problem harder. The decision is ultimately yours but I would avoid using WITH NOCHECK. The script returns all untrusted check constraints for a given database.
/*
-----------------------------------------------------------------
Untrusted Check Constraints
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
-- Change db context
USE databasenamehere;
GO
-- Get untrusted check constraints
SELECT
name,
[object_id],
principal_id,
[schema_id],
parent_object_id,
[type],
type_desc,
create_date,
modify_date,
is_ms_shipped,
is_published,
is_schema_published,
is_disabled,
is_not_for_replication,
is_not_trusted,
parent_column_id,
[definition],
uses_database_collation,
is_system_named
FROM
sys.check_constraints
WHERE
is_not_trusted = 1;
GO
Enjoy!
Chris
