I have been assigned to try and make collations uniform across databases. Currently, it's a mess, different collations everywhere.

This is mostly solved by using collate database default in queries and stored procedures.

Bit of a broad question, but what would be a good approach for making it all uniform. Has anyone got tips / or a T-SQL script?

closed as too broad by Max Vernon, Tom V, sp_BlitzErik, MDCCL, Colin 't Hart Sep 7 at 9:41

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • The idea is that if all collations across databases (and columns) match, then comparisons will run fine without the syntax collate database default, which we currently use everywhere. – www.StudioSQL.nl Sep 6 at 13:00
  • 2
    For all the gotchas with changing the collation, honestly, I would set up a new instance with shell databases and empty tables where everything is the right collation, migrate the data, change all the connection strings, and blow the old mess away. But I tend to err toward nuke and pave whenever anything goes wrong with an instance, because it's just almost always easier to start over than to fix. – Aaron Bertrand Sep 6 at 13:46
  • @AaronBertrand thanks for the tip. I am what Brent Ozar calls a accidental dba, so it's kind of 'outta my leage'. Maybe starting over is not such a bad idea. – www.StudioSQL.nl Sep 6 at 14:00
up vote 3 down vote accepted

what would be a good approach for making [collations for columns] all uniform?

Well, "good" is highly subjective. The first thing to consider is the scope of what you believe should be changed. How many databases are affected? How many columns in each database would be changed? And how many total string columns are there? Does the collation need to change at the database level for any of the databases? Does the collation need to change at the server level? Does the issue you are trying to fix revolve around column-to-column comparisons / joins, or is it column-to-variable / literal comparisons, or both?

If most columns have the same collation but a small percentage have different collations, was that intentional such that changing the collation would change the behavior and/or performance of some queries? Meaning, it is one thing to want to change SQL_Latin1_General_CP1_CI_AS to Latin1_General_100_CI_AS as that is at least the same code page, culture (i.e. linguistic rules), and sensitivities (accent-sensitive, case + kana type + width insensitive). But it is quite another to change Latin1_General_100_CI_AS to Latin1_General_100_BIN2 or Latin1_General_100_CS_AS, or French_100_CI_AS, or even to SQL_Latin1_General_CP1_CI_AS.

If it is a small number / percentage of columns to change, AND assuming that the "incorrect" collations were not intentional and purposeful, then you should probably change them one at a time via ALTER TABLE ... ALTER COLUMN. You might need to drop and recreate dependencies if any exist, such as FKs, indexes, functions referencing those columns, etc.

If it is a lot of columns, and even more so if there is a need to change database and/or instance collations, then you could look into the undocumented approach that I documented here:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are pros and cons to that "undocumented" approach, but it does greatly reduce the workload of making the changes as it side-steps the dependency issues. There are a few gotchas that will affect some folks and not others, but only one that can't be fixed easily enough: changing code pages on a CHAR / VARCHAR / TEXT column1. However, I am guessing that it is fairly rare that someone would be changing collations that also change the code page (but if that is the case, then you can't use this undocumented approach without first making sure that you aren't using any of the characters that would be changing). Still, it changes ALL2 collations, which is generally desired in these cases, but does require that you might need to change a few back manually if there are some non-standard collations being used that are there for a good reason.


1 Code page change happens when the code page used by the old collation is different than the code page used by the new collation. If the old and new collations are in the same culture / locale (i.e. Latin1_General, French, Japanese, etc), then there shouldn't be any chance of that happening. And even going from Latin1_General to French isn't a problem as they both use Code Page 1252. But, going from Latin1_General to Japanese is a problem as they use different code pages. You can determine the code page using the COLLATIONPROPERTY built-in function. For example:

SELECT COLLATIONPROPERTY(N'Latin1_General_100_CI_AS', 'CodePage') AS [Latin1_General],
       COLLATIONPROPERTY(N'French_100_CI_AS', 'CodePage') AS [French],
       COLLATIONPROPERTY(N'Japanese_CI_AS', 'CodePage') AS [Japanese];

returns:

Latin1_General    French    Japanese
1252              1252      932

2 Almost all: collations of User-Defined Table Types are skipped.

You could loop through the tables with a cursor like this:

declare @table varchar(255),
@column varchar(255),
@data_type varchar(255),
@length varchar(10),
@nullable varchar(55),
@sql varchar(max)

declare c cursor local for

SELECT 
    st.name,
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    case when c.is_nullable = 0 then 'null' else 'not null' end

FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
inner join sys.tables st on c.object_id = st.object_id
    where t.name like '%char%'
    or t.name like '%text%'

open c
fetch from c into @table, @column, @data_type, @length, @nullable

while @@FETCH_STATUS = 0

begin

set @sql = 'alter table ' + @table + ' alter column' + @column + ' ' + @data_type + '(' + @length + ') ' + @nullable + ' Latin1_General_100_CI_AS'
print @sql

--exec sp_executesql

fetch next from c into @table, @column, @data_type, @length, @nullable
end

close c
deallocate c
  • 2
    Much closer. I think you need to also allow for TEXT / NTEXT columns, and might want to filter out columns that are already the correct collation so as to not do unnecessary extra work. Also, not sure why those two LEFT OUTER JOINs exist as they are not used. Finally, while this might work in many / most situations, it is best to use NVARCHAR / sysname when dealing with object names since they are all of those two types internally (sysname being an alias for NVARCHAR(128)). Not sure if you will get an error when selecting the length (which is a number) into a string variable. – Solomon Rutzky Sep 6 at 17:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.