List out all constraints on a table with there column names

Transact-SQL
1

Can anybody help me to know how I can get all the constraints on a table with there column name on which the constraints are applied?

2

There is a system proc available that lists constraints:

EXEC sys.sp_helpconstraint 'your_table_name_here', @nomsg = 'NOMSG'

2 Likes
3

Thanks a lot Scott for your help

4

if you want ALL Tables

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

SELECT
    [tab].[name] AS [Table]
    , [tab].[id] AS [Table Id]
    , [constr].[name] AS [Constraint Name]
    , [constr].[xtype] AS [Constraint Type]
    , CASE [constr].[xtype] WHEN 'PK' THEN 'Primary Key'
          WHEN 'UQ' THEN 'Unique'
          ELSE ''
      END AS [Constraint Name]
    , [i].[index_id] AS [Index ID]
    , [ic].[column_id] AS [Column ID]
    , [clmns].[name] AS [Column Name]
    , [clmns].[max_length] AS [Column Max Length]
    , [clmns].[precision] AS [Column Precision]
    , CASE WHEN [clmns].[is_nullable] = 0 THEN 'NO'
          ELSE 'YES'
      END AS [Column Nullable]
    , CASE WHEN [clmns].[is_identity] = 0 THEN 'NO'
          ELSE 'YES'
      END AS [Column IS IDENTITY]
FROM
    [sys].[sysobjects] AS [tab]
    INNER JOIN [sys].[sysobjects] AS [constr]
        ON (   [constr].[parent_obj] = [tab].[id]
               AND  [constr].[type] = 'K')
    INNER JOIN [sys].[indexes] AS [i]
        ON (   (   [i].[index_id] > 0
                   AND  [i].[is_hypothetical] = 0)
               AND  ([i].[object_id] = [tab].[id])
               AND  [i].[name] = [constr].[name])
    INNER JOIN [sys].[index_columns] AS [ic]
        ON (   [ic].[column_id] > 0
               AND  (   [ic].[key_ordinal] > 0
                        OR  [ic].[partition_ordinal] = 0
                        OR  [ic].[is_included_column] != 0))
           AND  (   [ic].[index_id] = CAST([i].[index_id] AS INT)
                    AND [ic].[object_id] = [i].[object_id])
    INNER JOIN [sys].[columns] AS [clmns]
        ON [clmns].[object_id] = [ic].[object_id]
           AND  [clmns].[column_id] = [ic].[column_id]
WHERE
    [tab].[xtype] = 'U'
ORDER BY
    [tab].[name];
1 Like