¡Hola, chicos!
¿Todo en calma?

En este artículo, me gustaría compartir con ustedes un problema que tuve recientemente, en el que varias columnas de varias tablas en una base de datos determinada usaban una intercalación diferente a la del estándar DB, lo que significa que al realizar uniones y condiciones WHERE entre columnas VARCHAR/CHAR/NVARCHAR con diferentes intercalaciones, la base de datos devolvió el siguiente mensaje de error:

No se puede resolver el conflicto de intercalación entre “SQL_Latin1_General_CP1_CI_AI” y “SQL_Latin1_General_CP1_CI_AS” en la operación igual a.

¿Qué es una COLECCIÓN?

La intercalación no es más que la forma de codificar caracteres que utiliza una base de datos para interpretarlos.

Una intercalación es una agrupación de estos caracteres en un orden determinado (cada intercalación tiene un orden diferente), donde “A” es un carácter diferente de “a”, si la intercalación distingue entre mayúsculas y minúsculas (diferenciación de mayúsculas y minúsculas) y “a” es diferente de “á”, si la intercalación distingue acentos (diferenciación de acentos).

COLLATION tiene tres niveles de jerarquía:
– Servidor
– Base de datos
- Columna

Si la base de datos se crea sin especificar qué intercalación se utilizará, se creará con la intercalación del servidor (idioma del sistema operativo). Cuando se crea una tabla sin especificar la clasificación de las columnas de texto (VARCHAR, NVARCHAR, CHAR, etc.), la clasificación de la base de datos se utilizará como clasificación de las tablas.

En SQL Server, el nombre de intercalación sigue el siguiente patrón de nomenclatura:
SQL_IntercalaciónDesignador_CaseSensibilidad_Sensibilidad al acento_KanatypeSensible_AnchoSensibilidad

Ejemplo de intercalación:
SQL_Latín1_General_CP1_CS_HACIA

Dónde:

  • IntercalaciónDesignador: Especifica las reglas de agrupación básicas utilizadas por la agrupación de Windows, donde las reglas de clasificación se basan en el alfabeto o el idioma.
  • Sensibilidad entre mayúsculas y minúsculas: CI especifica que no distingue entre mayúsculas y minúsculas, CS especifica que distingue entre mayúsculas y minúsculas.
  • Sensibilidad al acento: AI especifica que no distingue los acentos, AS especifica que sí lo hace.
  • KanatypeSensible: Omitido especifica que es insensible a los caracteres kana, KS especifica que es insensible a los caracteres kana.
  • AnchoSensibilidad: Omitido especifica que no es sensible al ancho, WS especifica que sí es sensible al ancho.

Si una columna utiliza COLLATION distingue entre mayúsculas y minúsculas (CS), una consulta como SELECT * FROM Table WHERE Column LIKE '%Oracle%' devolverá el registro "Oracle", pero no devolverá el registro "oracle".

Lo mismo sucede con una columna que utiliza un COLLATION sensible al acento (AS). Una consulta como SELECT * FROM Table WHERE Column LIKE '%JOÃO%' devolverá el registro "João", pero no devolverá el registro "Joao".

Para consultar la lista completa de colaciones por región e idioma, accede a este enlace, recordando que el más utilizado en portugués (Brasil) es SQL_Latin1_General_CP1_CI_AI (o SQL_Latin1_General_CP1_CS_AS).

Para saber más sobre qué es Collation, echa un vistazo al artículo. SQL Server: no se puede resolver el conflicto de intercalación entre... en la operación igual a..

¿Cómo identificar la intercalación predeterminada?

Para identificar la clasificación predeterminada de una base de datos, simplemente utilice la siguiente consulta:

USE [dirceuresende]
GO

SELECT collation_name
FROM sys.databases
WHERE database_id = DB_ID()

Resultado:

Para identificar la intercalación predeterminada para su instancia, simplemente use el siguiente comando:

SELECT SERVERPROPERTY('Collation')

Resultado:

Generación de la base para las pruebas.

Para estandarizar los ejemplos y ayudar con la comprensión práctica de este artículo, usaré un script simple para generar datos de ejemplo (pruebas):

------------------------------------------------------------------------
-- Geração dos dados para teste
------------------------------------------------------------------------

CREATE TABLE dbo.Cliente (
    CPF VARCHAR(11) COLLATE Latin1_General_CS_AS NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(50)
)

INSERT INTO dbo.Cliente ( CPF, Nome )
VALUES ( '12345678909', 'Joãozinho' )

