Change Collation of a SQL Server Table Column

To see how to Find Collation of a SQL Server Database and Table Column, read related article:
Find Collation of a SQL Server Database and Table Column

Change Collation of a SQL Server Table Column using T-SQL:

First find current column collation:

USE AdventureWorks
GO
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test'
AND COLUMN_NAME = 'Text'

table_column_collation_tsq_2

Then Change Collation of a SQL Server Table Column:

USE AdventureWorks
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO

table_column_collation_tsq_2_change

We can see that the column collation has changed:

USE AdventureWorks
GO
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test'
AND COLUMN_NAME = 'Text'

table_column_collation_tsq_2_changed

Change Collation of a SQL Server Table Column using SQL Server Management Studio (SSMS):

Find Collation of a SQL Server Table Column using SQL Server Management Studio (SSMS):

Right Click on a table in Object Explorer -> Click ‘Design’:

table_column_design_ssms_2

Click on a column and see it’s collation, and click on a "..." button to change it:

table_column_collation_ssms_view

Choose new column collation and click 'OK' to confirm:

table_column_collation_ssms_change

Save the new table design.

If the post helped you, please share it:
Pin It

1 comment to Change Collation of a SQL Server Table Column

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">