/*--------------------------------------------------------------------- $Header: /WWW/Collations/AlterDatabaseCollate.sql 1 24-01-14 18:29 Sommar $ This script serves as an aide to perform the first and last steps of changing the collation of a database. The script identifies objects that directly prevent ALTER DATABASE COLLATE from running and thus must be dropped and later recreated. The script also identifies objects that in their turn prevent the first set of objects to be dropped. Beware that the script does not consider all feautures in SQL Server, so there may still be manual work to do. The script creaets a schema ChangeCollation, and in this schema it creates a table AffectedObjects. For all objects, there is one column drop_cmd to drop the object and one column create_cmd to recreate the object. The script also creates two simple stored procedure to run these commands: RunDropCmds and RunCreateCmds. The script finds the objects that block ALTER DATABASE COLATES and writes them to the table. "Object" here is of: * CHECK constraints. * Computed columns. * Schemabound views and modules. * Modules that for some other reason depend on the databsae collation. * Filtered indexes, and indexes on computed columns (save for constraint indexes) and indexes on schemabound views. * Filtered statistics, and user-defined statistics on computed columns and views. * Views and table-valued functions with string columns and which are not blocking ALTER DATABASE COLLATE, but which needs to be refreshed with sp_refreshsqlmodule after the collation change. They have a blank drop_cmd. There is an accompanying script ChangeColumnCollation.sql, http://www.sommarskog.se/Collations/ChangeColumnCollaton.sql.txt, which you should use to change the database collation. Thus, this is the run order: EXEC ChangeCollation.RunDropCmds ALTER DATABASE COLLATE Run ChangeColumnCollation.sql and its output. EXEC ChangeCollation.RunCreateCmds Beware that none of these two scripts are perfect, and you may have to make manual tweaks to change the order of the commands, or add extra commands for feature the scripts to not consider. Once you have been able to run all this successfully, it is very important that you perform schema compare with a reference copy of the old database. Use one of Schema Compare in Visual Studio or Red Gate's SQLCompare, to make sure that no unexpected difference have been introduced. And, yes, this can easily happen. The script supports SQL 2008 and up, but note that for versions before SQL 2019, you will have to edits the script to remove references to columns in catalog views not present in earlier versions. For SQL 2014, you will also have to make a few adjustments to newer syntax. For more details, see my article "Changing the Server and/or Database Collation in SQL Server", http://www.sommarskog.se/collation-change.html. Copyright © Erland Sommarskog 2024. $History: AlterDatabaseCollate.sql $ * * ***************** Version 1 ***************** * User: Sommar Date: 24-01-14 Time: 18:29 * Created in $/WWW/Collations * * ***************** Version 1 ***************** * User: Sommar Date: 24-01-14 Time: 18:28 * Created in $/WWW ---------------------------------------------------------------------*/ SET XACT_ABORT ON SET NOCOUNT ON go IF schema_id('ChangeCollation') IS NULL EXEC('CREATE SCHEMA ChangeCollation') go DROP TABLE IF EXISTS ChangeCollation.AffectedObjects go CREATE TABLE ChangeCollation.AffectedObjects (drop_order int NOT NULL, create_order int NULL, schema_name sysname NOT NULL, object_name sysname NOT NULL, minor_name sysname NOT NULL, reason varchar(30) NOT NULL, drop_cmd nvarchar(MAX) NOT NULL, create_cmd nvarchar(MAX) NOT NULL, CONSTRAINT pk_AffectedObjects PRIMARY KEY (drop_order), CONSTRAINT u_AffectedObjects UNIQUE (schema_name, object_name, minor_name) ) DECLARE @curr_max int = 0 -- CHECK constraints. They always block ALTER DATABASE COLLATE, since they have a dependency -- on the column name, which may no longer match after the change. INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, t.name, ck.name), s.name, t.name, ck.name, 'CHECK CONSTRAINT', 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' DROP CONSTRAINT IF EXISTS ' + quotename(ck.name), 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' ADD ' + CASE ck.is_system_named WHEN 0 THEN 'CONSTRAINT ' + quotename(ck.name) + ' ' WHEN 1 THEN '' END + ' CHECK ' + ck.definition FROM sys.check_constraints ck JOIN sys.tables t ON ck.parent_object_id = t.object_id JOIN sys.schemas s ON s.schema_id = t.schema_id -- Indexes. There are three groups: -- Filtered indexes. Blockers in their own right, since there is a dependency on a oolumn name. -- Indexes on computed columns; Computed columns are almost always blockers. -- Indexes on views. You can only index schemabound views, and they block ALTER DATABASE COLLATE because of binding. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, o.name, i.index_id DESC), s.name, o.name, i.name, CASE WHEN i.has_filter = 1 THEN 'FILTERED INDEX' ELSE 'DEPENDENT INDEX' END, 'DROP INDEX IF EXISTS ' + quotename(i.name) + ' ON ' + quotename(s.name) + '.' + quotename(o.name), 'CREATE ' + CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END + CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END + CASE WHEN i.type = 3 THEN 'XML ' WHEN i.type = 4 THEN 'SPATIAL ' WHEN i.type IN (5, 6) THEN 'COLUMNSTORE ' ELSE '' END + 'INDEX ' + quotename(i.name) + ' ON ' + quotename(s.name) + '.' + quotename(o.name) + '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1, len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ') ' + CASE WHEN len(ic2.incllist.value('.', 'nvarchar(MAX)')) > 0 THEN ' INCLUDE (' + substring(ic2.incllist.value('.', 'nvarchar(MAX)'), 1, len(ic2.incllist.value('.', 'nvarchar(MAX)')) - 1) + ') ' ELSE '' END + CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END + ' WITH (IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + CASE WHEN i.fill_factor <> 0 THEN ', FILLFACTOR = ' + convert(varchar(10), i.fill_factor) ELSE '' END + ', PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN 'ON' ELSE 'OFF' END + ', STATISTICS_NORECOMPUTE = ' + CASE WHEN st.no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ', STATISTICS_INCREMENTAL = ' + CASE WHEN st.is_incremental = 1 THEN 'ON' ELSE 'OFF' END + ', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ', OPTIMIZE_FOR_SEQUENTIAL_KEY = ' + CASE WHEN i.optimize_for_sequential_key = 1 THEN 'ON' ELSE 'OFF' END + ', DATA_COMPRESSION = ' + p.data_compression_desc COLLATE DATABASE_DEFAULT + ') ON ' + quotename(ds.name) + CASE WHEN ds.type = 'PS' THEN (SELECT '(' + quotename(c.name) + ')' FROM sys.index_columns icp JOIN sys.columns c ON icp.object_id = c.object_id AND icp.column_id = c.column_id WHERE icp.object_id = i.object_id AND icp.index_id = i.index_id AND icp.partition_ordinal = 1) ELSE '' END FROM sys.indexes i JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id JOIN sys.objects o ON i.object_id = o.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id AND p.partition_number = 1 LEFT JOIN sys.stats st ON st.object_id = i.object_id AND st.stats_id = i.index_id CROSS APPLY (SELECT quotename(c.name) + CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END + ', ' FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND (i.type > 2 OR ic.key_ordinal > 0) ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE) AS ic(collist) CROSS APPLY (SELECT quotename(c.name) + ', ' FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1 ORDER BY ic.index_column_id FOR XML PATH(''), TYPE) AS ic2(incllist) WHERE i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND (i.has_filter = 1 OR o.type = 'V' OR EXISTS (SELECT * FROM sys.index_columns ic JOIN sys.computed_columns cc ON cc.object_id = ic.object_id AND cc.column_id = ic.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND cc.uses_database_collation = 1)) -- User defined statistics. The same groups as for indexes. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, o.name, st.name), s.name, o.name, st.name, CASE WHEN st.has_filter = 1 THEN 'FILTERED STATS' ELSE 'DEPENDENT STATS' END, 'DROP STATISTICS ' + quotename(s.name) + '.' + quotename(o.name) + '.' + quotename(st.name), 'CREATE STATISTICS ' + quotename(st.name) + ' ON ' + quotename(s.name) + '.' + quotename(o.name) + '(' + substring(sc.collist.value('.', 'nvarchar(MAX)'), 1, len(sc.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' + CASE WHEN st.has_filter = 1 THEN ' WHERE ' + st.filter_definition ELSE '' END + ' WITH INCREMENTAL = ' + CASE WHEN st.is_incremental = 1 THEN 'ON' ELSE 'OFF' END + CASE WHEN st.no_recompute = 1 THEN ', NORECOMPUTE' ELSE '' END FROM sys.stats st JOIN sys.objects o ON o.object_id = st.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id CROSS APPLY (SELECT quotename(c.name) + ',' FROM sys.stats_columns sc JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id WHERE sc.object_id = st.object_id AND sc.stats_id = st.stats_id ORDER BY sc.stats_column_id FOR XML PATH(''), TYPE) AS sc(collist) WHERE st.user_created = 1 AND (st.has_filter = 1 OR o.type = 'V' OR EXISTS (SELECT * FROM sys.stats_columns sc JOIN sys.computed_columns cc ON cc.object_id = sc.object_id AND cc.column_id = sc.column_id WHERE sc.object_id = st.object_id AND sc.stats_id = st.stats_id AND cc.uses_database_collation = 1)) -- Views and table-valued functions that are not dependent on database collation, -- but which has at least one column with the current database collation. We don't -- have to do anything with these to do ALTER DATABASE COLLATE, but we should refresh -- them later, so that the collation is updated in sys.columns. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, o.name), s.name, o.name, '', 'REFRESH ONLY', '', 'EXEC sp_refresh' + CASE WHEN o.type = 'V' THEN 'view' ELSE 'sqlmodule' END + ' ' + nchar(39) + replace(quotename(s.name) + '.' + quotename(o.name), char(39), nchar(39) + nchar(39)) + nchar(39) FROM sys.sql_modules sm JOIN sys.objects o ON o.object_id = sm.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE EXISTS (SELECT * FROM sys.columns c WHERE c.object_id = o.object_id AND c.collation_name = convert(nvarchar(128), databasepropertyex(db_name(), 'Collation'))) AND sm.uses_database_collation = 0 AND o.type IN ('V', 'IL', 'TF', 'FT') -- We will drop views dependent on database collation later, so we need to restore -- any triggers later. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, v.name, tr.name), s.name, v.name, tr.name, 'TRIGGER SCHBOUND VIEW', CASE WHEN sm.definition IS NULL THEN 'DROP ENCRYPTED!!! ' ELSE '-- DROP ' END + ' TRIGGER IF EXISTS + ' + quotename(s.name) + '.' + quotename(tr.name), isnull(sm.definition, 'ENCRYPTED ' + quotename(s.name) + '.' + quotename(tr.name)) FROM sys.triggers tr JOIN sys.sql_modules sm ON sm.object_id = tr.object_id JOIN sys.views v ON tr.parent_id = v.object_id JOIN sys.schemas s ON s.schema_id = v.schema_id JOIN sys.sql_modules smv ON smv.object_id = v.object_id WHERE smv.uses_database_collation = 1 -- Permissions on modules that we need to drop (see below). Nothing happens on the DROP -- round, only when we created. Since creation is done in reverse order to the script, -- we need to add permissions here. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, o.name), s.name, o.name, 'perms', 'RESTORE PERMISSIONS', '', dp.permsql.value('.', 'nvarchar(MAX)') FROM sys.sql_modules sm JOIN sys.objects o ON o.object_id = sm.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id CROSS APPLY (SELECT dp.state_desc + ' ' + dp.permission_name + ' ON ' + quotename(s.name) + '.' + quotename(o.name) + ' TO ' + quotename(user_name(dp.grantee_principal_id)) + nchar(13) + nchar(10) FROM sys.database_permissions dp WHERE dp.class = 1 AND dp.major_id = sm.object_id FOR XML PATH(''), TYPE) AS dp(permsql) WHERE sm.uses_database_collation = 1 AND dp.permsql IS NOT NULL -- Modules using the schema collation. These could be schemabound objects (because SCHEMABINDING -- is a promise that all names in the module exist), but it can also be table valued functions -- with a string column in the return table without a specific COLLATE clause. These modules -- block ALTER DATABASE COLLATE, so they need to be dropped. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, o.name), s.name, o.name, '', 'MODULE USING DB COLLATION', 'DROP ' + CASE WHEN sm.definition IS NULL THEN ' ENCRYPTED!!! ' ELSE '' END + CASE WHEN o.type = 'V' THEN 'VIEW' WHEN o.type = 'P' THEN 'PROCEDURE' WHEN o.type IN ('IL', 'TF', 'FN') THEN 'FUNCTION' WHEN o.type = 'TR' THEN 'TRIGGER' END + ' IF EXISTS ' + quotename(s.name) + '.' + quotename(o.name), isnull(sm.definition, 'ENCRYPTED! ' + quotename(s.name) + '.' + quotename(o.name)) FROM sys.sql_modules sm JOIN sys.objects o ON o.object_id = sm.object_id JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE sm.uses_database_collation = 1 -- Computed columns, almost always depend on the database collation for the name resolution. SELECT @curr_max = isnull(MAX(drop_order), 0) FROM ChangeCollation.AffectedObjects INSERT ChangeCollation.AffectedObjects (drop_order, schema_name, object_name, minor_name, reason, drop_cmd, create_cmd) SELECT @curr_max + 100 * row_number() OVER(ORDER BY s.name, t.name, cc.name), s.name, t.name, cc.name, 'COMPUTED COLUMN', 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' DROP COLUMN IF EXISTS ' + quotename(cc.name), 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + ' ADD ' + quotename(cc.name) + ' AS ' + cc.definition + CASE WHEN is_persisted = 1 THEN ' PERSISTED' + CASE WHEN is_nullable = 0 THEN ' NOT NULL' ELSE '' END ELSE '' END FROM sys.computed_columns cc JOIN sys.tables t ON t.object_id = cc.object_id JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE cc.uses_database_collation = 1 go CREATE OR ALTER PROCEDURE ChangeCollation.RunDropCmds AS DECLARE @cur CURSOR, @drop_cmd nvarchar(MAX) SET @cur = CURSOR STATIC FOR SELECT drop_cmd FROM ChangeCollation.AffectedObjects WHERE len(drop_cmd) > 0 ORDER BY drop_order OPEN @cur WHILE 1 = 1 BEGIN FETCH @cur INTO @drop_cmd IF @@fetch_status <> 0 BREAK PRINT @drop_cmd EXEC(@drop_cmd) END go CREATE OR ALTER PROCEDURE ChangeCollation.RunCreateCmds AS DECLARE @cur CURSOR, @create_cmd nvarchar(MAX) SET @cur = CURSOR STATIC FOR SELECT create_cmd FROM ChangeCollation.AffectedObjects WHERE isnull(create_order, 1) > 0 AND len(create_cmd) > 0 ORDER BY isnull(create_order, drop_order) DESC OPEN @cur WHILE 1 = 1 BEGIN FETCH @cur INTO @create_cmd IF @@fetch_status <> 0 BREAK PRINT @create_cmd EXEC(@create_cmd) END