I have a table with roughly 3000 rows in it and I need to modify a column definition to allow nulls. I have a change script that will perform the change but I would like to be able to re-run the script so that the change will only occur if the column definition hasn't already been changed. How do I test a column definition to identify if the column is NULL or NOT NULL?

3 Answers 3


You can also look at sys.columns.is_nullable ...


Standard solution:

where TABLE_NAME ='table' and COLUMN_NAME ='column'
  • 1
    Is this the more correct approach? Is this cross platform or still SQL Server specific? I'm not about to change platforms but I would prefer to learn something that has broader appliction. Commented Sep 27, 2011 at 19:20
  • 2
    @David I typically caution against the INFORMATION_SCHEMA views. Yes they are more standard but if you're switching from SQL Server to Oracle do you think metadata queries are going to be your biggest hassle? Besides, Microsoft acknowledges that these views are not complete (they are not updated for new features, like the catalog views are) and they even occasionally suggest that they're incorrect. Background: web.archive.org/web/20121126175858/http://connect.microsoft.com/… Commented Sep 27, 2011 at 22:44
  • My point wasn't really about switching from SQL Server to Oracle, it was more about learning something that has application across multiple database engines rather than vendor-specific extensions, e.g. NULLIF versus COALESCE. I'm a .Net developer (not sure if that's a wise admission here) so I'm looking for the biggest bang for my buck. Commented Sep 27, 2011 at 23:35
  • @David The design of system tables can be changing between versions of SQL Server, but Information_schema does not.
    – msi77
    Commented Sep 29, 2011 at 9:16
  • 1
    @msi77 that's part of the problem though, isn't it? You want to use one method when you only care about very basic stuff that hasn't changed in years, and a different method if you care about features that weren't in SQL 2000? Personally, I'd rather code consistently... Commented Oct 3, 2011 at 21:30

Never mind - found the answer over here

SELECT COLUMNPROPERTY( OBJECT_ID('dbo.spt_values'),'number','AllowsNull')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.