CREATE NONCLUSTERED INDEX SK01 ON dbo.Cliente(CPF) INCLUDE(Nome)


CREATE TABLE dbo.Pedido (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Dt_Pedido DATETIME DEFAULT GETDATE(),
    Vl_Pedido MONEY,
    CPF_Cliente VARCHAR(11) COLLATE Latin1_General_CS_AS CONSTRAINT [FK_Cliente] FOREIGN KEY(CPF_Cliente) REFERENCES dbo.Cliente(CPF) NOT NULL
)

INSERT INTO dbo.Pedido ( Vl_Pedido, CPF_Cliente )
VALUES
(
    9.99, -- Vl_Pedido - money
    '12345678909' -- Id_Cliente - int
)

CREATE NONCLUSTERED INDEX SK02 ON dbo.Pedido(Id, Dt_Pedido, CPF_Cliente)

¿Cómo estandarizar la intercalación de todas las columnas?

Si desea estandarizar la intercalación de todas las columnas en una base de datos, para que utilicen una intercalación específica o la predeterminada de la base de datos, desarrollé un script que identificará todas las columnas que no son estándar y ejecutará un comando ALTER TABLE para aplicar el cambio.

Como existen dependencias (restricciones, índices y claves externas), el script eliminará los objetos dependientes identificados, aplicará los cambios y recreará estos objetos. Debido a esto, le recomiendo encarecidamente que realice una buena prueba antes de ejecutar este script en producción. Preferiblemente, primero haga una copia de seguridad de la estructura.

Script para cambiar la intercalación de todas las columnas (y dependencias) que tienen una intercalación diferente a la de la base de datos
Ver código fuente

SET NOCOUNT ON

-------------------------------------------------------------------------
-- Identifica as colunas com collation diferente do padrão do database conectado
-------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
SELECT
    B.[object_id] AS table_object_id,
    B.[name] AS table_name,
    B.[schema_id] AS table_schema_id,
    C.[name] AS [type_name],
    D.[name] AS [schema_name],
    D.[schema_id],
    A.*,
    C.system_type_id AS type_system_type_id,
    C.user_type_id AS type_user_type_id,
    C.collation_name AS type_collation_name,
    E.collation_name AS [database_default_collation]
INTO
    #Colunas
FROM
    sys.columns		            AS A
    JOIN sys.tables	            AS B ON A.[object_id] = B.[object_id]
    JOIN sys.types		    AS C ON A.user_type_id = C.user_type_id
    JOIN sys.schemas	            AS D ON B.[schema_id] = D.[schema_id]
    JOIN sys.databases	            AS E ON E.database_id = DB_ID()
WHERE
    B.is_ms_shipped = 0
    AND A.collation_name <> E.collation_name COLLATE DATABASE_DEFAULT
   

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas
SELECT DISTINCT
    table_object_id,
    table_name,
    table_schema_id,
    [schema_name]
INTO
    #Tabelas
FROM
    #Colunas


-------------------------------------------------------------------------
-- Identifica os índices que utilizam essas colunas
-------------------------------------------------------------------------
    
IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices
SELECT 
    A.*,
    B.[name] AS index_name,
    B.[type],
    B.[type_desc],
    B.is_unique,
    B.is_primary_key,
    B.is_unique_constraint,
    B.is_disabled
INTO 
    #Indices
FROM
    #Colunas A
    JOIN sys.indexes B ON A.table_object_id = B.[object_id]
WHERE
    EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)
    

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao
SELECT DISTINCT
    table_object_id,
    table_schema_id,
    table_name,
    [schema_name],
    type_collation_name,
    database_default_collation,
    index_name,
    is_unique_constraint,
    is_primary_key
INTO
    #Indices_Alteracao
FROM
    #Indices


-------------------------------------------------------------------------
-- Gera os scripts
-------------------------------------------------------------------------

DECLARE 
    @CmdAlterTable VARCHAR(MAX) = '',

    @CmdDropIndex VARCHAR(MAX) = '',
    @CmdCreateIndex VARCHAR(MAX) = '',

    @CmdDropFK VARCHAR(MAX) = '',
    @CmdCreateFK VARCHAR(MAX) = '',

    @CmdDropIndexConstraint VARCHAR(MAX) = '',
    @CmdCreateIndexConstraint VARCHAR(MAX) = '',

    @CmdDisableCK VARCHAR(MAX) = '',
    @CmdEnableCK VARCHAR(MAX) = '',

    @Fl_Debug BIT = 1


