Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

3 Answers

up vote 4 down vote

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

share|improve this answer
up vote 3 down vote

Never mind - found the answer over here

SELECT COLUMNPROPERTY( OBJECT_ID('dbo.spt_values'),'number','AllowsNull')
share|improve this answer
up vote 3 down vote

Standard solution:

select IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME ='table' and COLUMN_NAME ='column'
share|improve this answer
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
@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
@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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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