Данный материал является переводом оригинальной статьи «MSSQLTips : How to change server level collation for a SQL Server Instance».
Случилось так, что уже после того, как выполнена установка экземпляра SQL Server, вам с опозданием сообщили, что вы должны использовать другой параметр сортировки (Collation) для этого экземпляра. В этой статье пошагово опишем то, как можно изменить настройку параметров сортировки на уровне сервера для существующего экземпляра SQL Server.
Прежде чем идти дальше, давайте обсудим, что используется для настройки параметров сортировки в соответствии с Books Online.
«В параметрах сортировки задаются правила сортировки и сравнения строк символьных данных на основе норм конкретных языков и локалей. Например, в предложении ORDER BY англоговорящий ожидает, что строка символов «Chiapas» появится до «Colima» в порядке возрастания. Однако испаноговорящий в Мексике может ожидать, что слова, начинающиеся с «Ch», появятся в конце списка слов, начинающихся с «C». Параметры сортировки диктуют эти правила сортировки и сравнения. Параметр сортировки Latin_1 будет сортировать «Chiapas» до «Colima» в предложении ORDER BY ASC, тогда как параметр сортировки Traditional_Spanish будет сортировать «Chiapas» после «Colima».
Параметры сортировки сервера действуют, как параметры сортировки по умолчанию для всех системных баз данных, которые установлены с экземпляром SQL Server, а также с любыми новыми созданными пользовательскими базами данных. В процессе установки SQL Server указываются параметры сортировки сервера. Не обязательно, чтобы мы меняли параметры сортировки уровня сервера по умолчанию, потому что вы можете указать другой уровень параметров сортировки при создании пользовательских баз данных, но вам нужно помнить, что нужно указывать его при создании пользовательских баз данных.
Чтобы изменить параметры сортировки SQL Server по умолчанию, вы можете просто перестроить системные базы данных. Когда вы перестраиваете системную базу данных master, системные базы model, msdb и tempdb фактически удаляются и воссоздаются в исходном местоположении. Если в инструкции rebuild указаны новые параметры сортировки, системные базы данных перестраиваются с использованием этой настройки параметров сортировки. Любые пользовательские изменения в этих базах данных будут потеряны, поэтому важно создать резервную копию любой из той информации, которую вы хотите сохранить. Например, у вас могут быть определенные пользователем объекты в базе данных master, запланированные задания в msdb или изменения параметров базы данных по умолчанию в базе данных model. База данных tempdb воссоздается каждый раз при перезапуске SQL Server, поэтому в этой базе данных нет ничего, что нужно сохранять. Изменение параметров сортировки на уровне сервера не изменяет параметров сортировки существующих пользовательских баз данных, но все вновь созданные пользовательские базы данных будут использовать новые параметры сортировки по умолчанию.
ПРИМЕЧАНИЕ: НЕ ДЕЛАЙТЕ ЛЮБЫЕ ИЗМЕНЕНИЯ В РАБОЧЕЙ СРЕДЕ БЕЗ ДОЛЖНЫХ ИСПЫТАНИЙ В СРЕДЕ ТЕСТИРОВАНИЯ
Далее рассмотрим шаги, необходимые для изменения параметров сортировки на уровне сервера на заданном экземпляре SQL Server.
Шаг 1. Проверка текущих параметров сортировки
Сначала проверьте существующие параметры сортировки SQL Server вашего экземпляра. Выполните приведенную ниже команду, чтобы получить значение параметров сортировки экземпляра SQL Server.
SELECT SERVERPROPERTY(N'Collation')
Как видите, в данном случае установлен порядок сортировки «SQL_Latin1_General_CP1_CI_AS».
Шаг 2. Сохранение системной конфигурации
В нашем примере необходимо изменить текущий порядок сортировки на «SQL_Latin1_General_CP1_CI_AI». Как я уже упоминал, мы должны перестроить наши системные базы данных, чтобы изменить параметры сортировки на уровне сервера и поместить это новое значение параметра сортировки в команду rebuild. Обязательно запишите все настройки уровня сервера до пересоздания системных баз данных, чтобы смочь обеспечить восстановление системных баз данных до их текущих настроек. Запишите все значения конфигурации сервера, выполнив приведенные ниже команды и сохраните вывод. Если это была новая настройка, и вы не внесли никаких изменений в системные базы данных, вам не нужно беспокоиться о сборе этих данных.
SELECT * FROM sys.configurations;
-- OR
EXEC SP_CONFIGURE
Шаг 3. Сохранение сценариев создания объектов
Создайте и подготовьте все сценарии, связанные с заданиями, планами обслуживания, логинами и уровнями доступа. Вы можете создавать сценарии, выбирая все задания в проводнике объектов в SSMS и щелкая правой кнопкой мыши по вашему выбору, затем выберите вариант «script as» для создания сценария для всех заданий. Вы можете сделать аналогичные шаги для генерации сценариев для предупреждений и операторов. Ниже снимок экрана для создания сценариев для всех ваших заданий.
Далее следует обеспечить безопасность ваших логинов, паролей и их уровней доступа. Вы можете использовать хранимую процедуру sp_help_revlogin для создания сценария для всех логинов, чтобы их можно было легко воссоздать.
Шаг 4. Отсоединение пользовательских БД
Отсоедините все пользовательские базы данных до пересоздания системных баз данных. Если вы оставите прикрепленные базы данных, они будут отсоединены и будут находиться в папке базы данных.
Шаг 5. Перестроение системных БД
Теперь пришло время перестроить ваши системные базы данных. Эта операция воссоздает вашу базу данных master и все существующие настройки будут сброшены. Выполните команду ниже из командной строки Windows. Обязательно запустите эту команду из каталога, в который вы разместили установочные файлы SQL Server. Как только вы нажмете Enter, появится отдельное окно, чтобы показать вам индикатор выполнения. Как только перестройка будет завершена, это окно исчезнет.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=gouranghariom /SAPWD=M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
По завершении операции перестроения, проверьте параметры сортировки сервера, чтобы проверить, выполнено ли это изменение или нет. Как мы видим на скриншоте ниже, параметры сортировки сервера изменились на «SQL_Latin1_General_CP1_CI_AI». На этом этапе мы не можем восстановить какую-либо из системных баз данных, так как это вернет нас обратно к предыдущей настройке параметров сортировки. Поэтому, нам нужно будет использовать сценарии, созданные для воссоздания логинов, заданий и т.д…
Шаг 6. Присоединение пользовательских БД
Прикрепите все пользовательские базы данных, которые были отсоединены на шаге 4. Если у вас есть какие-либо проблемы, взгляните на этот совет: How to fix database attach error in SQL Server 2008R2.
Шаг 7. Обновление пользовательских БД (опционально)
Теперь измените настройки параметров сортировки для всех пользовательских баз данных. Нет необходимости изменять настройки параметров сортировки для пользовательских баз данных, это полностью зависит от ваших требований. Выполните приведенные ниже команды, чтобы изменить настройки параметров сортировки ваших пользовательских баз данных.
ALTER DATABASE DBName collate SQL_Latin1_General_CP1_CI_AI
Иногда команда не выполняется, и вы получаете ошибку:
Msg 5075, Level 16, State 1, Line 1 The object 'CK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
В этом случае вам может потребоваться экспортировать все данные и воссоздать базу данных с новыми настройками параметров сортировки.
Шаг 8. Восстановление системной конфигурации и сценариев
Теперь запустите все сценарии, созданные на шаге 3, чтобы восстановить задания, предупреждения (alerts), логины, операторы и т.д. Также не забудьте изменить настройки конфигурации на уровне сервера, которые были зафиксированы на Шаге 2. Теперь ваш экземпляр готов использовать новые параметры сортировки уровня сервера.
Changing SQL Server Collation In Simple Steps
Here is the simpler steps to change the SQL Server Instance Collation, Please note once the collation is changed we may need to modify the query according to the need as the collation it was referring will change.
Steps:
- Open the CMD in elevated privilege
- Check the services
sc queryex type= service state=
all
| find /i
"SQL Server"
- Stop SQL Server NET STOP «SQL Server (SQLEXPRESS2008R2)»
- Move to Binn Directory from CMD
EXAMPLE: D:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESS2008R2MSSQLBinn - type DIR and Check SQL Server.exe is listed
- Apply a New SQL Server Collation
sqlservr -m -T
4022
-T
3659
-s"SQLEXPRESS2008R2"
-q"SQL_Latin1_General_CP1_CI_AS"
[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied4022
Used to bypass automatically started (startup) procedures, this is a subset of startup option –f.
TIP: Each SP consumes one worker thread while executing so you may prefer to make one startup procedure that calls others.3659
After a lot of research, I found a reference to this flag in a script called AddSelfToSqlSysadmin, written by Ward Beattie, a developer in the SQL Server product group at Microsoft.
The script contains a line which suggests that this flag enables logging all errors to error log during server startup. - SQL Server may start in single user mode, if command prompt doesn’t end press ctrlX, this will prompt Y/N to shutdown SQL Server, Choose Y
2018
-08
-01
17:
12:
30.25
spid
7
s The
default
collation was successfully change
d.
2018
-08
-01
17:
12:
30.26
spid
7
s Recovery is complete. This is an informationa
l message only. No user action is required.
2018
-08
-01
17:
12:
42.01
Logon Error:
18461
, Severity:
14
, State:
1
.
2018
-08
-01
17:
12:
42.01
Logon Login failed for user 'NT AUTHORITYNETWORK S
ERVICE'. Reason: Server is in single user mode. Only one administrator can conne
ct at this time. [CLIENT: <
local
machine>]
2018
-08
-01
17:
13:
41.91
Logon Error:
18461
, Severity:
14
, State:
1
.
2018
-08
-01
17:
13:
41.91
Logon Login failed for user 'NT AUTHORITYNETWORK S
ERVICE'. Reason: Server is in single user mode. Only one administrator can conne
ct at this time. [CLIENT: <
local
machine>]
Do you wish to shutdown SQL Server (Y/N)? Y
- Start SQL and verify the collation
NET START
"SQL Server (SQLEXPRESS2008R2)"
D:Program FilesMicrosoft SQL ServerMSSQL
10
_
50
.SQLEXPRESS
2008
R
2
MSSQLBinn>
NET START
"SQL Server (SQLEXPRESS2008R2)"
The SQL Server (SQLEXPRESS
2008
R
2
)
service is starting.The SQL Server (SQLEXPRESS
2008
R
2
)
service was started successfully.
Exporting all of the data (including logins, linked servers, SQL Agent jobs, DB Mail settings, etc), and rebuilding the instance-level data, plus reloading all of the user data, is a lot of work. And, even after all of that, there’s still no guarantee that you can update a database’s default collation via ALTER DATABASE
because there are several conditions that will prevent the operation from completing (please see the «Changing the Database Collation» section of the ALTER DATABASE
documentation for details).
There is, however, an undocumented method that is much easier. The main drawback being that it’s unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won’t help fix it (because they never guaranteed that it would work).
The method I speak of is running sqlservr.exe
with the -q {new_collation_name}
switch. There’s a little more to it than that, but that is the basic idea. This method simply updates the system meta-data, which has benefits and consequences, the main ones being:
BENEFITS
- pretty fast
- bypass most restrictions that prevent
ALTER DATABASE
from working - likely far more accurate than any script that people have come up with over the years to drop and recreate objects
DRAWBACKS
- unsupported if something goes wrong
VARCHAR
data can change, IF the code page is different between the old and new collations, and characters with values of 128 — 255 (0x80 — 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 — 127 which are not in any danger, even if the code page changes.- User-Defined Table Types (UDTTs) are skipped and need to be updated manually.
For a detailed description of what the sqlservr.exe -q
method does and does not do (including details on how collations work at the various levels, and potential issues to watch out for), please see my post:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
To change only the instance (including the system databases: master
, model
, msdb
, and tempdb
) and one or more databases (but not all databases), simply detach the database(s) that you want to exclude from this operation, and then re-attach them once the collation update completes.
Exporting all of the data (including logins, linked servers, SQL Agent jobs, DB Mail settings, etc), and rebuilding the instance-level data, plus reloading all of the user data, is a lot of work. And, even after all of that, there’s still no guarantee that you can update a database’s default collation via ALTER DATABASE
because there are several conditions that will prevent the operation from completing (please see the «Changing the Database Collation» section of the ALTER DATABASE
documentation for details).
There is, however, an undocumented method that is much easier. The main drawback being that it’s unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won’t help fix it (because they never guaranteed that it would work).
The method I speak of is running sqlservr.exe
with the -q {new_collation_name}
switch. There’s a little more to it than that, but that is the basic idea. This method simply updates the system meta-data, which has benefits and consequences, the main ones being:
BENEFITS
- pretty fast
- bypass most restrictions that prevent
ALTER DATABASE
from working - likely far more accurate than any script that people have come up with over the years to drop and recreate objects
DRAWBACKS
- unsupported if something goes wrong
VARCHAR
data can change, IF the code page is different between the old and new collations, and characters with values of 128 — 255 (0x80 — 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 — 127 which are not in any danger, even if the code page changes.- User-Defined Table Types (UDTTs) are skipped and need to be updated manually.
For a detailed description of what the sqlservr.exe -q
method does and does not do (including details on how collations work at the various levels, and potential issues to watch out for), please see my post:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
To change only the instance (including the system databases: master
, model
, msdb
, and tempdb
) and one or more databases (but not all databases), simply detach the database(s) that you want to exclude from this operation, and then re-attach them once the collation update completes.
I’ve pulled together the script below from various different sources that drop the dependencies updates the collation then recreate the dependant objects. This solves the problem of updating the collation of columns that have dependencies (indexes, foreign key constraints, etc.)
/*******************************************************************************
*
* Created 2017-06-16 By Philip C
* Shared on: https://stackoverflow.com/questions/16730114/how-to-change-the-collate-to-all-the-columns-of-the-database/44587493#44587493
*
* This script will check individual columns collations and check it against the
* database default collation, where they are different it will create the scripts
* required to drop all the objects dependant on the column, change the collation
* to the database default and then recreate the dependant objects.
* Some of the code has been reused from stuff found online the majority from
* Jayakumaur R who created scripts to drop and recreate constraints
*
* Minor edit by Darren S to support SQL2019: Replaced AND ty.name !='sysname' with AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%')
*********************************************************************************/
SET ANSI_WARNINGS OFF;
GO
DECLARE @SchemaName VARCHAR(100);
DECLARE @TableName VARCHAR(256);
DECLARE @IndexName VARCHAR(256);
DECLARE @ColumnName VARCHAR(100);
DECLARE @is_unique VARCHAR(100);
DECLARE @IndexTypeDesc VARCHAR(100);
DECLARE @FileGroupName VARCHAR(100);
DECLARE @is_disabled VARCHAR(100);
DECLARE @IndexOptions VARCHAR(MAX);
DECLARE @IndexColumnId INT;
DECLARE @IsDescendingKey INT;
DECLARE @IsIncludedColumn INT;
DECLARE @TSQLScripCreationIndex VARCHAR(MAX);
DECLARE @TSQLScripDisableIndex VARCHAR(MAX);
DECLARE @Collation_objectid INT;
DECLARE @Collation_columnid INT;
DECLARE @Collation_constraint INT;
DECLARE @Collation_index INT;
DECLARE @Collation_foreign INT;
DECLARE @Collation_stats INT;
DECLARE @stats_id INT;
DECLARE @Collation_fkid INT;
DECLARE @Collation_unique INT;
DECLARE @DatabaseCollation VARCHAR(100);
CREATE TABLE #tempscriptstore (ScriptType VARCHAR(20),
script NVARCHAR(MAX));
SELECT @DatabaseCollation=collation_name
FROM sys.databases
WHERE database_id=DB_ID();
/************************************************************************************************************************************
* Generates a list of all the columns where their collation doesn't match the database default and the depenmdancies they have. *
************************************************************************************************************************************/
DECLARE collationfix CURSOR FOR
SELECT t.object_id, c.column_id, COUNT(kc.object_id) AS [has_key_constraint], COUNT(ic.index_id) AS [has_index], COUNT(fk.constraint_object_id) AS [has_foreign_key], COUNT(st.stats_id) AS [has_stats], COUNT(uq.object_id) AS [has_unique_constraint]
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id=t.object_id
INNER JOIN sys.types ty ON c.system_type_id=ty.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id
LEFT JOIN sys.key_constraints kc ON kc.parent_object_id=c.object_id AND kc.unique_index_id=ic.index_id AND kc.type='PK'
LEFT JOIN sys.key_constraints uq ON uq.parent_object_id=c.object_id AND uq.unique_index_id=ic.index_id AND uq.type='UQ'
LEFT JOIN sys.foreign_key_columns fk ON fk.referenced_object_id=c.object_id AND fk.constraint_column_id=c.column_id
LEFT JOIN sys.stats_columns st ON st.object_id=c.object_id AND st.column_id=c.column_id AND st.stats_column_id !=1
WHERE t.is_ms_shipped=0 AND c.collation_name<>@DatabaseCollation AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%')
GROUP BY t.object_id, c.column_id;
OPEN collationfix;
FETCH NEXT FROM collationfix
INTO @Collation_objectid, @Collation_columnid, @Collation_constraint, @Collation_index, @Collation_foreign, @Collation_stats, @Collation_unique;
WHILE(@@FETCH_STATUS=0)BEGIN
/************************************************************************************************************************************
* Generates the code to update the columns colation *
************************************************************************************************************************************/
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AlterCollation', 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +QUOTENAME(t.name)+' ALTER COLUMN '+QUOTENAME(c.name)+' '+CASE WHEN ty.name='ntext' THEN ty.name+' COLLATE '+@DatabaseCollation+' ' ELSE ty.name+'('+CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CASE WHEN ty.name='nvarchar' THEN CAST(c.max_length / 2 AS VARCHAR(20))ELSE CAST(c.max_length AS VARCHAR(20))END END+') COLLATE '+@DatabaseCollation+' ' END+CASE WHEN c.is_nullable=1 THEN 'NULL;' ELSE 'NOT NULL;' END
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id=t.object_id
INNER JOIN sys.types ty ON c.system_type_id=ty.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id
WHERE t.is_ms_shipped=0 AND c.collation_name<>@DatabaseCollation AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%') AND c.column_id=@Collation_columnid AND t.object_id=@Collation_objectid;
/************************************************************************************************************************************
* If the column is in an index this creates the drop and recreate index script *
************************************************************************************************************************************/
IF @Collation_index>0 BEGIN
DECLARE CursorIndex CURSOR FOR
SELECT DISTINCT SCHEMA_NAME(t.schema_id) [schema_name], t.name, ix.name, CASE WHEN ix.is_unique=1 THEN 'UNIQUE ' ELSE '' END, ix.type_desc, CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END+CASE WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END+CASE WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END+CASE WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics')=1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END+CASE WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END+'SORT_IN_TEMPDB = OFF, FILLFACTOR ='+CASE WHEN ix.fill_factor=0 THEN CAST(100 AS VARCHAR(3))ELSE CAST(ix.fill_factor AS VARCHAR(3))END AS IndexOptions, ix.is_disabled, FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
JOIN sys.indexes ix ON t.object_id=ix.object_id
JOIN sys.columns c ON c.object_id=t.object_id
JOIN sys.index_columns ic ON ic.index_id=ix.index_id AND ic.column_id=c.column_id AND ic.object_id=t.object_id
WHERE ix.type>0 AND ix.is_primary_key=0 AND ix.is_unique_constraint=0
--AND schema_name(tb.schema_id)= @SchemaName
--AND tb.name=@TableName
AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams' AND c.column_id=@Collation_columnid AND t.object_id=@Collation_objectid AND ic.column_id=@Collation_columnid
ORDER BY SCHEMA_NAME(t.schema_id), t.name, ix.name;
OPEN CursorIndex;
FETCH NEXT FROM CursorIndex
INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName;
WHILE(@@fetch_status=0)BEGIN
DECLARE @IndexColumns VARCHAR(MAX);
DECLARE @IncludedColumns VARCHAR(MAX);
SET @IndexColumns='';
SET @IncludedColumns='';
DECLARE CursorIndexColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id=ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id=col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND(ix.is_primary_key=0 OR ix.is_unique_constraint=0)AND SCHEMA_NAME(tb.schema_id)=@SchemaName AND tb.name=@TableName AND ix.name=@IndexName
ORDER BY ixc.index_column_id;
OPEN CursorIndexColumn;
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn;
WHILE(@@fetch_status=0)BEGIN
IF @IsIncludedColumn=0
SET @IndexColumns=@IndexColumns+@ColumnName+CASE WHEN @IsDescendingKey=1 THEN ' DESC, ' ELSE ' ASC, ' END;
ELSE SET @IncludedColumns=@IncludedColumns+@ColumnName+', ';
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn;
END;
CLOSE CursorIndexColumn;
DEALLOCATE CursorIndexColumn;
SET @IndexColumns=SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns)-1);
SET @IncludedColumns=CASE WHEN LEN(@IncludedColumns)>0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns)-1)ELSE '' END;
-- print @IndexColumns
-- print @IncludedColumns
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'DropIndex', 'DROP INDEX '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+'.'+QUOTENAME(@IndexName)+';';
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'CreateIndex', 'CREATE '+@is_unique+@IndexTypeDesc+' INDEX '+QUOTENAME(@IndexName)+' ON '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+'('+@IndexColumns+') '+CASE WHEN LEN(@IncludedColumns)>0 THEN CHAR(13)+'INCLUDE ('+@IncludedColumns+')' ELSE '' END+CHAR(13)+'WITH ('+@IndexOptions+') ON '+QUOTENAME(@FileGroupName)+';';
IF @is_disabled=1
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'DisableIndex', 'ALTER INDEX '+QUOTENAME(@IndexName)+' ON '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+' DISABLE;';
FETCH NEXT FROM CursorIndex
INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName;
END;
CLOSE CursorIndex;
DEALLOCATE CursorIndex;
END;
/************************************************************************************************************************************
* If the column has a primary key constraint this creates the drop and recreate constraint script *
* this has been taken and adapted from a script found online created by Jayakumaur R *
************************************************************************************************************************************/
IF @Collation_constraint>0 BEGIN
-------------------------------------------------
--ALTER TABLE DROP PRIMARY KEY CONSTRAINT Queries
-------------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropPrimaryKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sys.key_constraints skc
WHERE type='PK' AND parent_object_id=@Collation_objectid;
---------------------------------------------------
--ALTER TABLE CREATE PRIMARY KEY CONSTRAINT Queries
---------------------------------------------------
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) AS pk_table, --PK table name
skc.object_id AS constid, QUOTENAME(skc.name) AS constraint_name, --PK name
QUOTENAME(iskcu.COLUMN_NAME)+CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END AS pk_col, iskcu.ORDINAL_POSITION, CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END AS index_unique_type, si.name AS index_name, si.type_desc AS index_type, QUOTENAME(fg.name) AS filegroup_name, 'WITH('+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END+','+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END+')' AS index_property
--,*
INTO #temp_pk
FROM sys.key_constraints skc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE iskcu ON skc.name=iskcu.CONSTRAINT_NAME
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id AND si.is_primary_key=1
INNER JOIN sys.index_columns sic ON si.object_id=sic.object_id AND si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg ON si.data_space_id=fg.data_space_id
WHERE skc.type='PK' AND iskcu.COLUMN_NAME=c.name AND skc.parent_object_id=@Collation_objectid
ORDER BY skc.parent_object_id, skc.name, ORDINAL_POSITION;
WITH cte AS (SELECT pk_table, constraint_name, index_type, SUBSTRING((SELECT ','+pk_col FROM #temp_pk WHERE constid=t.constid FOR XML PATH('')), 2, 99999) AS pk_col_list, index_unique_type, filegroup_name, index_property
FROM #temp_pk t)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddPrimaryKey', 'ALTER TABLE '+pk_table+' ADD CONSTRAINT '+constraint_name+' PRIMARY KEY '+CAST(index_type COLLATE DATABASE_DEFAULT AS VARCHAR(100))+' ('+pk_col_list+')'+index_property+' ON '+filegroup_name+''
FROM cte;
--dropping the temp tables
DROP TABLE #temp_pk;
END;
/************************************************************************************************************************************
* If the column has a foreign key constraint this creates the drop and recreate constraint script *
* this has been taken and adapted from a script found online cretaed by Jayakumaur R *
************************************************************************************************************************************/
IF @Collation_foreign>0 BEGIN
DECLARE foreignkeycursor CURSOR FOR
SELECT constraint_object_id
FROM sys.foreign_key_columns
WHERE referenced_object_id=@Collation_objectid AND referenced_column_id=@Collation_columnid;
OPEN foreignkeycursor;
FETCH NEXT FROM foreignkeycursor
INTO @Collation_fkid;
WHILE(@@FETCH_STATUS=0)BEGIN
---------------------------------------------
--ALTER TABLE DROP FOREIGN CONSTRAINT Queries
---------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropForeignKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid))+' DROP CONSTRAINT '+QUOTENAME(OBJECT_NAME(constid))
FROM sys.sysforeignkeys sfk
WHERE sfk.constid=@Collation_fkid;
------------------------------------------------
--ALTER TABLE CREATE FOREIGN CONSTRAINT Queries
------------------------------------------------
--Obtaining the necessary info from the sys tables
SELECT constid, QUOTENAME(OBJECT_NAME(constid)) AS constraint_name, CASE WHEN fk.is_not_trusted=1 THEN 'WITH NOCHECK' ELSE 'WITH CHECK' END AS trusted_status, QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid)) AS fk_table, QUOTENAME(c1.name) AS fk_col, QUOTENAME(OBJECT_SCHEMA_NAME(rkeyid))+'.'+QUOTENAME(OBJECT_NAME(rkeyid)) AS rk_table, QUOTENAME(c2.name) AS rk_col, CASE WHEN fk.delete_referential_action=1 AND fk.delete_referential_action_desc='CASCADE' THEN 'ON DELETE CASCADE ' ELSE '' END AS delete_cascade, CASE WHEN fk.update_referential_action=1 AND fk.update_referential_action_desc='CASCADE' THEN 'ON UPDATE CASCADE ' ELSE '' END AS update_cascade, CASE WHEN fk.is_disabled=1 THEN 'NOCHECK' ELSE 'CHECK' END AS check_status
--,sysfk.*,fk.*
INTO #temp_fk
FROM sys.sysforeignkeys sysfk
INNER JOIN sys.foreign_keys fk ON sysfk.constid=fk.object_id
INNER JOIN sys.columns c1 ON sysfk.fkeyid=c1.object_id AND sysfk.fkey=c1.column_id
INNER JOIN sys.columns c2 ON sysfk.rkeyid=c2.object_id AND sysfk.rkey=c2.column_id
WHERE sysfk.constid=@Collation_fkid
ORDER BY constid, sysfk.keyno
--building the column list for foreign/primary key tables
;
WITH cte AS (SELECT DISTINCT constraint_name, trusted_status, fk_table, SUBSTRING((SELECT ','+fk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')), 2, 99999) AS fk_col_list, rk_table, SUBSTRING((SELECT ','+rk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')), 2, 99999) AS rk_col_list, check_status, delete_cascade, update_cascade
FROM #temp_fk c)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddForeignKey', 'ALTER TABLE '+fk_table+' '+trusted_status+' ADD CONSTRAINT '+constraint_name+' FOREIGN KEY('+fk_col_list+') REFERENCES '+rk_table+'('+rk_col_list+')'+' '+delete_cascade+update_cascade+';'+' ALTER TABLE '+fk_table+' '+check_status+' CONSTRAINT '+constraint_name
FROM cte;
--dropping the temp tables
DROP TABLE #temp_fk;
FETCH NEXT FROM foreignkeycursor
INTO @Collation_fkid;
END;
CLOSE foreignkeycursor;
DEALLOCATE foreignkeycursor;
END;
/************************************************************************************************************************************
* If the column has statistics that aren't part of an index this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @Collation_stats>0 AND @Collation_index=0 BEGIN
DECLARE stats_cursor CURSOR FOR
SELECT sc.stats_id
FROM sys.stats_columns sc
JOIN sys.stats s ON s.object_id=sc.object_id AND s.stats_id=sc.stats_id AND s.user_created=1
WHERE sc.object_id=@Collation_objectid AND sc.column_id=@Collation_columnid;
OPEN stats_cursor;
FETCH NEXT FROM stats_cursor
INTO @stats_id;
WHILE(@@FETCH_STATUS=0)BEGIN
--Create DROP Statistics Statement
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'DropStatistics', 'DROP STATISTICS '+QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id))+'.'+QUOTENAME(OBJECT_NAME(s.object_id))+'.'+QUOTENAME(s.name)
FROM sys.stats s
WHERE s.object_id=@Collation_objectid AND s.stats_id=@stats_id;
--Building the CREATE statistics statement
--Obtaining all the information
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id))+'.'+QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table, QUOTENAME(s.name) AS st_name, QUOTENAME(c.name) AS st_column, sc.object_id, sc.stats_id, sc.stats_column_id
INTO #temp_stats
FROM sys.stats_columns sc
JOIN sys.stats s ON s.stats_id=sc.stats_id AND s.object_id=sc.object_id
JOIN sys.columns c ON c.object_id=sc.object_id AND c.column_id=sc.column_id
WHERE sc.object_id=@Collation_objectid AND sc.stats_id=@stats_id;
WITH cte AS (SELECT DISTINCT st_table, st_name, SUBSTRING((SELECT ','+st_column
FROM #temp_stats
WHERE stats_id=ts.stats_id
ORDER BY stats_column_id ASC
FOR XML PATH('')), 2, 99999) AS st_col_list
FROM #temp_stats ts)
--Constructing the statement
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'AddStatistics', 'CREATE STATISTICS '+cte.st_name+' ON '+cte.st_table+'('+cte.st_col_list+')'
FROM cte;
DROP TABLE #temp_stats;
FETCH NEXT FROM stats_cursor
INTO @stats_id;
END;
CLOSE stats_cursor;
DEALLOCATE stats_cursor;
END;
/************************************************************************************************************************************
* If the column has a unique constraint this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @Collation_unique>0 BEGIN
-------------------------------------------------
--ALTER TABLE DROP UNIQUE CONSTRAINT Queries
-------------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropUniqueKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sys.key_constraints skc
JOIN sys.index_columns ic ON ic.object_id=skc.parent_object_id AND ic.index_id=skc.unique_index_id
WHERE type='UQ' AND parent_object_id=@Collation_objectid AND ic.column_id=@Collation_columnid;
---------------------------------------------------
--ALTER TABLE CREATE UNIQUE CONSTRAINT Queries
---------------------------------------------------
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) AS uq_table, --PK table name
skc.object_id AS constid, QUOTENAME(skc.name) AS constraint_name, --PK name
QUOTENAME(iskcu.COLUMN_NAME)+CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END AS uq_col, iskcu.ORDINAL_POSITION, CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END AS index_unique_type, si.name AS index_name, si.type_desc AS index_type, QUOTENAME(fg.name) AS filegroup_name, 'WITH('+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END+','+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END+')' AS index_property
--,*
INTO #temp_uq
FROM sys.key_constraints skc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE iskcu ON skc.name=iskcu.CONSTRAINT_NAME
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id AND si.is_unique=1
INNER JOIN sys.index_columns sic ON si.object_id=sic.object_id AND si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg ON si.data_space_id=fg.data_space_id
WHERE skc.type='UQ' AND iskcu.COLUMN_NAME=c.name AND skc.parent_object_id=@Collation_objectid AND c.column_id=@Collation_columnid
ORDER BY skc.parent_object_id, skc.name, ORDINAL_POSITION;
WITH cte AS (SELECT uq_table, constraint_name, index_type, SUBSTRING((SELECT ','+uq_col FROM #temp_uq WHERE constid=t.constid FOR XML PATH('')), 2, 99999) AS uq_col_list, index_unique_type, filegroup_name, index_property
FROM #temp_uq t)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddUniqueKey', 'ALTER TABLE '+uq_table+' ADD CONSTRAINT '+constraint_name+' UNIQUE '+CAST(index_type COLLATE DATABASE_DEFAULT AS VARCHAR(100))+' ('+uq_col_list+')'+index_property+' ON '+filegroup_name+''
FROM cte;
--dropping the temp tables
DROP TABLE #temp_uq;
END;
FETCH NEXT FROM collationfix
INTO @Collation_objectid, @Collation_columnid, @Collation_constraint, @Collation_index, @Collation_foreign, @Collation_stats, @Collation_unique;
END;
CLOSE collationfix;
DEALLOCATE collationfix;
/************************************************************************************************************************************
* Returns all the created scripts in the correct order for running *
************************************************************************************************************************************/
SELECT DISTINCT script, CASE WHEN ScriptType='DropForeignKey' THEN 1
WHEN ScriptType='DropPrimaryKey' THEN 2
WHEN ScriptType='DropUniqueKey' THEN 3
WHEN ScriptType='DropIndex' THEN 4
WHEN ScriptType='DropStatistics' THEN 5
WHEN ScriptType='AlterCollation' THEN 6
WHEN ScriptType='CreateIndex' THEN 7
WHEN ScriptType='DisableIndex' THEN 8
WHEN ScriptType='AddStatistics' THEN 9
WHEN ScriptType='AddUniqueKey' THEN 10
WHEN ScriptType='AddPrimaryKey' THEN 11
WHEN ScriptType='AddForeignKey' THEN 12 ELSE 99 END AS [exec_order]
FROM #tempscriptstore
WHERE script !=''
ORDER BY exec_order ASC;
DROP TABLE #tempscriptstore;