'How do I get constraints on a SQL Server table column

I have a column called MealType (VARCHAR) in my table with a CHECK constraint for {"Veg", "NonVeg", "Vegan"}

That'll take care of insertion.

I'd like to display these options for selection, but I couldn't figure out the SQL query to find out the constraints of a particular column in a table.

From a first glance at system tables in SQL Server, it seems like I'll need to use SQL Server's API to get the info. I was hoping for a SQL query itself to get it.

sqlsql-server


Solution 1:[1]

Easiest and quickest way is to use:

sp_help 'TableName'

Solution 2:[2]

SELECT obj_table.NAME      AS 'table', 
        columns.NAME        AS 'column',
        obj_Constraint.NAME AS 'constraint',
        obj_Constraint.type AS 'type'

    FROM   sys.objects obj_table 
        JOIN sys.objects obj_Constraint 
            ON obj_table.object_id = obj_Constraint.parent_object_id 
        JOIN sys.sysconstraints constraints 
             ON constraints.constid = obj_Constraint.object_id 
        JOIN sys.columns columns 
             ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
    WHERE obj_table.NAME='table_name'
    ORDER  BY 'table'

Solution 3:[3]

You can use 

sp_helpconstraint 'tableName', 'nomsg'

to get all the constraints for the table.

"sp_help" return far more information.

Solution 4:[4]

Thanks to orgtrigger for his example! I improved it to be able to remove unnecessary constraints (and then create their modified versions, if needed). Maybe this code will be useful for anybody.

  -- removing old constraints
DECLARE @ConstraintNames TABLE (Name VARCHAR(MAX), RowNum INT)
DECLARE @TableName VARCHAR(100) = 'HubSpot'

INSERT @ConstraintNames
  SELECT [constraint].name,
         ROW_NUMBER() OVER (ORDER BY [constraint].[name]) AS RowNum
  FROM sys.default_constraints [constraint]
  INNER JOIN sys.columns col
      ON [constraint].parent_object_id = col.object_id
  INNER JOIN sys.tables st
      ON [constraint].parent_object_id = st.object_id
  WHERE 
  st.name = @TableName 
  AND col.name IN ('ForceUpdateOnImport', 'ForceUpdateOnExport')
  AND col.column_id = [constraint].parent_column_id
  
SELECT * FROM @ConstraintNames

DECLARE @i INT = 1,
        @count INT,
        @constraintName VARCHAR(MAX),
        @sql VARCHAR(MAX)
SELECT @count = COUNT(1) FROM @ConstraintNames

WHILE @i <= @count 
  BEGIN 
    SELECT @constraintName = cn.Name FROM @ConstraintNames cn WHERE cn.RowNum = @i
    SET @sql = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @constraintName
      
    EXEC (@sql)
    SET @i = @i + 1
  END

Solution 5:[5]

Below is helpful for check and default constraints. I use it for implicit constraints to offer up guidance for what the name should be. If you remove everything after the where clause, it should be good for any check/default constraints.

SELECT /* obj_table.NAME      AS 'table', 
        columns.NAME        AS 'column',
        obj_Constraint.NAME AS 'constraint',
        obj_Constraint.type AS 'type',
        sss.name as 'schema',*/
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] DROP CONSTRAINT [' + obj_Constraint.NAME + '];' As 'Wrong_Implicit_Constraint',
        'ALTER TABLE [' + ltrim(rtrim(sss.name))+'].['+ltrim(rtrim(obj_table.name)) + '] ADD CONSTRAINT [' + CASE obj_Constraint.type 
        WHEN 'D' THEN 'DF' WHEN 'F' THEN 'FK' 
        WHEN 'U' THEN 'UX' WHEN 'PK' THEN 'PK' WHEN 'N' THEN 'NN' WHEN 'C' THEN 'CK' 
        END + '_' + ltrim(rtrim(obj_table.name)) + '_' + columns.NAME + ']' +
        CASE obj_Constraint.type WHEN 'D' THEN ' DEFAULT (' + dc.definition +') FOR [' + columns.NAME + ']'
        WHEN 'C' THEN ' CHECK (' + cc.definition +')'
        ELSE '' END +
        ';' As 'Right_Explicit_Constraint'
    FROM   sys.objects obj_table 
        JOIN sys.objects obj_Constraint ON obj_table.object_id = obj_Constraint.parent_object_id 
        JOIN sys.sysconstraints constraints ON constraints.constid = obj_Constraint.object_id 
        JOIN sys.columns columns ON columns.object_id = obj_table.object_id 
            AND columns.column_id = constraints.colid 
        left join sys.schemas sss on obj_Constraint.schema_id=sss.schema_id 
        left join sys.default_constraints dc on dc.object_id = obj_Constraint.object_id
        left join sys.check_constraints cc on cc.object_id = obj_Constraint.object_id
    WHERE obj_Constraint.type_desc LIKE '%CONSTRAINT'
    AND RIGHT(obj_Constraint.name,10) LIKE '[_][_]________' --match double underscore + 8 chars of anything
    AND RIGHT(obj_Constraint.name,8) LIKE '%[A-Z]%'          --Ensure alpha in last 8
    AND RIGHT(obj_Constraint.name,8) LIKE '%[0-9]%'                 --Ensure numeric in last 8
    AND RIGHT(obj_Constraint.name,8) not LIKE '%[^0-9A-Z]%' --Ensure no special chars

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zonus
Solution 2 Ravinder Reddy Kottabad
Solution 3 Mitio
Solution 4 Gennady Maltsev
Solution 5 Nicholas McQuillen

Related Questions