0

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.

user3748950
  • 19
  • 4
  • `default` is an option for `create table` – Stu Oct 18 '21 at 16:16
  • What error do you get? – gvee Oct 18 '21 at 16:17
  • You can't change the `DEFAULT` value of a column in an `ALTER TABLE` statement. See the [syntax](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15#syntax-for-disk-based-tables) section of the documentation. You have to `DROP` the `CONSTRAINT`s (if it already exists) and (re)`CREATE` them. I hope you named your default value constraints if they did already exist. – Thom A Oct 18 '21 at 16:20

1 Answers1

1

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];
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Thanks for the fast reply. I guess its easier to drop all the tables and generate a create table statement instead. Anyways this solved the problem :) – user3748950 Oct 18 '21 at 16:30