How to check if a column has not null constraint?

Question

I am using SQL Server 2008 R2.

I have a table in which I have a column that have a not null constraint.

Now, what if I want to check if column has not null constraint defined or not for specific column?

Is there any query to find out it?

Thanks in advance..

Answer 1

Something like

SELECT o.name AS tab, c.name AS col, c.is_nullable 
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHERE o.name like '%yourtable%' and type = 'U'

See sys.columns and sys.objects

Answer 2

There's some catalog views you can use:

// information about check constraints
select * from sys.check_constraints 

// information about specific columns
select name, is_nullable from sys.columns

// information about tables
select * from sys.tables

The sys.columns is_nullablefield holds information about nullability.

Answer 3

there is a table sys.all_columns and a column in this table called is_nullable

http://technet.microsoft.com/en-us/library/ms177522(v=sql.105).aspx

select s.name, c.name, c.is_nullable from sys.tables s, sys.all_columns c
where s.object_id = c.object_id
and s.type = 'U' -- USER_TABLE
and  c.is_nullable = 1

Answer 4

It is a simple command that will list - Field, Type, Null, Key, Default

SHOW FIELDS FROM Your_Table_Name;