SELECT 
    @CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),
    @CmdDropIndexConstraint += (CASE WHEN is_unique_constraint = 1 OR is_primary_key = 1 THEN 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] DROP CONSTRAINT [' + index_name + ']; ' ELSE '' END),
    @CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',
    @CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '
FROM
    #Indices_Alteracao

    

-------------------------------------------------------------------------
-- Identifica AS FK's que utilizam essas colunas
-------------------------------------------------------------------------


SELECT
    @CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'
FROM
    sys.foreign_keys AS A
    JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]
    JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]
    JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id


SELECT
    @CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(A.[name])
        FROM
            sys.columns AS A
            INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]
        WHERE
            B.constraint_object_id = D.[object_id]
        ORDER BY
            B.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(C.[name])
        FROM
            sys.columns AS C
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]
        WHERE
            fkc.constraint_object_id = D.[object_id]
        ORDER BY
            fkc.constraint_column_id
        FOR XML PATH(N''), TYPE
    ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''
                                                                                                                                                                                                                   ) + ');'
FROM
    sys.foreign_keys AS D
    JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]
    JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]
    JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]
    JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]
    JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id
WHERE
    E.is_ms_shipped = 0
    AND G.is_ms_shipped = 0


SELECT
    @CmdCreateIndex += (CASE WHEN B.is_unique_constraint = 0 AND B.is_primary_key = 0 THEN 'CREATE ' + (CASE WHEN B.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' INDEX [' + B.[name] + '] ON [' + SCHEMA_NAME(C.[schema_id]) + '].[' + C.[name] + '] (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END),

    @CmdCreateIndexConstraint += (CASE WHEN B.is_unique_constraint = 1 OR B.is_primary_key = 1 THEN 'ALTER TABLE [' + A.[schema_name] + '].[' + A.table_name + '] ADD CONSTRAINT [' + A.index_name + '] ' + (CASE WHEN B.is_primary_key = 1 THEN 'PRIMARY KEY ' ELSE '' END) + (CASE WHEN B.is_unique = 1 AND B.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END)
FROM
    #Indices_Alteracao	AS A
    JOIN sys.indexes    AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT
    JOIN sys.tables     AS C ON C.[object_id] = B.[object_id]
    JOIN sys.indexes	AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id
    JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                    SELECT
                        ', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END
                    FROM
                        sys.index_columns AS X1
                        JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0
                    WHERE
                        X1.[object_id] = X3.[object_id]
                        AND X1.index_id = X3.index_id
                    GROUP BY
                        X1.[object_id],
                        X2.[name],
                        X1.index_id
                    ORDER BY
                        MAX(X1.key_ordinal)
                    FOR XML PATH('')
                    ),   1, 2, ''
                ) AS KeyColumns
            FROM
                sys.index_columns AS X3
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS X4
    )                    AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id
    JOIN sys.stats       AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id
    JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id
    JOIN sys.filegroups  AS H ON B.data_space_id = H.data_space_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                        SELECT
                            ', [' + X2.[name] + ']'
                        FROM
                            sys.index_columns AS X1
                            JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1
                        WHERE
                            X1.[object_id] = X3.[object_id]
                            AND X1.index_id = X3.index_id
                        GROUP BY
                            X1.[object_id],
                            X2.[name],
                            X1.index_id
                        FOR XML PATH('')
                    ),   1, 2, ''
                ) AS IncludedColumns
            FROM
                sys.index_columns AS X3  
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS tmp1
        WHERE
            IncludedColumns IS NOT NULL
    ) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id


SELECT
    @CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + [database_default_collation] + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'
FROM
    #Colunas A

    


-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

Script para cambiar la intercalación de todas las columnas (y dependencias) según la intercalación deseada:
Ver código fuente

SET NOCOUNT ON

-------------------------------------------------------------------------
-- Identifica as colunas com collation diferente do desejado
-------------------------------------------------------------------------

DECLARE
    @Collation_Desejado VARCHAR(100) = 'SQL_Latin1_General_CP1_CI_AI' -- SELECT * FROM sys.fn_helpcollations()


IF (OBJECT_ID('tempdb..#Colunas') IS NOT NULL) DROP TABLE #Colunas
SELECT
    B.[object_id] AS table_object_id,
    B.[name] AS table_name,
    B.[schema_id] AS table_schema_id,
    C.[name] AS [type_name],
    D.[name] AS [schema_name],
    D.[schema_id],
    A.*,
    C.system_type_id AS type_system_type_id,
    C.user_type_id AS type_user_type_id,
    C.collation_name AS type_collation_name,
    E.collation_name AS [database_default_collation]
