SQL Server – Como padronizar o Collation de todas as colunas do database

Visualizações: 4.808
Tempo de Leitura: 15 minutos

Olá pessoal!
Tudo tranquilo?

Neste artigo, gostaria de compartilhar com vocês um problema que tive recentemente, o qual várias colunas, de várias tabelas de um determinado database utilizavam uma collation diferente do padrão do DB, fazendo com que ao realizar joins e condições WHERE entre colunas VARCHAR/CHAR/NVARCHAR com collations diferentes, o banco nos retorne a seguinte mensagem de erro:

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

O que é uma COLLATION?

O Collation nada mais é do que a forma de codificação de caracteres que um banco de dados utiliza para interpretá-los.

Um Collation é um agrupamento desses caracteres em uma determinada ordem (cada Collation tem uma ordem diferente), onde o “A” é um caracter diferente do “a”, caso o collation seja case-sensitive (diferenciação de maiúsculos e minúsculos) e o “a” é diferente do “á”, caso o collation seja Accent-Sensitive (diferenciação de acentos).

O COLLATION possui três níveis de hierarquia:
– Servidor
– Database
– Coluna

Caso o database seja criado sem especificar qual a collation que será utilizada, ele será criado com a collation do servidor (idioma do sistema operacional). Quando uma tabela é criada sem especificar o collation das colunas de texto (VARCHAR, NVARCHAR, CHAR, etc), o collate do database será utilizado como o collation das tabelas.

No SQL Server, o nome da Collation segue o seguinte padrão de nomeclatura:
SQL_CollationDesignator_CaseSensitivity_AccentSensitivity_KanatypeSensitive_WidthSensitivity

Exemplo de Collation:
SQL_Latin1_General_CP1_CS_AS

Onde:

  • CollationDesignator: Especifica as regras de agrupamento básicas usadas pelo agrupamento do Windows, onde as regras de classificação são baseadas no alfabeto ou no idioma.
  • CaseSensitivity: CI especifica que não diferencia maiúsculas de minúsculas, CS especifica que diferencia maiúsculas de minúsculas.
  • AccentSensitivity: AI especifica que não diferencia acentos, AS especifica que diferencia acento.
  • KanatypeSensitive: Omitido especifica que não faz distinção de caracteres kana, KS especifica que faz distinção de caracteres kana.
  • WidthSensitivity: Omitido especifica que não distingue largura, WS especifica que distingue largura.

Se uma coluna estiver utilizando uma COLLATION case sensitive (CS), uma query como SELECT * FROM Tabela WHERE Coluna LIKE ‘%Oracle%’ irá retornar o registro “Oracle”, mas não irá retornar o registro “oracle”.

A mesma coisa acontece com uma coluna utilizando um COLLATION accent sensitive (AS). Uma query como SELECT * FROM Tabela WHERE Coluna LIKE ‘%JOÃO%’ irá retornar o registro “João”, mas não irá retornar o registro “Joao”.

Para verificar a lista completa de Collations por região e idioma, acesse este link, lembrando que o mais utilizado no idioma Português (Brasil) é o SQL_Latin1_General_CP1_CI_AI (ou SQL_Latin1_General_CP1_CS_AS).

Para saber mais sobre o que é Collation, dê uma olhada no artigo SQL Server – Cannot resolve the collation conflict between … in the equal to operation..

Como identificar a collation padrão?

Para identificar a collation padrão de um database, basta utilizar a query abaixo:

Transact-SQL
1
2
3
4
5
6
USE [dirceuresende]
GO
 
SELECT collation_name
FROM sys.databases
WHERE database_id = DB_ID()

Resultado:

Para identificar a collation padrão da sua instância, basta utilizar o comando abaixo:

Transact-SQL
1
SELECT SERVERPROPERTY('Collation')

Resultado:

Geração da base para os testes

Para padronizar os exemplos e ajudar no entendimento prático desse artigo, vou utilizar um script simples para gerar dados de exemplo (testes):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
------------------------------------------------------------------------
-- 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)

Como padronizar a collation de todas as colunas?

Caso você queira padronizar a collation de todas as colunas de um database, de modo que elas utilizem uma collation específica ou a padrão do database, desenvolvi um script que irá identificar todas as colunas que estão fora do padrão e irá executar um comando de ALTER TABLE para aplicar a alteração.

Como existem dependências (constraints, índices e foreign keys), o script irá dropar os objetos dependentes identificados, aplicar as alterações e recriar esses objetos. Por conta disso, recomendo fortemente que você teste bem antes de executar esse script em produção. De preferência, faça um backup de estrutura antes.

Script para alterar a collation de todas as colunas (e dependências) que estejam com o Collation diferente do database
Visualizar código-fonte

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
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 alterar o collation de todas as colunas (e dependências) de acordo com o Collation desejado:
Visualizar código-fonte

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
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:

Caso o script identifique muitas colunas para alterar e a saída do PRINT esteja ficando cortada por causa do tamanho das strings geradas, você pode utilizar a função fncSplitTexto, que disponibilizei no post Como quebrar um string em uma tabela de substrings utilizando um delimitador no SQL Server e substituir o final do script por esse trecho aqui:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-------------------------------------------------------------------------
-- 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 tenham gostado desse post e até a próxima!
PS: Lembre de fazer o backup e testar BASTANTE se você planeja aplicar esse script em Produção.

Em caso de dúvidas ou problemas, deixe aqui nos comentários.

Tags:

You may also like...

6 Responses

  1. Marcos disse:
    26 de janeiro de 2020 às 18:00

    Olá Dirceu.

    Agradeço pelo tempo dedicado em disseminar conhecimento.

    Eu executei esse procedimento mas infelizmente não funcionou.

    O script até alterou collation do SQL SERVER mas DATABASE quero alterar as tabelas não funcionou.

    Versão do SQL

    Microsoft SQL Server 2012 (SP4) (KB4018073)

    Tem alguma dica ?

    Responder
  2. Aldemir Fernandes disse:
    25 de abril de 2019 às 01:37

    Olá Dirceu.

    Agradeço demais esse post. Porém estou com um problema. Quando executo seu script, ele executa certinho, porém não altera o collation das colunas.

    No resultado, aponta como o script executado com sucesso.

    Tem ideia do que pode ser?

    Agradeço demais a ajuda.

    Responder
  3. Kelvin Faria disse:
    24 de setembro de 2018 às 15:22

    Olá Dirceu, boa tarde!

    Muito legal o post, porém tenho uma dúvida, no meu caso existem mais de 43mil colunas que precisam ser alteradas, portanto ao rodar o script o resultado do script ultrapassa o máximo permitido pelo campo “message” do management studio e acabo não vendo todos os comandos.

    Tem alguma solução?
    Obrigado desde já!

    Responder

Deixe uma resposta