How to check if NOT NULL constraint exists

Question

I am modifying a SQL table through C# code and I need to drop a NOT NULL constraint if it exists. How do I check to see if it exists first?

Answer 1

I'm not sure about your business rules, so I could be wrong, but what you are proposing sounds like a bad idea.

If the NOT NULL constraint shouldn't exist, then remove it from the DB. If it should exist, program around it.

If you actually do need to implement DDL to change tables, I'd write a stored procedure to do and call the stored procedure from C#.

Answer 2

select is_nullable 
from   sys.columns
where  object_id = OBJECT_ID('tablename')  
and    name = 'columnname';

Answer 3

Well, you could check syscolumns.isnullable flag? Or more recently:

COLUMNPROPERTY(@tableId, 'ColumnName', 'AllowsNull')

Where @tableId is OBJECT_ID('TableName')

Answer 4

you can with the use of COLUMNPROPERTY:

SELECT COLUMNPROPERTY( OBJECT_ID('schemaName.TableName'), 'ColumnName', 'AllowsNull')

Answer 5

execute this SQL Statement:

select * from information_schema.columns c
inner join information_schema.tables t on c.table_catalog = t.table_catalog and t.table_schema = c.table_schema and t.table_name = c.table_name
where c.table_name = 'Categories' and c.Is_nullable = 'NO'

and then execute the ALTER statement that removes the "not null" constraint