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