How to get restrictions on a SQL table column

Question

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

This will take care of the insert.

I would like to display these options for selection, but I could not figure out the SQL query to find out the limitations of a particular column in the table.

At a glance at the system tables on the MS SQL server, it looks like I will need to use the MS SQL API to get the information. I was hoping for the SQL query itself to get it.

Answer 1

This query should show you all the restrictions in the table:

 select chk.definition from sys.check_constraints chk inner join sys.columns col on chk.parent_object_id = col.object_id inner join sys.tables st on chk.parent_object_id = st.object_id where st.name = 'Tablename' and col.column_id = chk.parent_column_id 

can replace the select statement as follows:

 select substring(chk.Definition,2,3),substring(chk.Definition,9,6),substring(chk.Definition,20,5) 

Answer 2

The easiest and fastest way is to use:

 sp_help 'TableName' 

Answer 3

You can use

 sp_helpconstraint 'tableName', 'nomsg' 

to get all the restrictions for the table.

"sp_help" returns much more information.

Answer 4

 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'