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. 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/… 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. 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
    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... 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 that you have read and understand our privacy policy and code of conduct.

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