Как изменить сопоставление для всех столбцов базы данных?
Я хотел бы изменить параметры сортировки всех столбцов всех таблиц в моей базе данных. В другом посте в переполнении стека я нашел этот скрипт: ( пост)
SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General__CI_AI ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
GO
Однако, когда я вижу параметры сортировки столбцов, я вижу, что параметры сортировки - это старые параметры сортировки.
Фактическое сопоставление - AS, поэтому у меня могут быть "ANIMAL" и "ÁNIMAL". Когда я выполняю скрипт, я не получаю никакой ошибки. Я думаю, что я получил бы ошибку, потому что новое сопоставление - ИИ. Так что это заставляет меня думать, что сценарий ничего не делает.
Почему я могу изменить параметры сортировки всех столбцов всех моих таблиц в базе данных?
Благодарю.
10 ответов
Решение
Попробуй это -
Запрос:
DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Выход:
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------
[dbo].[CategoryType] -> CategoryTypeCD ALTER TABLE [dbo].[CategoryType] ALTER COLUMN [CategoryTypeCD] VARCHAR(20) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Employee] -> TabelNumber ALTER TABLE [dbo].[Employee] ALTER COLUMN [TabelNumber] VARCHAR(12) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[Supplement] -> WorkFactorCD ALTER TABLE [dbo].[Supplement] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> WorkFactorCD ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> Condition ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [Condition] NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkFactor] -> WorkFactorCD ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkFactor] -> Name ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [Name] NVARCHAR(200) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkOut] -> WorkShiftCD ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [WorkShiftCD] NVARCHAR(40) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> AbsenceCode ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [AbsenceCode] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> PaymentType ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [PaymentType] CHAR(4) COLLATE Cyrillic_General_CI_AS NULL
Я собрал приведенный ниже скрипт из различных источников, которые удаляют зависимости, обновляют параметры сортировки, а затем воссоздают зависимые объекты. Это решает проблему обновления сортировки столбцов, которые имеют зависимости (индексы, ограничения внешнего ключа и т. Д.)
/*******************************************************************************
*
* Created 2017-06-16 By Philip C
*
* 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
*
*********************************************************************************/
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 !='sysname'
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(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 !='sysname' 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;
Я публикую правку в ответе выше, которая касается длины символов и varchar, так как мое предыдущее редактирование выглядит так, как будто оно отклонено.
DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Хотя ни один из других подходов не помог мне, эта статья Дугласа П. Кастильо дала мне полное решение. Я делюсь этим на случай, если это кому-нибудь поможет.
- Создайте резервные копии всех баз данных.
- Остановите службы SQL Server на сервере.
- Откройте командную строку и перейдите в каталог Binn, например,
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
- Выполните команду ниже, чтобы применить новое сопоставление.
sqlservr -m -T4022 -T3659 -q "[Введите имя сопоставления]"
- Запустите службу SQL Server снова.
Вот и все.
Спасибо всем за предложение, оно мне очень помогло при переносе моей базы данных с SQL Server 2008 R2 на 2014
ПРИМЕЧАНИЕ. Некоторые из приведенных запросов имеют недопустимый VARCHAR(-), я изменил его на VARCHAR(MAX), и он работает нормально.
Шаги, чтобы изменить параметры сортировки:
- Запустите вышеуказанный запрос и получите запрос Alter table
- Удалить все индексы таблиц и табличных функций
- Запустите сгенерированный запрос шага 1
- Создание индексов таблиц и табличных функций.
Для тех, кто ищет это решение и использует PHPMyAdmin, есть опция внизу вкладки "Операции с базами данных" (сначала нажмите на базу данных, затем на вкладку "операции" на верхней панели инструментов); прокрутите вниз до поля сортировки, выберите параметры сортировки, отметьте два флажка (один из них появляется, когда отмечен первый):
- Изменить все таблицы сопоставлений
- Изменить все таблицы столбцов сопоставления
Похоже, что в основном запустить скрипт, как те, которые опубликованы консоль показывает последнюю выполненную команду:
ALTER TABLE `DB_NAME`.`LAST_TABLE_IN_DB`DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci
Перед выполнением этой операции, пожалуйста, сначала прочтите эту статью. https://www.mssqltips.com/sqlservertip/3221/how-to-change-the-collation-of-a-sql-server-column/
Прочитав вышеуказанную статью и убедитесь, что вы удалили ниже ссылки из вашей базы данных.
- Вычисляемый столбец
- Статистика распределения индекса, генерируемая автоматически или оператором CREATE STATISTICS
- Ограничение CHECK
- ИНОСТРАННОЕ КЛЮЧЕВОЕ ограничение
Нажмите на приведенный ниже скрипт в вашей базе данных, после чего не забудьте изменить также сортировку базы данных.
DECLARE @collate nvarchar(100);
DECLARE @schema_name nvarchar(255);
DECLARE @table_name nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @is_nullable bit;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
SET @collate = 'SQL_Latin1_General_CP1_CI_AS';
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) schemaname,
t.name table_name
, c.name column_name
, s.Name data_type
, c.max_length
, c.is_nullable
, c.column_id
FROM sys.tables t INNER JOIN
sys.columns c ON c.object_id=t.object_id INNER JOIN
sys.types s ON s.user_type_id=c.user_type_id
WHERE
(c.collation_name LIKE 'SQL_Latin1_General_CP1254_CI_AS' OR c.collation_name LIKE 'Turkish_CI_AS') AND
t.type like 'U'
AND t.name not like 'spt%'
AND t.name not like 'MSrep%'
AND c.collation_name is NOT NULL
order by schemaname
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @schema_name,@table_name, @column_name, @data_type, @max_length,@is_nullable, @column_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @nullable nvarchar(255),
@length varchar(10);
IF @is_nullable = 0
BEGIN
SET @nullable = ' NOT NULL';
END
ELSE
BEGIN
SET @nullable = ' NULL';
END
IF @data_type IN ('nchar', 'nvarchar') AND @max_length != -1
BEGIN
SET @length= CAST(@max_length / 2 AS VARCHAR(10));
END
ELSE IF @data_type IN ('char', 'varchar') AND @max_length != -1
BEGIN
SET @length= CAST(@max_length AS VARCHAR(10));
END
ELSE IF @data_type IN ('nchar', 'nvarchar', 'char', 'varchar') AND @max_length = -1
BEGIN
SET @length= 'MAX';
END
ELSE
BEGIN
SET @length= CAST(@max_length AS VARCHAR(10));
END
BEGIN TRY
IF @schema_name <> 'dbo'
BEGIN
SET @sql = 'ALTER TABLE ['+ @schema_name +'].[' + @table_name + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @length + ') COLLATE ' + @collate + ''+ @nullable+' '
PRINT @sql
EXEC sp_executesql @sql
END
END TRY
BEGIN CATCH
PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
PRINT @sql
END CATCH
FETCH NEXT FROM local_change_cursor
INTO @schema_name,@table_name, @column_name, @data_type, @max_length,@is_nullable, @column_id
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
END
GO
Первая часть
Примечание. Это не мое решение, это сборник комментариев, которые сработали для меня с этой страницы https://sqlserverfast.com/blog/hugo/2019/01/collation-change-script/.
/*******************************************************************************
*
* Created 2017-06-16 By Philip C
*
* 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
*
*
* Modified 2019-01-11 by Hugo Kornelis
*
* Removed double checking of recreated constraints
* Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
* Fixed a bug caused by unique constraints overlapping the primary key constraint
* Fixed a bug in detection of foreign key constraints to drop and recreate
* Fixed bugs with recreating multi-column UNIQUE constraints
*
* Modified 2019-08-12 by Walter Charrière
*
* Add drop and create default constraints
* Fixed bug with text columns adding length
* Add Where calusule for filtered indexes
*
* Modified 2019-12-19 by Damon Clark
*
* Added fix from Erland for doubling length of nchar columns
* Added quoting of column names in create index statements (for reserved words)
*
* Modified 2022-11-14 by Fernan2
* Added Darren Rhymer check constraint drop and create
*
************************************************************************************/
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
GO
-- Declare variables
DECLARE @SchemaName sysname,
@TableName sysname,
@IndexName sysname,
@ColumnName sysname,
@index_id int,
@is_unique nvarchar(100),
@IndexTypeDesc nvarchar(100),
@FileGroupName sysname,
@is_disabled nvarchar(100),
@IndexOptions nvarchar(MAX),
@IndexColumnId int,
@IsDescendingKey int,
@IsIncludedColumn int,
@TSQLScripCreationIndex nvarchar(MAX),
@TSQLScripDisableIndex nvarchar(MAX),
@object_id int,
@has_key_constraint int,
@has_index int,
@has_foreign_key int,
@has_stats int,
@stats_id int,
@FK_objectid int,
@FK_name sysname,
@UQ_objectid int,
@UQ_name sysname,
@UQ_index_id int,
@has_unique_constraint int,
@has_default_constraint int,
@has_check_constraint int,
@DatabaseCollation sysname,
@IndexColumns nvarchar(MAX),
@IncludedColumns nvarchar(MAX),
@filter_definition nvarchar(MAX);
-- Temporary table for generated script
CREATE TABLE #tempscriptstore
(ScriptType varchar(20),
script nvarchar(MAX));
-- Get dataabse default collation
SELECT @DatabaseCollation = collation_name
FROM sys.databases
WHERE database_id = DB_ID();
/************************************************************************************************************************************
* Iterate over all the tables that have at least one colmun where collation doesn't match the database default. *
* Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics. *
* (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero) *
************************************************************************************************************************************/
DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
SELECT t.object_id,
OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
OBJECT_NAME(t.object_id) AS tablename,
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,
COUNT(dc.object_id) AS has_default_constraint,
count(cc.object_id) as has_check_constraint
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON c.object_id = t.object_id
AND c.collation_name <> @DatabaseCollation -- Table needs to have columns with “wrong” collation
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname' -- Exclusion retained from Philip C's original script
LEFT JOIN sys.index_columns AS ic -- Find indexes on any of the affected columns
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT JOIN sys.key_constraints AS kc -- Find primary key constraints related to an affected index
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 AS uq -- Find unique constraints related to an affected index
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 AS fk -- Find foreign key constraints on any of the affected columns
ON fk.parent_object_id = c.object_id
AND fk.parent_column_id = c.column_id
LEFT JOIN sys.stats_columns AS st -- Find statistics on any of the affected columns
ON st.object_id = c.object_id
AND st.column_id = c.column_id
AND st.stats_column_id <> 1 -- Retained from Philip C's original script, no idea why this is in the query
LEFT JOIN sys.default_constraints AS dc -- Find default constraints on any of the affected columns
ON dc.object_id = c.default_object_id
AND dc.parent_column_id = c.column_id
LEFT JOIN sys.check_constraints AS cc -- Find check_constraints on any of the affected columns
ON cc.parent_object_id = c.object_id
AND cc.parent_column_id = c.column_id
WHERE t.is_ms_shipped = 0 -- Exclude Microsoft-shipped tables
GROUP BY t.object_id
--order by 3
OPEN collationfix;
FETCH NEXT FROM collationfix
INTO @object_id,
@SchemaName,
@TableName,
@has_key_constraint,
@has_index,
@has_foreign_key,
@has_stats,
@has_unique_constraint,
@has_default_constraint,
@has_check_constraint;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
/************************************************************************************************************************************
* Generates the code to update the colation for all affected columns in the table *
************************************************************************************************************************************/
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'AlterCollation',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
+ QUOTENAME(c.name) + ' '
+ CASE WHEN ty.name = N'ntext' or ty.name = N'text'
THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
THEN N'MAX'
ELSE CASE WHEN ty.name IN (N'nvarchar', N'nchar')
THEN CAST(c.max_length / 2 AS nvarchar(20))
ELSE CAST(c.max_length AS nvarchar(20))
END
END + N') COLLATE ' + @DatabaseCollation
END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
FROM sys.columns AS c
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE c.object_id = @object_id
AND c.collation_name <> @DatabaseCollation;
/************************************************************************************************************************************
* If the table has affected indexes, this creates the drop and recreate index scripts *
************************************************************************************************************************************/
IF @has_index > 0
BEGIN;
-- Process indexes on affected columns, one by one
DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
SELECT ix.index_id,
ix.name,
CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
ix.type_desc,
ix.filter_definition,
CASE WHEN ix.is_padded = 1
THEN N'PAD_INDEX = ON, '
ELSE N'PAD_INDEX = OFF, '
END + CASE WHEN ix.allow_page_locks = 1
THEN N'ALLOW_PAGE_LOCKS = ON, '
ELSE N'ALLOW_PAGE_LOCKS = OFF, '
END + CASE WHEN ix.allow_row_locks = 1
THEN N'ALLOW_ROW_LOCKS = ON, '
ELSE N'ALLOW_ROW_LOCKS = OFF, '
END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
THEN N'STATISTICS_NORECOMPUTE = ON, '
ELSE N'STATISTICS_NORECOMPUTE = OFF, '
END + CASE WHEN ix.ignore_dup_key = 1
THEN N'IGNORE_DUP_KEY = ON, '
ELSE N'IGNORE_DUP_KEY = OFF, '
END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
+ CASE WHEN ix.fill_factor = 0
THEN CAST(100 AS nvarchar(3))
ELSE CAST(ix.fill_factor AS nvarchar(3))
END AS IndexOptions,
ix.is_disabled,
FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
FROM sys.indexes AS ix
WHERE ix.object_id = @object_id
AND ix.type <> 0 -- Exclude heaps
AND ix.is_primary_key = 0 -- Exclude primary key constraints (handled separately)
AND ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
AND EXISTS (SELECT * -- Has to constrain at least one column with wrong collation
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
AND c.collation_name <> @DatabaseCollation
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE ic.index_id = ix.index_id
AND ic.object_id = ix.object_id);
OPEN CursorIndex;
FETCH NEXT FROM CursorIndex
INTO @index_id,
@IndexName,
@is_unique,
@IndexTypeDesc,
@filter_definition,
@IndexOptions,
@is_disabled,
@FileGroupName;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
-- For each index, use a nested cursor to build lists of indexed and inclued columns
SET @IndexColumns = N'';
SET @IncludedColumns = N'';
DECLARE CursorIndexColumn CURSOR FOR
SELECT c.name,
ic.is_descending_key,
ic.is_included_column
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE ic.object_id = @object_id
AND ic.index_id = @index_id
ORDER BY ic.index_column_id;
OPEN CursorIndexColumn;
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName,
@IsDescendingKey,
@IsIncludedColumn;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
IF @IsIncludedColumn = 0
SET @IndexColumns += QUOTENAME(@ColumnName)
+ CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
ELSE
SET @IncludedColumns += QUOTENAME(@ColumnName) + N', ';
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName,
@IsDescendingKey,
@IsIncludedColumn;
END;
CLOSE CursorIndexColumn;
DEALLOCATE CursorIndexColumn;
-- Remove trailing comma
SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
IF @IncludedColumns <> N''
SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
-- Generate DROP INDEX statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DropIndex',
N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
+ N';';
-- Generate CREATE INDEX statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'CreateIndex',
N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
+ QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
+ CASE WHEN @IncludedColumns <> N''
THEN N'INCLUDE (' + @IncludedColumns + N')'
ELSE N''
END
+ CASE WHEN @filter_definition <> ''
THEN N' WHERE ' + @filter_definition ELSE ''
END
+ N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
-- Generate script to (re)disable disabled index after creating it
IF @is_disabled = 1
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DisableIndex',
N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
+ QUOTENAME(@TableName) + N' DISABLE;';
FETCH NEXT FROM CursorIndex
INTO @index_id,
@IndexName,
@is_unique,
@IndexTypeDesc,
@filter_definition,
@IndexOptions,
@is_disabled,
@FileGroupName;
END;
CLOSE CursorIndex;
DEALLOCATE CursorIndex;
END;
/************************************************************************************************************************************
* If the table has an affected 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 @has_key_constraint > 0
BEGIN;
-- Find columns and other metadata for primary key
SELECT kc.object_id AS constid,
kc.name AS constraint_name, -- PK name
QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
ic.key_ordinal,
i.name AS index_name,
i.type_desc AS index_type,
QUOTENAME(fg.name) AS filegroup_name,
N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
+ CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
+ CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
+ CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
INTO #temp_pk
FROM sys.key_constraints AS kc
INNER JOIN sys.indexes AS i
ON i.object_id = kc.parent_object_id
AND i.is_primary_key = 1
INNER JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
INNER JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE kc.type = 'PK'
AND kc.parent_object_id = @object_id;
-- Generate DROP CONSTRAINT statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One DROP statement, even if there are multiple columns
'DropPrimaryKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
+ QUOTENAME(constraint_name) + N';'
FROM #temp_pk;
-- Generate CREATE CONSTRAINT statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One CREATE statement, even if there are multiple columns
'AddPrimaryKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
+ QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
+ STUFF((SELECT N', ' + pk_col -- This generates a comma-separated list of the columns, in order
FROM #temp_pk
ORDER BY key_ordinal
FOR XML PATH('')),
1,
2,
N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
FROM #temp_pk AS p;
DROP TABLE #temp_pk;
END;
Часть вторая
Примечание. Это не мое решение, это комментарии к компиляции, которые сработали для меня по этому URL-адресу https://sqlserverfast.com/blog/hugo/2019/01/collation-change-script/.
/************************************************************************************************************************************
* If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script *
* this has been taken and adapted from a script found online cretaed by Jayakumaur R *
************************************************************************************************************************************/
IF @has_foreign_key > 0
BEGIN;
-- Process foreign key constraints on affected columns, one by one
DECLARE foreignkeycursor CURSOR FOR
SELECT fk.object_id,
fk.name
FROM sys.foreign_keys AS fk
WHERE fk.parent_object_id = @object_id
AND EXISTS (SELECT * -- Has to constrain at least one column with wrong collation
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS c
ON c.object_id = fkc.parent_object_id
AND c.column_id = fkc.parent_column_id
AND c.collation_name <> @DatabaseCollation
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE fkc.parent_object_id = fk.parent_object_id
AND fkc.constraint_object_id = fk.object_id);
OPEN foreignkeycursor;
FETCH NEXT FROM foreignkeycursor
INTO @FK_objectid,
@FK_name;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
-- Find columns and other metadata for foreign key
SELECT fk.object_id AS constid,
fk.name AS constraint_name, -- FK name
fkc.constraint_column_id AS column_ordinal,
QUOTENAME(c1.name) AS key_col,
QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
+ QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
QUOTENAME(c2.name) AS ref_col,
CASE WHEN fk.delete_referential_action <> 0 -- No action
THEN N' ON DELETE ' + fk.delete_referential_action_desc
ELSE N''
END AS delete_cascade,
CASE WHEN fk.update_referential_action <> 0 -- No action
THEN N' ON UPDATE ' + fk.update_referential_action_desc
ELSE N''
END AS update_cascade,
fk.is_not_trusted,
fk.is_disabled
INTO #temp_fk
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_object_id = fk.parent_object_id
AND fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns AS c1
ON c1.object_id = fkc.parent_object_id
AND c1.column_id = fkc.parent_column_id
INNER JOIN sys.columns AS c2
ON c2.object_id = fkc.referenced_object_id
AND c2.column_id = fkc.referenced_column_id
WHERE fk.object_id = @FK_objectid;
-- Generate DROP CONSTRAINT statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DropForeignKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
+ QUOTENAME(@FK_name) + N';';
-- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One CREATE statement, even if there are multiple columns
'AddForeignKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
+ N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
+ STUFF(
(SELECT N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
FROM #temp_fk
ORDER BY column_ordinal
FOR XML PATH('')),
1,
2,
N'') + N') REFERENCES ' + ref_table + N'('
+ STUFF(
(SELECT N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
FROM #temp_fk
ORDER BY column_ordinal
FOR XML PATH('')),
1,
2,
N'') + N')' + delete_cascade + update_cascade + ';'
FROM #temp_fk;
-- If constraint was disabled, add code to disable it again
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One CREATE statement, even if there are multiple columns
'DisableForeignKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
+ QUOTENAME(constraint_name) + N';'
FROM #temp_fk
WHERE is_disabled = 1;
-- If constraint was trusted, add code to force re-check so it's trudsted again
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One CREATE statement, even if there are multiple columns
'CheckForeignKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
+ N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
FROM #temp_fk
WHERE is_not_trusted = 0;
DROP TABLE #temp_fk;
FETCH NEXT FROM foreignkeycursor
INTO @FK_objectid,
@FK_name;
END;
CLOSE foreignkeycursor;
DEALLOCATE foreignkeycursor;
END;
/*comment code
-- DROP and CREATE statistics for manual statistics has been disabled for now
/************************************************************************************************************************************
* If the column has statistics that aren't part of an index this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @has_stats > 0
AND @has_index = 0
BEGIN
DECLARE stats_cursor CURSOR FOR
SELECT sc.stats_id
FROM sys.stats_columns AS sc
JOIN sys.stats AS s
ON s.object_id = sc.object_id
AND s.stats_id = sc.stats_id
AND s.user_created = 1
WHERE sc.object_id = @object_id
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 AS s
WHERE s.object_id = @object_id
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 AS sc
JOIN sys.stats AS s
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
JOIN sys.columns AS c
ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE sc.object_id = @object_id
AND sc.stats_id = @stats_id;
WITH
cte AS
(SELECT DISTINCT ts.st_table,
ts.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 AS 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 table has unique constraints on affected columns, this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @has_unique_constraint > 0
BEGIN;
-- Process unique constraints on affected columns, one by one
DECLARE uniquecursor CURSOR FOR
SELECT kc.object_id,
kc.name,
kc.unique_index_id
FROM sys.key_constraints AS kc
WHERE kc.parent_object_id = @object_id
AND kc.type = 'UQ'
AND EXISTS (SELECT * -- Has to constrain at least one column with wrong collation
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
AND c.collation_name <> @DatabaseCollation
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE ic.object_id = kc.parent_object_id
AND ic.index_id = kc.unique_index_id);
OPEN uniquecursor;
FETCH NEXT FROM uniquecursor
INTO @UQ_objectid,
@UQ_name,
@UQ_index_id;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
-- Find columns and other metadata for unique constraint
SELECT QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS uq_col,
ic.key_ordinal,
i.name AS index_name,
i.type_desc AS index_type,
QUOTENAME(fg.name) AS filegroup_name,
N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
+ N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
+ N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
+ ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
INTO #temp_uq
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
INNER JOIN sys.filegroups AS fg
ON fg.data_space_id = i.data_space_id
WHERE i.object_id = @object_id
AND i.index_id = @UQ_index_id
AND i.is_unique_constraint = 1;
-- Generate DROP CONSTRAINT statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DropUniqueKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
+ QUOTENAME(@UQ_name) + N';';
-- Generate CREATE CONSTRAINT statement
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT TOP (1) -- One CREATE statement, even if there are multiple columns
'AddUniqueKey',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
+ QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
+ STUFF((SELECT N', ' + uq_col -- This generates a comma-separated list of the columns, in order
FROM #temp_uq
ORDER BY key_ordinal
FOR XML PATH('')),
1,
2,
N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
FROM #temp_uq;
DROP TABLE #temp_uq;
FETCH NEXT FROM uniquecursor
INTO @UQ_objectid,
@UQ_name,
@UQ_index_id;
END;
CLOSE uniquecursor;
DEALLOCATE uniquecursor;
END;
/************************************************************************************************************************************
* If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table *
************************************************************************************************************************************/
IF @has_default_constraint > 0
BEGIN
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DropDefault',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
+ QUOTENAME(dc.name) + ' '
FROM sys.columns AS c
INNER JOIN sys.default_constraints dc
ON dc.object_id = c.default_object_id
AND dc.parent_column_id = c.column_id
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE c.object_id = @object_id
AND c.collation_name <> @DatabaseCollation;
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'CreateDefault',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
+ dc.definition + ' FOR ' + QUOTENAME(c.name)
FROM sys.columns AS c
INNER JOIN sys.default_constraints dc on dc.object_id = c.default_object_id
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE c.object_id = @object_id
AND c.collation_name <> @DatabaseCollation;
End
/************************************************************************************************************************************
* If the table has check constraints on affected columns, this creates the drop and recreate scripts in the table *
************************************************************************************************************************************/
IF @has_check_constraint > 0
BEGIN
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'DropCheck',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
+ QUOTENAME(cc.name) + ' '
FROM sys.columns AS c
INNER JOIN sys.check_constraints cc
ON c.object_id = cc.parent_object_id
AND cc.parent_column_id = c.column_id
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE c.object_id = @object_id
AND c.collation_name <> @DatabaseCollation;
INSERT INTO #tempscriptstore (ScriptType,
script)
SELECT 'CreateCheck',
N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WITH CHECK ADD CONSTRAINT '
+ QUOTENAME(cc.name) + ' CHECK ' + cc.definition
FROM sys.columns AS c
INNER JOIN sys.check_constraints cc
ON cc.parent_object_id = c.object_id
AND cc.parent_column_id = c.column_id
INNER JOIN sys.types AS ty
ON ty.system_type_id = c.system_type_id
AND ty.name <> N'sysname'
WHERE c.object_id = @object_id
AND c.collation_name <> @DatabaseCollation
group by cc.name, cc.definition;
end
FETCH NEXT FROM collationfix
INTO @object_id,
@SchemaName,
@TableName,
@has_key_constraint,
@has_index,
@has_foreign_key,
@has_stats,
@has_unique_constraint,
@has_default_constraint,
@has_check_constraint;
END;
CLOSE collationfix;
DEALLOCATE collationfix;
/************************************************************************************************************************************
* Returns all the created scripts in the correct order for running *
* Also add GO in between some sections so the entire script can execute at once *
************************************************************************************************************************************/
DECLARE @oddnum INT = 1;
WITH ScriptInput
AS (SELECT script,
CASE WHEN ScriptType = 'DropDefault'
THEN 1
WHEN ScriptType = 'DropForeignKey'
THEN 3
WHEN ScriptType = 'DropIndex'
THEN 5
WHEN ScriptType = 'DropUniqueKey'
THEN 7
WHEN ScriptType = 'DropPrimaryKey'
THEN 9
WHEN ScriptType = 'DropStatistics'
THEN 11
WHEN ScriptType = 'DropCheck'
THEN 13
WHEN ScriptType = 'AlterCollation'
THEN 15
WHEN ScriptType = 'AddPrimaryKey'
THEN 17
WHEN ScriptType = 'AddUniqueKey'
THEN 19
WHEN ScriptType = 'CreateIndex'
THEN 21
WHEN ScriptType = 'AddStatistics'
THEN 23
WHEN ScriptType = 'AddForeignKey'
THEN 25
WHEN ScriptType = 'DisableForeignKey'
THEN 27
WHEN ScriptType = 'CheckForeignKey'
THEN 29
WHEN ScriptType = 'DisableIndex'
THEN 31
WHEN ScriptType = 'CreateDefault'
THEN 33
WHEN ScriptType = 'CreateCheck'
THEN 35
ELSE 99
END AS SortOrder
FROM #tempscriptstore
UNION ALL
SELECT CASE WHEN n.num > 0
THEN 'GO' + CHAR(13)+CHAR(10) + descrip
ELSE descrip end,
n.num
FROM (VALUES (0, '--- Drop Default'),
(2, '--- Drop Foreign Key'),
(4, '--- Drop Index'),
(6, '--- Drop Unique Key'),
(8, '--- Drop Primary Key'),
--(10, '--- Drop Statistics'),
(12, '--- Drop Check '),
(14, '--- Alter Collation '),
(16, '--- Add Primary Key '),
(18, '--- Add Unique Key '),
(20, '--- Create Index '),
--(22, '--- Add Statistics '),
(24, '--- Add Foreign Key '),
(26, '--- Disable Foreign Key '),
(28, '--- Check Foreign Key '),
(30, '--- Disable Index '),
(32, '--- Create Default '),
(34, '--- Create Check '),
(36, '--- End of script')
) AS n (num, descrip) )
SELECT ScriptInput.script
FROM ScriptInput
ORDER BY ScriptInput.SortOrder;
DROP TABLE #tempscriptstore;
Возможно, проще использовать PowerShell и SMO, например:
$db = Get-DbaDatabase -SqlInstance MyServer -Database MyDb
$db.Tables | % {
$t = $_
$t | % {
If ($_.Collation -eq 'SQL_Latin1_General_CP1_CI_AS') {
$_.Collation = 'Latin1_General_CI_AS'
}
}
}