List all table constraints (PK, UK, FK, Check and Default) in SQL Server

Query lists all table (and view) constraints - primary keys, unique key constraints and indexes, foreign keys and check, default constraints, and isDisabled.

Columns

• SchemaName
• Name - table or view schema and name
• object_type - object type:
  • Table
  • View
• constraint_type - type of constraint:
  • Primary key
  • Unique key
  • Foregin key
  • Check constraint
  • Default constraint
• constraint_name - name of constraint or index
• details - details of this constraint:
  • Primary key - PK column(s)
  • Unique key - UK column(s)
  • Foregin key - parent table name
  • Check constraint - check definition
  • Default constraint - column name and default value definition
• IsDisable – it is disabled or not.

SELECT SchemaName
     ,[Name]
     ,object_type
     ,constraint_type
     ,constraint_name
     ,details
     ,IsDisabled
FROM (
     -- Index
     SELECT SCHEMA_NAME(t.schema_id) AS SchemaName
         ,t.[name] AS [Name]
         ,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
         ,SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [details]
         ,i.is_disabled AS IsDisabled
     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
    
     -- Foreign key
     SELECT SCHEMA_NAME(fk_tab.schema_id)
         ,fk_tab.NAME AS foreign_table
         ,'Table'
         ,'Foreign key'
         ,fk.NAME AS fk_constraint_name
         ,SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME
         ,fk.is_disabled AS IsDisabled
     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
    
     -- Check constraint
     SELECT SCHEMA_NAME(t.schema_id)
         ,t.[name]
         ,'Table'
         ,'Check constraint'
         ,con.[name] AS constraint_name
         ,con.[definition]
         ,con.is_disabled AS IsDisabled
     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
    
     -- Default constraint
     SELECT SCHEMA_NAME(t.schema_id)
         ,t.[name]
         ,'Table'
         ,'Default constraint'
         ,con.[name]
         ,col.[name] + ' = ' + con.[definition]
         ,0 AS IsDisabled
     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
ORDER BY SchemaName
     ,[Name]
     ,constraint_type
     ,constraint_name;

Script to set nocheck on foreign key constaints

-- Script to set nocheck on foreign key constaints
DECLARE @SchemaName VARCHAR(250) = NULL; -- 'dbo';
DECLARE @TableName VARCHAR(250) = NULL;  -- 'Client';
DECLARE @IDKey INT;
DECLARE @SqlScript VARCHAR(max);

SET NOCOUNT ON;

DROP TABLE IF EXISTS #tb;

-- Foreign key
SELECT ROW_NUMBER() OVER(ORDER BY SCHEMA_NAME(fk_tab.schema_id), fk_tab.NAME) AS [IDKey],
        --SCHEMA_NAME(fk_tab.schema_id),
        --fk_tab.NAME AS foreign_table,
        --fk.NAME AS fk_constraint_name,
        --SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME,
        --fk.is_disabled AS IsDisabled,
        'ALTER TABLE [' + SCHEMA_NAME(fk_tab.schema_id) + '].[' + fk_tab.NAME + '] NOCHECK CONSTRAINT ' + fk.NAME + ';' AS SqlScript
INTO #tb
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
WHERE fk.is_disabled = 0
AND (@SchemaName IS NULL OR @SchemaName = SCHEMA_NAME(fk_tab.schema_id))
AND (@TableName IS NULL OR @TableName = fk_tab.NAME)
;

while ((SELECT COUNT(*) FROM #tb) > 0 )
BEGIN
     SELECT top 1 @IDKey=IDKey, @SqlScript = SqlScript FROM #tb ORDER BY IDKey;
     DELETE FROM #tb WHERE @IDKey=IDKey;
     PRINT @SqlScript;
     --EXEC(@SqlScript);
END;