INTO
    #Colunas
FROM
    sys.columns		            AS A
    JOIN sys.tables	            AS B ON A.[object_id] = B.[object_id]
    JOIN sys.types		        AS C ON A.user_type_id = C.user_type_id
    JOIN sys.schemas	        AS D ON B.[schema_id] = D.[schema_id]
    JOIN sys.databases	        AS E ON E.database_id = DB_ID()
WHERE
    B.is_ms_shipped = 0
    AND A.collation_name <> @Collation_Desejado
   

IF (OBJECT_ID('tempdb..#Tabelas') IS NOT NULL) DROP table #Tabelas
SELECT DISTINCT
    table_object_id,
    table_name,
    table_schema_id,
    [schema_name]
INTO
    #Tabelas
FROM
    #Colunas


-------------------------------------------------------------------------
-- Identifica os índices que utilizam essas colunas
-------------------------------------------------------------------------
    
IF (OBJECT_ID('tempdb..#Indices') IS NOT NULL) DROP TABLE #Indices
SELECT 
    A.*,
    B.[name] AS index_name,
    B.[type],
    B.[type_desc],
    B.is_unique,
    B.is_primary_key,
    B.is_unique_constraint,
    B.is_disabled
INTO 
    #Indices
FROM
    #Colunas A
    JOIN sys.indexes B ON A.table_object_id = B.[object_id]
WHERE
    EXISTS(SELECT NULL FROM sys.index_columns X WHERE X.index_id = B.index_id AND X.column_id = A.column_id)
    

IF (OBJECT_ID('tempdb..#Indices_Alteracao') IS NOT NULL) DROP TABLE #Indices_Alteracao
SELECT DISTINCT
    table_object_id,
    table_schema_id,
    table_name,
    [schema_name],
    type_collation_name,
    database_default_collation,
    index_name,
    is_unique_constraint,
    is_primary_key
INTO
    #Indices_Alteracao
FROM
    #Indices


-------------------------------------------------------------------------
-- Gera os scripts
-------------------------------------------------------------------------

DECLARE 
    @CmdAlterTable VARCHAR(MAX) = '',

    @CmdDropIndex VARCHAR(MAX) = '',
    @CmdCreateIndex VARCHAR(MAX) = '',

    @CmdDropFK VARCHAR(MAX) = '',
    @CmdCreateFK VARCHAR(MAX) = '',

    @CmdDropIndexConstraint VARCHAR(MAX) = '',
    @CmdCreateIndexConstraint VARCHAR(MAX) = '',

    @CmdDisableCK VARCHAR(MAX) = '',
    @CmdEnableCK VARCHAR(MAX) = '',

    @Fl_Debug BIT = 1


SELECT 
    @CmdDropIndex += (CASE WHEN is_unique_constraint = 0 AND is_primary_key = 0 THEN 'DROP INDEX [' + index_name + '] ON [' + [schema_name] + '].[' + table_name + ']; ' ELSE '' END),
    @CmdDropIndexConstraint += (CASE WHEN is_unique_constraint = 1 OR is_primary_key = 1 THEN 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] DROP CONSTRAINT [' + index_name + ']; ' ELSE '' END),
    @CmdDisableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] NOCHECK CONSTRAINT ALL; ',
    @CmdEnableCK += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] CHECK CONSTRAINT ALL; '
FROM
    #Indices_Alteracao

    

-------------------------------------------------------------------------
-- Identifica AS FK's que utilizam essas colunas
-------------------------------------------------------------------------


SELECT
    @CmdDropFK += N'ALTER TABLE ' + QUOTENAME(C.[name]) + '.' + QUOTENAME(B.[name]) + ' DROP CONSTRAINT ' + QUOTENAME(A.[name]) + ';'
FROM
    sys.foreign_keys AS A
    JOIN sys.tables AS B ON A.parent_object_id = B.[object_id]
    JOIN sys.schemas AS C ON B.[schema_id] = C.[schema_id]
    JOIN #Tabelas AS D ON B.[object_id] = D.table_object_id


