COLLATION - consultando e alterando

Consultando:

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
GO


SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'NomeDaTabela')
AND name = 'NomeDaColuna'

Alterando:

ALTER TABLE TestTable ALTER COLUMN FirstCol VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

GO 

 fonte: SQL SERVER – Change Collation of Database Column – T-SQL Script