Query to change table collation in SQL Server 2008

collation, sql-server, sql-server-2008

Solution

Never a bad idea to consult the documentation. Guessing at the source data type and NULLability; you can fill in the table/column names:

ALTER TABLE dbo.TableName ALTER COLUMN ColumnName
  NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;

If you can't check the documentation, Management Studio will sometimes show you the smart way to do something (though it doesn't always choose to do things the best way). Go into the design screen, change the collation, and instead of clicking OK, click the Script button.

Problem

In my database, one table collation is different than all the other tables. I would like to change that table collation to be the same as all other tables. Now, I can change a table collation by using SSMS Design but I would like to use query to change collation. Currently, my one table collation is `Thai_CI_AS` and I want to change collation is `SQL_Latin1_General_CP1_CI_AS`. It's not possible to drop the table because it already contains data.

Original source