SELECT
    @CmdCreateFK += N'ALTER TABLE ' + QUOTENAME(H.[name]) + '.' + QUOTENAME(G.[name]) + ' ADD CONSTRAINT ' + QUOTENAME(D.[name]) + ' FOREIGN KEY (' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(A.[name])
        FROM
            sys.columns AS A
            INNER JOIN sys.foreign_key_columns AS B ON B.parent_column_id = A.column_id AND B.parent_object_id = A.[object_id]
        WHERE
            B.constraint_object_id = D.[object_id]
        ORDER BY
            B.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(F.[name]) + '.' + QUOTENAME(E.[name]) + '(' + 
    STUFF((
        SELECT
            ',' + QUOTENAME(C.[name])
        FROM
            sys.columns AS C
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = C.column_id AND fkc.referenced_object_id = C.[object_id]
        WHERE
            fkc.constraint_object_id = D.[object_id]
        ORDER BY
            fkc.constraint_column_id
        FOR XML PATH(N''), TYPE
    ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N''
                                                                                                                                                                                                                   ) + ');'
FROM
    sys.foreign_keys AS D
    JOIN sys.tables AS E ON D.referenced_object_id = E.[object_id]
    JOIN sys.schemas AS F ON E.[schema_id] = F.[schema_id]
    JOIN sys.tables AS G ON D.parent_object_id = G.[object_id]
    JOIN sys.schemas AS H ON G.[schema_id] = H.[schema_id]
    JOIN #Tabelas AS I ON G.[object_id] = I.table_object_id
WHERE
    E.is_ms_shipped = 0
    AND G.is_ms_shipped = 0


SELECT
    @CmdCreateIndex += (CASE WHEN B.is_unique_constraint = 0 AND B.is_primary_key = 0 THEN 'CREATE ' + (CASE WHEN B.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' INDEX [' + B.[name] + '] ON [' + SCHEMA_NAME(C.[schema_id]) + '].[' + C.[name] + '] (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END),

    @CmdCreateIndexConstraint += (CASE WHEN B.is_unique_constraint = 1 OR B.is_primary_key = 1 THEN 'ALTER TABLE [' + A.[schema_name] + '].[' + A.table_name + '] ADD CONSTRAINT [' + A.index_name + '] ' + (CASE WHEN B.is_primary_key = 1 THEN 'PRIMARY KEY ' ELSE '' END) + (CASE WHEN B.is_unique = 1 AND B.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END) + B.[type_desc] COLLATE DATABASE_DEFAULT + ' (' + E.KeyColumns + ')' + ISNULL(' INCLUDE (' + I.IncludedColumns + ')', '') + ISNULL(' WHERE ' + B.filter_definition, '') + ' WITH (' + CASE WHEN B.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', ' + 'FILLFACTOR = ' + CONVERT(VARCHAR(5), CASE WHEN B.fill_factor = 0 THEN 100 ELSE B.fill_factor END) + ', ' +
    'SORT_IN_TEMPDB = OFF, ' + (CASE WHEN B.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END) + ', ' + (CASE WHEN F.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END) + ', ' +
    'ONLINE = OFF, DATA_COMPRESSION = PAGE, ' + (CASE WHEN B.[allow_row_locks] = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END) + ', ' + (CASE WHEN B.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END) + ') ON [' + G.[name] + ']; ' ELSE '' END)
FROM
    #Indices_Alteracao	AS A
    JOIN sys.indexes    AS B ON A.table_object_id = B.[object_id] AND A.index_name = B.[name] COLLATE DATABASE_DEFAULT
    JOIN sys.tables     AS C ON C.[object_id] = B.[object_id]
    JOIN sys.indexes	AS D ON B.[object_id] = D.[object_id] AND B.index_id = D.index_id
    JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                    SELECT
                        ', [' + X2.[name] + ']' + CASE WHEN MAX(CONVERT(INT, X1.is_descending_key)) = 1 THEN ' DESC' ELSE '' END
                    FROM
                        sys.index_columns AS X1
                        JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 0
                    WHERE
                        X1.[object_id] = X3.[object_id]
                        AND X1.index_id = X3.index_id
                    GROUP BY
                        X1.[object_id],
                        X2.[name],
                        X1.index_id
                    ORDER BY
                        MAX(X1.key_ordinal)
                    FOR XML PATH('')
                    ),   1, 2, ''
                ) AS KeyColumns
            FROM
                sys.index_columns AS X3
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS X4
    )                    AS E ON B.[object_id] = E.[object_id] AND B.index_id = E.index_id
    JOIN sys.stats       AS F ON F.[object_id] = B.[object_id] AND F.stats_id = B.index_id
    JOIN sys.data_spaces AS G ON B.data_space_id = G.data_space_id
    JOIN sys.filegroups  AS H ON B.data_space_id = H.data_space_id
    LEFT JOIN
    (
        SELECT
            *
        FROM
        (
            SELECT
                X3.[object_id],
                X3.index_id,
                STUFF((
                        SELECT
                            ', [' + X2.[name] + ']'
                        FROM
                            sys.index_columns AS X1
                            JOIN sys.columns  AS X2 ON X2.[object_id] = X1.[object_id] AND X2.column_id = X1.column_id AND X1.is_included_column = 1
                        WHERE
                            X1.[object_id] = X3.[object_id]
                            AND X1.index_id = X3.index_id
                        GROUP BY
                            X1.[object_id],
                            X2.[name],
                            X1.index_id
                        FOR XML PATH('')
                    ),   1, 2, ''
                ) AS IncludedColumns
            FROM
                sys.index_columns AS X3  
            GROUP BY
                X3.[object_id],
                X3.index_id
        ) AS tmp1
        WHERE
            IncludedColumns IS NOT NULL
    ) AS I ON I.[object_id] = B.[object_id] AND I.index_id = B.index_id


