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
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];
Here is the query which lists all constraints from a connected database.
select table_view, object_type, constraint_type, constraint_name, is_enforced, details from ( select schema_name(t.schema_id) + '.' + t.[name] as table_view, case when t.[type] = 'U' then 'Table' when t.[type] = 'V' then 'View' end as [object_type], case when c.[type] = 'PK' then 'Primary key' when c.[type] = 'UQ' then 'Unique constraint' when i.[type] = 1 then 'Unique clustered index' when i.type = 2 then 'Unique index' end as constraint_type, isnull(c.[name], i.[name]) as constraint_name, c.is_enforced as is_enforced, substring(column_names, 1, len(column_names)-1) as [details] from sys.objects t left outer join sys.indexes i on t.object_id = i.object_id left outer join sys.key_constraints c on i.object_id = c.parent_object_id and i.index_id = c.unique_index_id cross apply (select col.[name] + ', ' from sys.index_columns ic inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id where ic.object_id = t.object_id and ic.index_id = i.index_id order by col.column_id for xml path ('') ) D (column_names) where is_unique = 1 and t.is_ms_shipped <> 1 union all select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table, 'Table', 'Foreign key', fk.name as fk_constraint_name, case when fk.is_disabled = 0 then 1 else 0 end as is_enforced, schema_name(pk_tab.schema_id) + '.' + pk_tab.name from sys.foreign_keys fk inner join sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id inner join sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id inner join sys.foreign_key_columns fk_cols on fk_cols.constraint_object_id = fk.object_id union all select schema_name(t.schema_id) + '.' + t.[name], 'Table', 'Check constraint', con.[name] as constraint_name, case when con.is_disabled = 0 then 1 else 0 end as is_enforced, con.[definition] from sys.check_constraints con left outer join sys.objects t on con.parent_object_id = t.object_id left outer join sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id union all select schema_name(t.schema_id) + '.' + t.[name], 'Table', 'Default constraint', con.[name], '1' as is_enforced, col.[name] + ' = ' + con.[definition] from sys.default_constraints con left outer join sys.objects t on con.parent_object_id = t.object_id left outer join sys.all_columns col on con.parent_column_id = col.column_id and con.parent_object_id = col.object_id) a where is_enforced = 0 order by table_view, constraint_type, constraint_name