List All Table Constraints (PK, UK, FK, Check and Default) in SQL Server

 1
 2SELECT SchemaName
 3     ,[Name]
 4     ,object_type
 5     ,constraint_type
 6     ,constraint_name
 7     ,details
 8     ,IsDisabled
 9FROM (
10     -- Index
11     SELECT SCHEMA_NAME(t.schema_id) AS SchemaName
12         ,t.[name] AS [Name]
13         ,CASE
14             WHEN t.[type] = 'U'
15                 THEN 'Table'
16             WHEN t.[type] = 'V'
17                 THEN 'View'
18             END AS [object_type]
19         ,CASE
20             WHEN c.[type] = 'PK'
21                 THEN 'Primary key'
22             WHEN c.[type] = 'UQ'
23                 THEN 'Unique constraint'
24             WHEN i.[type] = 1
25                 THEN 'Unique clustered index'
26             WHEN i.type = 2
27                 THEN 'Unique index'
28             END AS constraint_type
29         ,ISNULL(c.[name], i.[name]) AS constraint_name
30         ,SUBSTRING(column_names, 1, LEN(column_names) - 1) AS [details]
31         ,i.is_disabled AS IsDisabled
32     FROM sys.objects t
33     LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id
34     LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id
35         AND i.index_id = c.unique_index_id
36     CROSS APPLY (
37         SELECT col.[name] + ', '
38         FROM sys.index_columns ic
39         INNER JOIN sys.columns col ON ic.object_id = col.object_id
40             AND ic.column_id = col.column_id
41         WHERE ic.object_id = t.object_id
42             AND ic.index_id = i.index_id
43         ORDER BY col.column_id
44         FOR XML PATH('')
45         ) D(column_names)
46     WHERE is_unique = 1
47         AND t.is_ms_shipped <> 1
48
49     UNION ALL
50
51     -- Foreign key
52     SELECT SCHEMA_NAME(fk_tab.schema_id)
53         ,fk_tab.NAME AS foreign_table
54         ,'Table'
55         ,'Foreign key'
56         ,fk.NAME AS fk_constraint_name
57         ,SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME
58         ,fk.is_disabled AS IsDisabled
59     FROM sys.foreign_keys fk
60     INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
61     INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
62     INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
63
64     UNION ALL
65
66     -- Check constraint
67     SELECT SCHEMA_NAME(t.schema_id)
68         ,t.[name]
69         ,'Table'
70         ,'Check constraint'
71         ,con.[name] AS constraint_name
72         ,con.[definition]
73         ,con.is_disabled AS IsDisabled
74     FROM sys.check_constraints con
75     LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
76     LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
77         AND con.parent_object_id = col.object_id
78
79     UNION ALL
80
81     -- Default constraint
82     SELECT SCHEMA_NAME(t.schema_id)
83         ,t.[name]
84         ,'Table'
85         ,'Default constraint'
86         ,con.[name]
87         ,col.[name] + ' = ' + con.[definition]
88         ,0 AS IsDisabled
89     FROM sys.default_constraints con
90     LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id
91     LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id
92         AND con.parent_object_id = col.object_id
93     ) a
94ORDER BY SchemaName
95     ,[Name]
96     ,constraint_type
97     ,constraint_name;

 1
 2-- Script to set nocheck on foreign key constaints
 3DECLARE @SchemaName VARCHAR(250) = NULL; -- 'dbo';
 4DECLARE @TableName VARCHAR(250) = NULL;  -- 'Client';
 5DECLARE @IDKey INT;
 6DECLARE @SqlScript VARCHAR(max);
 7
 8SET NOCOUNT ON;
 9
10DROP TABLE IF EXISTS #tb;
11
12-- Foreign key
13SELECT ROW_NUMBER() OVER(ORDER BY SCHEMA_NAME(fk_tab.schema_id), fk_tab.NAME) AS [IDKey],
14        --SCHEMA_NAME(fk_tab.schema_id),
15        --fk_tab.NAME AS foreign_table,
16        --fk.NAME AS fk_constraint_name,
17        --SCHEMA_NAME(pk_tab.schema_id) + '.' + pk_tab.NAME,
18        --fk.is_disabled AS IsDisabled,
19        'ALTER TABLE [' + SCHEMA_NAME(fk_tab.schema_id) + '].[' + fk_tab.NAME + '] NOCHECK CONSTRAINT ' + fk.NAME + ';' AS SqlScript
20INTO #tb
21FROM sys.foreign_keys fk
22      INNER JOIN sys.tables fk_tab ON fk_tab.object_id = fk.parent_object_id
23      INNER JOIN sys.tables pk_tab ON pk_tab.object_id = fk.referenced_object_id
24      INNER JOIN sys.foreign_key_columns fk_cols ON fk_cols.constraint_object_id = fk.object_id
25WHERE fk.is_disabled = 0
26AND (@SchemaName IS NULL OR @SchemaName = SCHEMA_NAME(fk_tab.schema_id))
27AND (@TableName IS NULL OR @TableName = fk_tab.NAME)
28;
29
30while ((SELECT COUNT(*) FROM #tb) > 0 )
31BEGIN
32     SELECT top 1 @IDKey=IDKey, @SqlScript = SqlScript FROM #tb ORDER BY IDKey;
33     DELETE FROM #tb WHERE @IDKey=IDKey;
34     PRINT @SqlScript;
35     --EXEC(@SqlScript);
36END;