MSSQL: collate, collation и information_schema

Задача:

Подготовить запрос, у которого в результирующих строках будут команды, которые меняют COLLATION столбцов в базе, с 'Cyrillic_General_CS_AS' на 'Cyrillic_General_CI_AS'.

Предусловия:
1. Понять что такое collation
2. Понять, как изменить collation столбцов в базе

Теория:
1. Collation
CHARACTER SET - это некий набор символов (кодировка). Разные CHARACTER SET включают в себя различные наборы символов. Различные CHARACTER SET могут включать примерно одинаковые наборы символов но в различном порядке (см. например koi8-r и cp1251). MySQL необходимо знать какой CHARACTER SET будет использован для данных в таблице, чтобы корректно проводтиь сортировку и индексацию данных.

COLLATION описывает способ, которым следует упорядочивать и сравнивать данные в БД. Для одного и того же CHARACTER SET существует как правило несколько COLLATION - например: cp1251_general_ci - default collation, нечуствительный к регистру. cp1251_cs - то же самое, но с учетом регистра.
Каждая схема устанавливает:
-порядок сортировки для данных с кодировкой Unicode;
-порядок сортировки для данных с кодировкой не-Unicode;
-кодовую страницу для данных с кодировкой не-Unicode.

На сервере реализованы две группы схем – Windows collations и SQL collations. Первая группа схем сопоставления реализована на сервере для поддержки региональных настроек Windows. Рекомендуется работать именно с этой группой. Вторая группа, SQL collations, используется для совместимости с предыдущими версиями сервера MS SQL.

Уровни  использования схемы сопоставления:

1. Уровень сервера. Схема сопоставления выбирается при установке сервера. Выбранная схема будет использована по умолчанию для всех системных баз и пользовательских баз данных, а также всех объектов каждой базы. При необходимости изменить схему на уровне сервера используется утилита Rebuild Master.
2. Уровень базы данных. Схему сопоставления можно указать при создании базы. Все объекты базы будут использовать эту схему по умолчанию.Также выбранная схема будет использоваться для символьных переменных и параметров. Изменить схему сопоставления базы данных можно при помощи команды ALTER DATABASE.
3.Уровень поля таблицы. При создании таблицы можно указать собственную схему сопоставления.
На уровне объектов базы (таблиц) схема не указывается.

Полный список схем сопоставления с расшифровкой:

select * from ::fn_helpcollations()

Проблемы с collation
Проблемы  появляются, когда присоединяется или устанавливается база с другой схемой сопоставления.
Ошибка «Cannot resolve collation conflict» будет появляться:
-при соединениях JOIN или UNION с таблицами из базы с другой схемой сопоставления.
-при работе с временными таблицами в контексте рабочей базы данных. Временные таблицы создаются в базе TempDB, где, как было уже отмечено, используется серверная схема сопоставления, и символьные данные в этом случае корректно сравнить не удается.

Решение: привести данные к одной схеме collation.
1. Если в запросах к БД  идет работа с временными таблицами, либо переменными табличного типа, то при их создании надо явно указывать нужную схему collation для каждого символьного поля.
2. Выполнить соединение между полями с различными схемами напрямую нельзя. Соответственно, нельзя сделать JOIN или UNION для таблиц с различными схемами collation из одной или разных баз, объединяемые поля также необходимо привести к одной схеме при помощи преобразования схемы сопоставлений.

Ограничения:
-нельзя изменить схемы для вычисляемых полей
-индексированных полей
-полей с ограничением CHECK или внешних ключей.
Необходимо вначале удалить их, а после изменения схемы сопоставления заново создать. Так что работа здесь может быть проделана большая и серьезная.
2. Information_schema

Схема INFORMATION_SCHEMA- это особая схема, которая есть в каждой базе данных.
Она содержит определения некоторых объектов базы данных.
Подробное описание представлений схемы тут

Решение:

Select  'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' ' + DATA_TYPE + Case when IsNULL(CHARACTER_MAXIMUM_LENGTH,0) > 0 then '('+Convert(varchar(10),CHARACTER_MAXIMUM_LENGTH) + ')'
 else '' end + ' COLLATE Cyrillic_General_CS_AS'
 from
 INFORMATION_SCHEMA.COLUMNS
where
(COLLATION_NAME = 'Cyrillic_General_CI_AS') and (TABLE_CATALOG=DB_name())and (DATA_TYPE LIKE '%char%')