CODEWITHSUNDEEP
c#sql-server
ScottG
ScottG

Reputation: 11111

How to check if NOT NULL constraint exists

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?

Upvotes: 2

Views: 6452

Answers (5)

Dave Markle
Dave Markle

Reputation: 97671

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

Upvotes: 14

Rick Kierner
Rick Kierner

Reputation: 714

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

Upvotes: 1

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

you can with the use of COLUMNPROPERTY:

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062590

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

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

Where @tableId is OBJECT_ID('TableName')

Upvotes: 1

mson
mson

Reputation: 7824

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#.

Upvotes: 0

Related Questions