I'm doing an audit of constraints in SQL Server.
How to find all constraints that are not enabled?
This, after using the next declaration:
alter table mytable nocheck constraint all
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Use the system views for this:
select * from sys.check_constraints where is_disabled = 1;
SELECT [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), [disabled_constraint] = c.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.check_constraints AS c ON t.object_id = c.parent_object_id WHERE c.is_disabled = 1;
Mike and Aaron are both on the right track - but the question asks for ALL constraints that are disabled - not just CHECK constraints (i.e., FKs can be disabled too).
Moreover, FKs can also be UNTRUSTED - which will yield the potential for not just bad/invalid DATA but can/will also lead to perf issues (as SQL Server can't/won't trust these FKs when creating execution plans) as per: https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/
As such, I tend to use the following:
WITH constraints AS ( SELECT QUOTENAME(SCHEMA_NAME(t.[schema_id])) + N'.' + QUOTENAME(t.[name]) [table] , c.[type_desc] [constraint_type], c.[name] [constraint_name] FROM sys.tables AS t INNER JOIN sys.check_constraints AS c ON t.[object_id] = c.parent_object_id WHERE c.is_disabled = 1 UNION SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME(OBJECT_NAME([parent_object_id])) [table], CASE WHEN is_disabled = 1 THEN 'FOREIGN_KEY (DISABLED)' ELSE 'FOREIGN_KEY (UNTRUSTED)' END [constraint_type], [name] [constraint_name] FROM sys.[foreign_keys] WHERE [is_disabled] = 1 OR [is_not_trusted] = 1 ) SELECT [table], [constraint_type], [constraint_name] FROM constraints ORDER BY [constraint_type], [table];