SELECT
    @CmdAlterTable += 'ALTER TABLE [' + [schema_name] + '].[' + table_name + '] ALTER COLUMN [' + [name] + '] ' + UPPER([type_name]) + (CASE WHEN UPPER([type_name]) <> 'SYSNAME' THEN '(' + (CASE WHEN A.max_length > 0 THEN CAST(A.max_length AS VARCHAR(10)) ELSE 'MAX' END) + ')' ELSE '' END) + ' COLLATE ' + @Collation_Desejado + ' ' + (CASE WHEN A.is_sparse = 1 THEN 'SPARSE ' ELSE '' END) + (CASE WHEN A.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';'
FROM
    #Colunas A

    


-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) PRINT @CmdDropFK
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) PRINT @CmdDisableCK
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) PRINT @CmdDropIndex
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) PRINT @CmdDropIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) PRINT @CmdAlterTable
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) PRINT @CmdEnableCK
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) PRINT @CmdCreateIndex
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) PRINT @CmdCreateIndexConstraint
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) PRINT @CmdCreateFK
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

Resultado:

Si el script identifica muchas columnas para cambiar y la salida PRINT se corta debido al tamaño de las cadenas generadas, puede usar la función fncSplitTexto, que puse a disposición en la publicación. Cómo dividir una cadena en una tabla de subcadenas usando un delimitador en SQL Server y reemplace el final del script con este extracto aquí:

-------------------------------------------------------------------------
-- Executa os scripts
-------------------------------------------------------------------------

PRINT '---------- Dropando as contraints FK'
IF (NULLIF(LTRIM(RTRIM(@CmdDropFK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropFK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropFK)

PRINT '---------- Desativando as Check Contraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDisableCK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDisableCK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDisableCK)

PRINT '---------- Dropando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndex)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropIndex, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropIndex)

PRINT '---------- Dropando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdDropIndexConstraint)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdDropIndexConstraint, ';')
IF (@Fl_Debug = 0) EXEC(@CmdDropIndexConstraint)

PRINT '---------- Alterando a tabela'
IF (NULLIF(LTRIM(RTRIM(@CmdAlterTable)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdAlterTable, ';')
IF (@Fl_Debug = 0) EXEC(@CmdAlterTable)

PRINT '---------- Reativando as constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdEnableCK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdEnableCK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdEnableCK)

PRINT '---------- Recriando os índices'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndex)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateIndex, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndex)

PRINT '---------- Recriando os índices em constraints'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateIndexConstraint)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateIndexConstraint, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateIndexConstraint)

PRINT '---------- Recriando as FKs'
IF (NULLIF(LTRIM(RTRIM(@CmdCreateFK)), '') IS NOT NULL) SELECT * FROM dirceuresende.dbo.fncSplitTexto(@CmdCreateFK, ';')
IF (@Fl_Debug = 0) EXEC(@CmdCreateFK)

Resultado:

Espero que hayas disfrutado de esta publicación y ¡hasta la próxima!
PD: Recuerde hacer una copia de seguridad y probar MUCHO si planea aplicar este script en Producción.

Si tienes alguna duda o problema déjalo aquí en los comentarios.