Alter column without changing collation

Question

We're experiencing difficulties in managing different customer databases:

We need to alter a primary key column from VARCHAR(20) to VARCHAR(40) without altering the collation. The reason behind this is that we use a case-insensitive default collation and the column we want to change is case-sensitive and needs to stay that way.

Usually this would be no problem, we could tell it to keep the case-sensitive collation:

ALTER TABLE Commiss 
   ALTER COLUMN CommissName VARCHAR(40)
      COLLATE Latin1_General_CS_AS NOT NULL;

However, some of our customers have different default collations (French, Chinese, ...). Until now, we could always use the same update-scripts for all customers. Now we need to change all ALTER COLUMN statements to the correct collation before updating the schema.

So my question is: Is there any way to tell SQL Server to alter the datatype and keep the existing collation for this column. Alternatively it would also be ok for us to do something like this:

ALTER TABLE Commiss 
   ALTER COLUMN CommissName VARCHAR(40)
      COLLATE CS_AS NOT NULL;

So effectively keeping the language as it is and only "changing" the case-sensitivity.

Thanks!

Answer 1

Unfortunately, not in the ALTER TABLE directly

You can generate these statements though from sys.columns:

DECLARE @sql nvarchar(1000);

SELECT
    @sql = 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id)) +
   ' ALTER COLUMN ' + QUOTENAME(C.name) + ' VARCHAR(40)
      COLLATE ' + c.collation_name + ' NOT NULL;'
FROM
    sys.columns C
WHERE
    c.object_id = OBJECT_ID('dataenum.host')
    AND
    C.name = 'hostname';

SELECT @sql;

--EXEC (@sql);