Feb 22 2010 at 05:13

Изменение COLLATION у базы SQL Server 2005

Invite pending

Что делать, когда требуется изменить Collation существующей базы SQL Server, а ALTER DATABASE <DATA_BASE_NAME> COLLATE <NEW_COLLATION_NAME> не помогает? И гугление не дает одназначного ответа и даже sql.ru запутанно приводит не совсем те аргументы, что хотелось бы видеть.

Прибегнуть к следующему способу (за основу взят метод):

1) Выполнить backup базы;

2) Сгенерировать ключи, индейсы и констрэйнты:

Очень поможет утилита ScriptDB.

Формат запуска:

ScriptDb.exe server=(local);database=<DATA_BASE_NAME>;trusted_connection=yes <SCRIPTS_FOLDER_PATH> false true

При этом каждый объект базы будет создан отдельным sql скриптом и расположен иерархично в структуре каталогов экспорта.

3) Удалить ключи, индексы и констрэйнты изменяемой базы:

a. Ключи и констрэйнты:

В Enterprise Manager выполнить:

select 'ALTER TABLE [' + TABLE_NAME + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']'

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

where constraint_type='PRIMARY KEY' 

 or constraint_type='FOREIGN KEY'

 or constraint_type='UNIQUE'

 or constraint_type='CHECK'

ORDER BY constraint_type

В итоге получим набор запросов по удалению PK, FK, U и С. Выделяем, копируем, выполняем.

b. Индексы:

В Enterprise Manager выполнить:

SELECT 'drop index ' + o.name + '.' + i.name AS ScriptBody

FROM sysobjects o INNER JOIN sysindexes i ON o.id = i.id

WHERE (o.xtype = 'U') AND 

 (i.indid > 0) AND 

 (i.indid < 255) AND 

 (INDEXPROPERTY(i.id, i.name, 'isStatistics') = 0)

and o.name <> 'dtproperties'

ORDER BY o.name, i.indid

Получаем набор запросов по удалению индексов. Выделяем, копируем, выполняем.

4) Изменяем Collation у полей таблиц:

Заменяем в скрипте ниже <NEW_COLLATION_NAME> на нужное наименование Collation:

SELECT 'ALTER TABLE ['+ 

 rtrim(TABLE_NAME)+

 '] ALTER COLUMN ['+

 rtrim(COLUMN_NAME)+

 '] '+

 rtrim(DATA_TYPE)+

 CASE 

 WHEN UPPER(rtrim(DATA_TYPE))=UPPER('ntext') THEN '' 

 WHEN UPPER(rtrim(DATA_TYPE))=UPPER('text') THEN ''

 WHEN CHARACTER_MAXIMUM_LENGTH=-1 THEN ''

 WHEN NOT(CHARACTER_MAXIMUM_LENGTH IS NULL) OR (CHARACTER_MAXIMUM_LENGTH=0)

 THEN '('+convert(varchar(10),CHARACTER_MAXIMUM_LENGTH)+')'

 END+

 ' <NEW_COLLATION_NAME>' COLLATE <NEW_COLLATION_NAME>

 FROM INFORMATION_SCHEMA.COLUMNS 

 WHERE (TABLE_CATALOG=DB_NAME() COLLATE <NEW_COLLATION_NAME>) AND 

 ((DATA_TYPE LIKE '%char%' COLLATE <NEW_COLLATION_NAME>) OR (DATA_TYPE LIKE '%text%' COLLATE <NEW_COLLATION_NAME>)) AND

 (COLLATION_NAME IS NOT NULL) AND

 (COLLATION_NAME <> ' <NEW_COLLATION_NAME>' COLLATE <NEW_COLLATION_NAME>) AND 

 TABLE_NAME in (SELECT o.name 

 FROM sysobjects o 

 WHERE (o.xtype = 'U'))

Выполняем в EM, получаем набор запросов по изменению полей. Выделяем, копируем, выполняем.

5) Меняем Collation базы:

ALTER DATABASE <DATA_BASE_NAME> COLLATE <NEW_COLLATION_NAME>

Наконец-то работает.

6) Восстанавливаем ключи, индексы и констрэйнты.

Опять воспользуемся утилитой ScriptDB. В ее состав входит .bat'ник по массовому выполнению экспортированных скриптов – builddb.bat. Но не все так сразу, при экспорте у нас создались скрипты так же для таблиц, хранимых процедур, функций и т.д., чтобы ничего лишнего не выполнить, открываем builddb.bat на редактирование, комментируем лишние объекты:

REM call :BuildDirectory "%script_directory%\Schemas\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Assemblies\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Assemblies\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Types\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Defaults\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Rules\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Tables\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Views\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Functions\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\StoredProcedures\*.sql" || goto Done

call :BuildDirectory "%script_directory%\Tables\Constraints\*.sql" || goto Done

call :BuildDirectory "%script_directory%\Tables\PrimaryKeys\*.sql" || goto Done

call :BuildDirectory "%script_directory%\Tables\UniqueKeys\*.sql" || goto Done

call :BuildDirectory "%script_directory%\Tables\ForeignKeys\*.sql" || goto Done

call :BuildDirectory "%script_directory%\Tables\Indexes\*.sql" || goto Done

REM call :BuildDirectory "%script_directory%\Programmability\Triggers\*.sql" || goto Done

После чего, выполняем батник с параметрами:

builddb.bat <DATA_BASE_NAME> <SQL_SERVER_NAME> < SCRIPTS_FOLDER_PATH +"\"+ DATA_BASE_NAME>

В результате Collation базы изменен.

Hubs:

Lumber room