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?
asked Sep 26, 2011 at 21:45
Add a comment
|
3 Answers
You can also look at sys.columns.is_nullable ...
answered Sep 26, 2011 at 22:35
Standard solution:
select IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME ='table' and COLUMN_NAME ='column'
-
1Is 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.– msi77Commented 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')
answered Sep 26, 2011 at 21:49