Comment puis-je résoudre mon conflit de collation d'une meilleure manière?

J'ai un problème de collation avec ma database et j'ai développé ma propre solution.

Solution:

DECLARE @new_collation varchar(128), @conflict_collation varchar(128), @cmd_holder varchar(2000), @cmd_complete varchar(2000), @schema varchar(128), @table_name varchar(128), @constraints_name varchar(128), @column_name varchar(128), @definition varchar(256), @data_type varchar(128), @type varchar(5), @length varchar(4), @nullability varchar(8), @db_name varchar(10) SET @new_collation = 'SQL_Latin1_General_CP1_CI_AS' SET @conflict_collation = 'French_CI_AS' CREATE TABLE #LIST_CONSTRAINT( constraints_name VARCHAR(128), table_name VARCHAR(128), definition VARCHAR(256), type VARCHAR(10)) INSERT INTO #LIST_CONSTRAINT SELECT c.name AS constraints_name, o.name AS table_name, definition, 'CH' AS type FROM sys.check_constraints c INNER JOIN sysobjects o ON id = parent_object_id INSERT INTO #LIST_CONSTRAINT SELECT i.name AS index_name, o.name AS table_name, c.name AS field_name, 'UQ' AS type FROM sys.indexes i INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id and i.index_id = ic.index_id INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id INNER JOIN sys.objects o ON i.object_id = o.object_id WHERE is_unique_constraint = 1 SET @cmd_holder = 'ALTER TABLE $table_name DROP CONSTRAINT $constraints_name' DECLARE column_cursor CURSOR FOR SELECT constraints_name, table_name FROM #LIST_CONSTRAINT GROUP BY constraints_name, table_name OPEN column_cursor FETCH NEXT FROM column_cursor INTO @constraints_name, @table_name WHILE @@Fetch_Status = 0 BEGIN SELECT @cmd_complete = @cmd_holder, @cmd_complete = Replace(@cmd_complete, '$table_name', '[' + @table_name +']'), @cmd_complete = Replace(@cmd_complete, '$constraints_name', @constraints_name) --PRINT @cmd_complete EXEC(@cmd_complete) FETCH NEXT FROM column_cursor INTO @constraints_name, @table_name END CLOSE column_cursor DEALLOCATE column_cursor SELECT @db_name = DB_NAME() EXEC('ALTER DATABASE ' + @db_name + ' COLLATE ' + @new_collation) SET @cmd_holder = 'ALTER TABLE $schema.$table_name ALTER COLUMN $column_name $data_type($length) COLLATE $new_collation $nullability' DECLARE column_cursor CURSOR FOR SELECT table_schema, table_name, column_name, data_type, CASE WHEN character_maximum_length = -1 THEN 'max' ELSE Convert(varchar(4), character_maximum_length) END As length, CASE WHEN is_nullable = 'YES' THEN 'NULL' ELSE 'NOT NULL' END As nullability FROM information_schema.columns INNER JOIN sysobjects ON name = table_name WHERE collation_name = @conflict_collation AND xtype = 'U' AND table_name NOT IN ('dtproperties', 'Exotics', 'ContractAccountsBalance', 'TechnicalParameters', 'SavingProducts', 'GeneralParameters') OPEN column_cursor FETCH NEXT FROM column_cursor INTO @schema, @table_name, @column_name, @data_type, @length, @nullability WHILE @@Fetch_Status = 0 BEGIN SELECT @cmd_complete = @cmd_holder, @cmd_complete = Replace(@cmd_complete, '$schema', @schema), @cmd_complete = Replace(@cmd_complete, '$table_name', '[' + @table_name +']'), @cmd_complete = Replace(@cmd_complete, '$column_name', '[' + @column_name +']'), @cmd_complete = Replace(@cmd_complete, '$data_type', @data_type), @cmd_complete = Replace(@cmd_complete, '$length', @length), @cmd_complete = Replace(@cmd_complete, '$new_collation', @new_collation), @cmd_complete = Replace(@cmd_complete, '$nullability', @nullability), @cmd_complete = Replace(@cmd_complete, 'text(*)', 'text') --PRINT @cmd_complete EXEC(@cmd_complete) FETCH NEXT FROM column_cursor INTO @schema, @table_name, @column_name, @data_type, @length, @nullability END CLOSE column_cursor DEALLOCATE column_cursor DECLARE @name_constraints VARCHAR(128) DECLARE column_cursor CURSOR FOR SELECT constraints_name, table_name, definition, [type] FROM #LIST_CONSTRAINT OPEN column_cursor FETCH NEXT FROM column_cursor INTO @constraints_name, @table_name, @definition, @type WHILE @@Fetch_Status = 0 BEGIN IF @type = 'CH' SET @cmd_holder = 'ALTER TABLE $table_name WITH NOCHECK ADD CONSTRAINT $constraints_name CHECK NOT FOR REPLICATION $definition ALTER TABLE $table_name CHECK CONSTRAINT $constraints_name' IF @type = 'UQ' BEGIN SET @cmd_holder = 'ALTER TABLE $table_name ADD CONSTRAINT $constraints_name UNIQUE NONCLUSTERED ($definition) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)' SET @definition = ''; SELECT @definition = @definition + definition + ', ' FROM #LIST_CONSTRAINT WHERE constraints_name = @constraints_name SELECT @definition = SUBSTRING(@definition, 1, LEN(@definition) - 1) END SELECT @cmd_complete = @cmd_holder, @cmd_complete = Replace(@cmd_complete, '$table_name', '[' + @table_name +']'), @cmd_complete = Replace(@cmd_complete, '$constraints_name', @constraints_name), @cmd_complete = Replace(@cmd_complete, '$definition', @definition) --PRINT @cmd_complete IF (@name_constraints <> @constraints_name) EXEC(@cmd_complete) SET @name_constraints = @constraints_name FETCH NEXT FROM column_cursor INTO @constraints_name, @table_name, @definition, @type END CLOSE column_cursor DEALLOCATE column_cursor DROP TABLE #LIST_CONSTRAINT 

Quelqu'un at-il une autre solution?

Quelqu'un peut-il offrir des conseils pour optimiser mon code?

J'ai vu ceci où le tempdb a une colatation par défaut différente de votre database, et j'ai dû append "COLLATE DATABASE_DEFAULT" à mes comparaisons par exemple

 Create table #tmp ( mailbox varchar(50) not null ) . . . Select t.mailbox, count(*) from #tmp t inner join processed_email e on t.mailbox = e.mailbox 

devient

 Select t.mailbox, count(*) from #tmp t inner join processed_email e on t.mailbox COLLATE DATABASE_DEFAULT = e.mailbox COLLATE DATABASE_DEFAULT 

La solution de Binary Worrier est utile pour exécuter du code avec différentes classments. Par exemple, les tables temporaires et les variables temporaires utilisent le classment tempdb.

Pour modifier le classment, MS a créé une base de connaissances " Comment transférer une database d'un classment vers un autre classment dans SQL Server ". Il mentionne toujours l'utilisation de DTS, ce qui peut ne pas être possible. Je ne trouve pas d'article de la base de connaissances mis à jour.