/******************************************************************************* * * 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 2023-12-26 by Erland Sommarskog * * Revived the section on user-defined statistics. * Corrected all joins to sys.types to be on user_type_id, not system_type_id, and removed exception for sysname. * Handling string columns with a user-defined type. * Added a variable @FromCollation to only handle columns with this collation, * so you don't blindly change everything to the DB collation. * Added dropping and reload of table types with columns of @ToCollation. This includes * dropping and re-creating modules using these types and restoring permissions on * modules and types. * Several other minor modifications. * * For more details, see the article "Changing the Server and/or Database * Collation in SQL Server", http://www.sommarskog.se/collation-change.html. * *********************************************************************************/ SET NOCOUNT, ANSI_WARNINGS, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL ON GO DROP TABLE IF EXISTS #tempscriptstore DROP TABLE IF EXISTS #temp_pk DROP TABLE IF EXISTS #temp_fk DROP TABLE IF EXISTS #temp_stats DROP TABLE IF EXISTS #temp_uq go DECLARE @FromCollation sysname = 'Greek_CI_AS', -- The collation you want to replace. @ToCollation sysname = convert(nvarchar(128), DATABASEPROPERTYEX(DB_NAME(), 'Collation')) -- The collation you want to switch to, typically the databsae collation. -- 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_is_system_named bit, @UQ_index_id int, @has_unique_constraint int, @has_default_constraint int, @IndexColumns nvarchar(MAX), @IncludedColumns nvarchar(MAX), @filter_definition nvarchar(MAX); -- Temporary table for generated script CREATE TABLE #tempscriptstore (ScriptType varchar(20), script nvarchar(MAX)); /************************************************************************************************************************************ * Iterate over all the tables that have at least one colmun with a collation different from @FromCollation. * * 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 FROM sys.tables AS t INNER JOIN sys.columns AS c ON c.object_id = t.object_id AND c.collation_name = @FromCollation -- Table needs to have columns with "wrong" collation 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 st -- Find user-defined statistics on any of the affected columns JOIN sys.stats_columns AS sc ON sc.object_id = st.object_id AND sc.stats_id = st.stats_id) ON sc.object_id = c.object_id AND sc.column_id = c.column_id AND st.user_created = 1 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 WHERE t.is_ms_shipped = 0 -- Exclude Microsoft-shipped tables GROUP BY t.object_id; 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; 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.user_type_id <> ty.system_type_id THEN QUOTENAME(sch.name) + '.' + QUOTENAME(ty.name) -- User-defined type; COLLATE clause not permitted! ELSE CASE WHEN ty.name IN (N'ntext', N'text') THEN ty.name ELSE ty.name + N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' 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 + N')' END + N' COLLATE ' + @ToCollation END + CASE WHEN c.is_sparse = 1 THEN N' SPARSE' ELSE '' 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.user_type_id = c.user_type_id INNER JOIN sys.schemas sch ON ty.schema_id = sch.schema_id INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE c.object_id = @object_id AND c.collation_name = @FromCollation AND t.temporal_type_desc <> 'HISTORY_TABLE'; -- Columns in history tables are altered through the parent tables. /************************************************************************************************************************************ * 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_desc NOT IN ('HEAP', 'XML', 'SPATIAL', 'CLUSTERED COLUMNSTORE') 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 = @FromCollation 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.key_ordinal; 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.is_system_named, 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 ' + CASE WHEN is_system_named = 0 THEN N' CONSTRAINT ' + QUOTENAME(p.constraint_name) ELSE N'' END + 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; /************************************************************************************************************************************ * 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 = @FromCollation 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; /************************************************************************************************************************************ * 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 JOIN sys.columns c ON c.object_id = sc.object_id AND c.column_id = sc.column_id WHERE sc.object_id = @object_id AND c.collation_name = @FromCollation AND s.user_created = 1; 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.is_system_named, 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 = @FromCollation 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_is_system_named, @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 ' + CASE WHEN @UQ_is_system_named = 0 THEN N' CONSTRAINT ' + QUOTENAME(@UQ_name) ELSE N'' END + 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_is_system_named, @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_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE c.object_id = @object_id AND c.collation_name = @FromCollation; INSERT INTO #tempscriptstore (ScriptType, script) SELECT 'CreateDefault', N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD ' + CASE WHEN dc.is_system_named = 0 THEN ' CONSTRAINT ' + QUOTENAME(dc.name) ELSE '' END + ' 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 AND dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE c.object_id = @object_id AND c.collation_name = @FromCollation; 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; END; CLOSE collationfix; DEALLOCATE collationfix; /************************************************************************************************************************************ * Over to table types with string columns with @FromCollation. There is no way to alter them, but we need to drop and recreate * them. And do that we, also need to drop and re-create modules that uses these types as parameters. ************************************************************************************************************************************/ -- First drop all modules depending on a table type. INSERT INTO #tempscriptstore (ScriptType, script) SELECT 'DropTblTypeProc', 'DROP ' + CASE WHEN sm.definition IS NULL THEN ' ENCRYPTED!!! ' ELSE '' END + CASE o.type WHEN 'P' THEN 'PROCEDURE' ELSE 'FUNCTION' END + ' ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) FROM sys.objects o JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.sql_modules sm ON sm.object_id = o.object_id WHERE EXISTS (SELECT * FROM sys.parameters p JOIN sys.table_types tt ON p.user_type_id = tt.user_type_id JOIN sys.columns c ON tt.type_table_object_id = c.object_id WHERE c.collation_name = @FromCollation AND p.object_id = o.object_id) -- How to re-create them. Don't forget to restore permissions! INSERT INTO #tempscriptstore (ScriptType, script) SELECT 'CreateTblTypeProc', N'GO' + nchar(13) + nchar(10) + isnull(sm.definition, N'ENCRYPTED OBJECT!') + nchar(13) + nchar(10) + N'GO' + isnull((SELECT nchar(13) + nchar(10) + dp.state_desc + N' ' + dp.permission_name + N' ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' TO ' + QUOTENAME(USER_NAME(dp.grantee_principal_id)) FROM sys.database_permissions dp WHERE dp.class = 1 AND dp.major_id = o.object_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), N'') FROM sys.objects o JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.sql_modules sm ON sm.object_id = o.object_id WHERE EXISTS (SELECT * FROM sys.parameters p JOIN sys.table_types tt ON p.user_type_id = tt.user_type_id JOIN sys.columns c ON tt.type_table_object_id = c.object_id WHERE c.collation_name = @FromCollation AND p.object_id = o.object_id) -- Dropping types is easy. INSERT INTO #tempscriptstore (ScriptType, script) SELECT 'DropTableType', 'DROP TYPE ' + QUOTENAME(s.name) + '.' + QUOTENAME(tt.name) FROM sys.table_types tt JOIN sys.schemas s ON tt.schema_id = s.schema_id WHERE EXISTS (SELECT * FROM sys.columns c WHERE c.object_id = tt.type_table_object_id AND c.collation_name = @FromCollation) -- Re-creating them is a lot more work. We run a cursor and take one type at a time. DECLARE @sql nvarchar(MAX) DECLARE tabletype_cur CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR SELECT o.object_id FROM sys.objects o WHERE o.type = 'TT' AND EXISTS (SELECT * FROM sys.columns c WHERE c.object_id = o.object_id AND c.collation_name = @FromCollation) OPEN tabletype_cur WHILE 1 = 1 BEGIN FETCH tabletype_cur INTO @object_id IF @@fetch_status <> 0 BREAK -- The opening CREATE TYPE. SELECT @sql = 'CREATE TYPE ' + QUOTENAME(s.name) + '.' + QUOTENAME(tt.name) + N' AS TABLE (' + char(13) + char(10) FROM sys.table_types tt JOIN sys.schemas s ON s.schema_id = tt.schema_id WHERE tt.type_table_object_id = @object_id -- Add columns. SELECT @sql += ( SELECT QUOTENAME(c.name) + ' ' + CASE WHEN cc.definition IS NOT NULL THEN N'AS ' + cc.definition + CASE WHEN cc.is_persisted = 1 THEN ' PERSISTED ' + CASE WHEN cc.is_nullable = 0 THEN ' NOT NULL' ELSE '' END ELSE '' END ELSE CASE WHEN t.user_type_id <> t.system_type_id THEN QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) WHEN t.name IN (N'nchar', N'nvarchar', N'char', N'varchar') THEN t.name + N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' WHEN t.name LIKE N'n%' THEN CONVERT(nvarchar, c.max_length/2) ELSE CONVERT(nvarchar, c.max_length) END + N')' + N' COLLATE ' + CASE WHEN c.collation_name = @FromCollation THEN @ToCollation ELSE c.collation_name END WHEN t.name IN (N'text', N'ntext') THEN t.name + N' COLLATE ' + CASE WHEN c.collation_name = @FromCollation THEN @ToCollation ELSE c.collation_name END WHEN t.name IN (N'binary', N'varbinary') THEN t.name + N'(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CONVERT(nvarchar, c.max_length) END + N')' WHEN t.name IN (N'decimal', N'numeric') THEN t.name + N'(' + CONVERT(nvarchar, c.precision) + N',' + CONVERT(nvarchar, c.scale) + N')' WHEN t.name IN (N'datetime2', N'datetimeoffset', N'time') THEN t.name + N'(' + CONVERT(nvarchar, c.scale) + N')' ELSE t.name END + ' ' + CASE WHEN c.is_nullable = 0 THEN N'NOT ' ELSE N'' END + N'NULL' + CASE WHEN c.is_identity = 1 THEN N' IDENTITY' ELSE N'' END + CASE WHEN c.is_rowguidcol = 1 THEN N' ROWGUIDCOL' ELSE N'' END + CASE WHEN ck.definition IS NOT NULL THEN N' CHECK ' + ck.definition ELSE N'' END + CASE WHEN dc.definition IS NOT NULL THEN N' DEFAULT ' + dc.definition ELSE N'' END END + N',' + nchar(13) + nchar(10) FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id JOIN sys.schemas s ON s.schema_id = t.schema_id LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id LEFT JOIN sys.default_constraints dc ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id LEFT JOIN sys.check_constraints ck ON ck.parent_object_id = c.object_id AND ck.parent_column_id = c.column_id WHERE c.object_id = @object_id ORDER BY c.column_id FOR XML PATH(''), TYPE).value(N'.', N'nvarchar(MAX)') -- Add any table-level CHECK constraints. SELECT @sql += isnull(( SELECT N'CHECK (' + definition + N'),' + nchar(10) + nchar(13) FROM sys.check_constraints WHERE parent_object_id = @object_id AND parent_column_id = 0 FOR XML PATH(N''), TYPE).value(N'.', N'nvarchar(MAX)'), '') -- And key constraints and indexes. IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = @object_id) BEGIN SELECT @sql += ( SELECT CASE WHEN i.is_primary_key = 1 THEN N'PRIMARY KEY' WHEN i.is_unique_constraint = 1 THEN N'UNIQUE' ELSE N'INDEX ' + QUOTENAME(i.name) END + N' ' + CASE i.index_id WHEN 1 THEN N'CLUSTERED' ELSE N'NONCLUSTERED' END + N' (' + (SELECT QUOTENAME(c.name) + N' ' + CASE ic.is_descending_key WHEN 1 THEN N'DESC' ELSE N'ASC' END + N',' FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id ORDER BY ic.key_ordinal FOR XML PATH(N''), TYPE).value(N'.', 'nvarchar(MAX)') + ')' + CASE WHEN i.ignore_dup_key = 1 THEN N'WITH (IGNORE_DUP_KEY = ON)' ELSE N'' END + N',' + nchar(13) + nchar(10) FROM sys.indexes i WHERE i.object_id = @object_id AND i.index_id <> 0 ORDER BY i.index_id FOR XML PATH(N''), TYPE).value(N'.', N'nvarchar(MAX)') -- Cleanup trailing commas in index lists. SELECT @sql = replace(@sql, N'SC,)', N'SC)') END -- Strip final comma and line break and add closing paren. SELECT @sql = substring(@sql, 1, len(@sql)-3) + N');' -- Add permissions. SELECT @sql += isnull((SELECT nchar(13) + nchar(10) + dp.state_desc + N' ' + dp.permission_name + N' ON TYPE::' + QUOTENAME(s.name) + N'.' + QUOTENAME(tt.name) COLLATE DATABASE_DEFAULT + N' TO ' + QUOTENAME(USER_NAME(dp.grantee_principal_id)) FROM sys.database_permissions dp JOIN sys.table_types tt ON tt.user_type_id = dp.major_id JOIN sys.schemas s ON s.schema_id = tt.schema_id WHERE dp.class = 6 AND tt.type_table_object_id = @object_id FOR XML PATH(''), TYPE).value(N'.', N'nvarchar(MAX)'), N''); SELECT @sql += nchar(13) + nchar(10) + N'GO' INSERT INTO #tempscriptstore (ScriptType, script) VALUES ('CreateTableType', @sql) END DEALLOCATE tabletype_cur /************************************************************************************************************************************ * 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 = 'AlterCollation' THEN 13 WHEN ScriptType = 'AddPrimaryKey' THEN 15 WHEN ScriptType = 'AddUniqueKey' THEN 17 WHEN ScriptType = 'CreateIndex' THEN 19 WHEN ScriptType = 'AddStatistics' THEN 21 WHEN ScriptType = 'AddForeignKey' THEN 23 WHEN ScriptType = 'DisableForeignKey' THEN 25 WHEN ScriptType = 'CheckForeignKey' THEN 27 WHEN ScriptType = 'DisableIndex' THEN 29 WHEN ScriptType = 'CreateDefault' THEN 31 WHEN ScriptType = 'DropTblTypeProc' THEN 101 WHEN ScriptType = 'DropTableType' THEN 103 WHEN ScriptType = 'CreateTableType' THEN 105 WHEN ScriptType = 'CreateTblTypeProc' THEN 107 ELSE 999 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, '---- Alter Collation '), (14, '---- Add Primary Key '), (16, '---- Add Unique Key '), (18, '---- Create Index '), (20, '---- Add Statistics '), (22, '---- Add Foreign Key '), (24, '---- Disable Foreign Key '), (26, '---- Check Foreign Key '), (28, '---- Disable Index '), (30, '---- Create Default '), (100, '-- Drop modules with table type'), (102, '-- Drop table type'), (104, '-- Create table type'), (106, '-- Create modules with table type'), (1000, '---- End of script') ) AS n (num, descrip) ) SELECT ScriptInput.script FROM ScriptInput ORDER BY ScriptInput.SortOrder; DROP TABLE #tempscriptstore;