I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts). Using SMO and C# (I am a sw engineer), I managed to generate really nice scripts like SQL Server Management studio would. It all works very nicely except for one particular issue:

for a a handful of tables, when I call ALTER TABLE [myTable] ALTER COLUMN [columnA] INT it decides to also change the column from NOT NULL to NULL. That of course is a huge issue since I need to regenerate primary keys for most of those tables on those particular columns.

Obviously, I have plenty of options using SMO to find out which columns are primary keys and force them to be NOT NULL after or while I am updating the data type, but I am really curious as to what can be causing this.

  • You can find this out if columns are members of the primary key via T/SQL as well. SMO isn't required.
    – mrdenny
    Feb 8, 2011 at 23:44

1 Answer 1


It depends on your setting of SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON (read these for other effects too from SET ANSI_DEFAULTS).

Personally, I'd have specified this constraint explicitly if I wanted null or non null columns rather than relying on environment settings.


You can issue this command again and if a column is already at the desired nullability the command is ignored.

However, you also said that you only need to change it where it's part of a primary key. That should have no bearing on whether a column can be nullable or not from the modelling/implementation phase


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.