Query to change table collation in SQL Server 2008
Solution 1
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.
Solution 2
This is a valid solution that I applied and tested resetting collation on one of jira database tables.
-- CHANGED COLUMN COLLATION PROPERLY
ALTER TABLE [schemaName].[TableName]
ALTER COLUMN [columnName] [varchar](255)
COLLATE SQL_Latin1_General_CP437_CI_AI NOT NULL;
--Check string column collation for specified table
--Lists all string columns with respective collation
SELECT c.name,
c.collation_name
FROM SYS.COLUMNS c
JOIN SYS.TABLES t ON t.object_id = c.object_id
WHERE t.name = 'TableName'
and c.collation_name is not null
dragullar
Updated on June 26, 2022Comments
-
dragullar almost 2 years
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 isSQL_Latin1_General_CP1_CI_AS
.It's not possible to drop the table because it already contains data.