sql_latin1_general_cp1_ci_as - Cambiar las intercalaciones de todas las columnas de todas las tablas en SQL Server

collate sql server ejemplos

Question

Importé una base de datos con algunos datos para comparar con otra base de datos.

La base de datos de destino tiene una intercalación Latin1_General_CI_AS y la base de datos de origen tiene SQL_Latin1_General_CP1_CI_AS .

Latin1_General_CI_AS la recopilación de la base de datos fuente en general a Latin1_General_CI_AS utilizando SQL Server Management Studio. Pero las tablas y columnas internas permanecen con la antigua intercalación.

Sé que puedo cambiar una columna usando:

ALTER TABLE [table]
ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS

Pero tengo que hacer esto para todas las tablas y todas las columnas dentro.

Antes de que yo sepa, empiece a escribir un procedimiento almacenado que lea todas las tablas y dentro de todas las columnas de tipo varchar y cámbielas en un bucle de cursor de tabla y columna ...

¿Alguien sabe de una manera más fácil o es la única forma de hacerlo con un script que se ejecuta en todas las tablas de un procedimiento?

Answer 1

Así que aquí estoy, una vez más, no satisfecho con la respuesta. Me asignaron la tarea de actualizar JIRA 6.4.x a JIRA Software 7.xy fui a ese problema en particular con la base de datos y la recopilación de columnas.

En SQL Server, si no se eliminan las restricciones, como la clave principal, la clave externa o incluso los índices, la secuencia de comandos proporcionada anteriormente como respuesta no funciona en absoluto. Sin embargo, cambiará aquellos sin esas propiedades. Esto es realmente problemático, porque no quiero eliminar manualmente todas las restricciones y volver a crearlas. Esa operación probablemente termine con errores. Por otro lado, la creación de un script que automatice el cambio podría demorar años.

Así que encontré una forma de hacer la migración simplemente usando SQL Management Studio. Este es el procedimiento:

• Cambie el nombre de la base de datos por otra cosa. Por ejemplo, el mío era "Jira", así que lo renombré como "JiraTemp".
• Crea una nueva base de datos llamada "Jira" y asegúrate de establecer la intercalación correcta. Simplemente seleccione la página "Opciones" y cambie la intercalación.
• Una vez creado, regrese a "JiraTemp", haga clic derecho, "Tareas -> Generar Scripts ...".
  • Seleccione "Script de toda la base de datos y todos los objetos de la base de datos".
  • Seleccione "Guardar en nueva ventana de consulta", luego seleccione "Avanzado"
  • Cambie el valor de "Script for Server Version" para el valor deseado
  • Habilite "Permisos de nivel de objeto de secuencia de comandos", "Propietario de secuencia de comandos" y "Índices de secuencia de comandos de texto"
  • Deje todo lo demás tal como está o personalícelo si lo desea.
• Una vez generado, elimine la sección "CREAR BASE DE DATOS". Reemplaza "JiraTemp" por "Jira".
• Ejecute el script Toda la estructura de la base de datos y los permisos de la base de datos ahora se replican en "Jira".
• Antes de copiar los datos, debemos deshabilitar todas las restricciones. Ejecute el siguiente comando para hacerlo en la base de datos "Jira": EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
• Ahora los datos deben ser transferidos. Para hacerlo, simplemente haga clic derecho en "JiraTemp", luego seleccione "Tareas -> Exportar datos ..."
  • Seleccione como fuente de datos y destino el proveedor OLE DB para SQL Server.
  • La base de datos de origen es "JiraTemp"
  • La base de datos de destino es "Jira"
  • El nombre del servidor es técnicamente el mismo para el origen y el destino (excepto si ha creado la base de datos en otro servidor).
  • Seleccione "Copiar datos de una u otra tabla o vista"
  • Seleccione todas las tablas excepto las vistas. Luego, cuando aún está resaltado, haga clic en "Editar asignaciones". Marca "Habilitar inserción de identidad"
  • Haga clic en Aceptar, Siguiente y luego en Finalizar
• La transferencia de datos puede tomar un tiempo. Una vez finalizado, ejecute el siguiente comando para volver a habilitar todas las restricciones: exec sp_msforeachtable @command1="print ''?''", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Una vez completado, reinicié JIRA y la compilación de mi base de datos estaba en orden. Espero que ayude a mucha gente!

Answer 2

Como no encontré una manera adecuada, escribí un guión para hacerlo y lo estoy compartiendo aquí para quienes lo necesiten. El script se ejecuta en todas las tablas de usuario y recopila las columnas. Si el tipo de columna es cualquier tipo de carácter, intenta convertirlo a la intercalación dada.

Columns tiene que estar libre de índices y restricciones para que esto funcione.

