Raja Raja - 2 months ago 9
SQL Question

How do I get constraints on a SQL 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 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

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)