How do I get constraints on a SQL table column

Question

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 MS SQL server, it seems like I'll need to use MS SQL's API to get the info. I was hoping for a SQL query itself to get it.

Answer 1

Easiest and quickest way is to use:

sp_help 'TableName'

Answer 2

This query should show you all the constraints on a 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 with this:

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