SQL Server: How to change collation of all columns of my select on the fly

Question

I need to query something from a table in my database but the collation of some columns are different from the one I need.

I found how to change the collation of one column on the fly like this:

SELECT 
  u.name  COLLATE Latin1_General_CI_AS AS 'User'(...)

But the problem is that are more than 150 columns that need a COLLATION change on the fly (don't ask me why because we can't change the collation of the entire table even though I think it would be the best option).

So the main question is: Is there a way to set the COLLATION to all columns of my query?

Answer 1

There's no way to change the collation of all text columns on the fly -- but manipulating the query to achieve the same result is doable. Martin's answer shows one way to do it, here's another using the metadata of the query. As a benefit, it's cursorless, and it avoids gratuitously introducing "new" columns if the collation is already what we want. It requires at least SQL Server 2012 to have sys.dm_exec_describe_first_result_set, and SQL Server 2017 to have STRING_AGG (on previous versions, the classic tricks for string concatenation can be used, like the FOR XML PATH trick).

DECLARE @query NVARCHAR(MAX) = N'SELECT * FROM MyTable';
DECLARE @tweakedQuery NVARCHAR(MAX);
DECLARE @tweakedCollation SYSNAME = N'Latin1_General_CI_AS';

SELECT @tweakedQuery = CONCAT(
    'SELECT ', 
    STRING_AGG(
        CONCAT(
            CONVERT(NVARCHAR(MAX), QUOTENAME([name])), 
            CASE 
                WHEN collation_name IS NOT NULL AND collation_name <> @tweakedCollation
                THEN ' COLLATE ' + @tweakedCollation + ' AS ' + QUOTENAME([name]) 
            END
        ), 
        ', ' + CHAR(13) + CHAR(10)
    ),
    ' FROM (' + CHAR(13) + CHAR(10),
    @query,
    CHAR(13) + CHAR(10) + ') _'
)
FROM sys.dm_exec_describe_first_result_set(@query, NULL, NULL);
PRINT @tweakedQuery;

Incorporating custom aliases would be simple, if they're generated systematically (based on the table or column name). Otherwise you'll have to patch things up manually.

For a one-off query (not something you necessarily need to do on the fly) it's still a much better idea to familiarize yourself with using regexes in your favorite editor. If your favorite editor has no regexes, get one that does.

Answer 2

I'm not sure that there's a way of forcing a default collation for all columns of the output in this way.

However, if your server permits you to use dynamic SQL, you could build a dynamic query and execute it to get the result your looking for.

An example of this working is shown below. Please change YOUR_TABLE_NAME to your actual table name in order for it to work:

DECLARE @col    VARCHAR(200)
DECLARE @sql    VARCHAR(MAX)
DECLARE @table  VARCHAR(200)
SET @table = 'YOUR_TABLE_NAME';

-- Use a cursor to read all varchar columns from table
DECLARE curColumns CURSOR FORWARD_ONLY STATIC READ_ONLY
  FOR
    SELECT  c.name
      FROM  sys.columns c
        INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
      WHERE c.object_id = OBJECT_ID(@table) AND
            t.name = 'varchar'

SET @sql = 'SELECT'

-- Go through column cursor
OPEN curColumns
FETCH NEXT FROM curColumns INTO @col
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- Add column to SQL with collation
    SET @sql = @sql + ' ' + @col + ' COLLATE Latin1_General_CI_AS,'

    FETCH NEXT FROM curColumns INTO @col
END
CLOSE curColumns
DEALLOCATE curColumns

-- Add the from clause
SET @sql = SUBSTRING(@sql, 0, LEN(@sql) - 1) + ' FROM ' + @table

-- Uncomment this line to see the generated SQL
--PRINT @sql
EXEC (@sql)

Note that there are several limitations to the above example:

1. You server must permit the running of dynamic SQL.
2. In this example, only VARCHAR columns are output (you can change this by changing the cursor query to include more types).
3. There is no way to specify column aliases using this method.