6

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?

8

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

7

Standard solution:

select IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS 
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. – David Clarke Sep 27 '11 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 (though they're not always right about that). Background: connect.microsoft.com/SQLServer/feedback/details/686118/… – Aaron Bertrand Sep 27 '11 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. – David Clarke Sep 27 '11 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 '11 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... – Aaron Bertrand Oct 3 '11 at 21:30
3

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, privacy policy and cookie policy

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