Search code examples
sqlsql-serveralter

Alter default value of column in mssql

I need to alter the collation of quite a lot tables including all the columns of these tables. I already wrote a sql statement which generates these alter commands for me. So for example I get:

ALTER TABLE [TABLE] ALTER COLUMN [COLUMN] nchar(1  ) COLLATE Latin1_General_CI_AS not null default 'y'

However I get an error, regarding the default at the end. How can I incorporate the default constraint in the statement above?

Thanks in advance.

Solution

  • Alter table does not have an option to supply a default constraint so you have to issue two commands; your first to alter the column, then you can create its default constraint:

    alter table [Table] add constraint DF_ColumnName default 'y' for [column];