Si alguien todavía tiene una mejor solución para esto, ¡publícala!

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
SET @collate = ''Latin1_General_CI_AS'';
DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1
OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;
IF (@data_type LIKE ''%char%'')
BEGIN TRY
SET @sql = ''ALTER TABLE '' + @table + '' ALTER COLUMN '' + @column_name + '' '' + @data_type + ''('' + CAST(@max_length AS nvarchar(100)) + '') COLLATE '' + @collate
PRINT @sql
EXEC sp_executesql @sql
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 @row_id, @column_name, @data_type, @max_length, @column_id
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
END
CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO

Answer 3

Lo siento tarde para la fiesta, pero aquí está la mía: atienda a la mesa con un esquema y nombres divertidos de columnas y tablas. Sí, tuve algunos de ellos.

SELECT
''ALTER TABLE ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME
+ ''] ALTER COLUMN ['' + COLUMN_NAME + ''] '' + DATA_TYPE
+ ''('' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(100))
+ '') COLLATE '' + ''Latin1_General_CI_AS''
+ CASE WHEN IS_NULLABLE = ''YES'' THEN '' NULL'' ELSE '' NOT NULL'' END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
DATA_TYPE like ''%char''

Answer 4

Para hacer esto, tengo una solución fácil que funciona para mí.

1. Crea una nueva base de datos con la nueva intercalación.
2. Exporte los datos de la base de datos original en modo secuencia de comandos.
3. Importe el contenido a la nueva base de datos utilizando la secuencia de comandos (cambie el nombre de la frase USE a la nueva base de datos).

Sin embargo, debe tener precaución si su base de datos tiene desencadenantes, procedimientos o similares, más que solo datos y tablas.

Answer 5

Problema de longitud fija nvarchar (incluir max), texto incluido y NULL / NOT NULL agregado.

USE [put your database name here];
begin tran
DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
SET @collate = ''Latin1_General_CI_AS'';
DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1
ORDER BY [name]
OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, col.CHARACTER_MAXIMUM_LENGTH
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE ''%char%'' OR t.Name LIKE ''%text%'')
AND c.collation_name <> @collate
ORDER BY c.column_id
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable
WHILE @@FETCH_STATUS = 0
BEGIN
set @max_length_str = @max_length
IF (@max_length = -1) SET @max_length_str = ''max''
IF (@max_length > 4000) SET @max_length_str = ''4000''
BEGIN TRY
SET @sql =
CASE
WHEN @data_type like ''%text%''
THEN ''ALTER TABLE '' + @table + '' ALTER COLUMN ['' + @column_name + ''] '' + @data_type + '' COLLATE '' + @collate + '' '' + CASE WHEN @is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END
ELSE ''ALTER TABLE '' + @table + '' ALTER COLUMN ['' + @column_name + ''] '' + @data_type + ''('' + @max_length_str + '') COLLATE '' + @collate + '' '' + CASE WHEN @is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END
END
--PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT ''ERROR ('' + @table + ''): Some index or constraint rely on the column '' + @column_name + ''. No conversion possible.''
--PRINT @sql
END CATCH
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
END
CLOSE local_table_cursor
DEALLOCATE local_table_cursor
commit tran
GO

Aviso: en caso de que solo necesite cambiar alguna colación específica, use una condición como esta:

WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE ''%char%'' OR t.Name LIKE ''%text%'')
AND c.collation_name = ''collation to change''

por ejemplo, NO : AND c.collation_name <> @collate

En mi caso, tuve una compilación correcta / especificada de algunas columnas y no quería cambiarlas.

Answer 6

Problema de longitud fija nvarchar y agregado NULL / NOT NULL

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
DECLARE @is_Nullable bit;
DECLARE @null nvarchar(25);
SET @collate = ''Latin1_General_CI_AS'';
DECLARE local_table_cursor CURSOR FOR
SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N''IsUserTable'') = 1
OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@max_length = -1) SET @max_length = 4000;
set @null='' NOT NULL''
if (@is_nullable = 1) Set @null='' NULL''
if (@Data_type=''nvarchar'') set @max_length=cast(@max_length/2 as bigint)
IF (@data_type LIKE ''%char%'')
BEGIN TRY
SET @sql = ''ALTER TABLE '' + @table + '' ALTER COLUMN ['' + rtrim(@column_name) + ''] '' + @data_type + ''('' + CAST(@max_length AS nvarchar(100)) + '') COLLATE '' + @collate + @null
PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT ''ERROR: Some index or contraint rely on the column '' + @column_name + ''. No conversion possible.''
PRINT @sql
END CATCH
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_Nullable
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
FETCH NEXT FROM local_table_cursor
INTO @table
END
CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO