Comment puis-je résoudre mon conflit de classement dans une meilleure façon?
J'ai un classement de problème avec ma base de données 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 donner quelques conseils pour optimiser mon code?
OriginalL'auteur Galkin | 2009-05-11
Vous devez vous connecter pour publier un commentaire.
J'ai vu ça où la base de données tempdb a une valeur par défaut différente colation de votre base de données, et j'ai besoin d'ajouter "RASSEMBLER DATABASE_DEFAULT" à mes comparaisons par exemple
devient
OriginalL'auteur Binary Worrier
Binaire Guerrier de la solution est utile pour l'exécution de code avec des classements différents. Par exemple, les tables temporaires et variables temp utiliser la base de données tempdb classement.
Pour modifier le classement, MS se mettre un KO "Comment faire pour transférer une base de données à partir d'un classement à un autre classement dans SQL Server". Il mentionne encore l'aide de DTS, ce qui peut ne pas être possible. Je ne peux pas trouver une mise à jour de l'article.
OriginalL'auteur gbn