Ajustar o Collate (ordenação) no banco de dados SQL Server

Em alguns casos encontramos o erro informando que o Collate X não pode ser usado pelo Collate Y, isso acontece porque o Collate do servidor não é o mesmo do banco. Para correção precisamos sempre colocar o Collate do banco igual ao Collate do Servidor.

Para ajustar o collate do banco, se faz necessário algumas coisas:

Remover todos os índices do banco
Remover os campos calculados
Mudar o banco para Single User
Ajustar o Collate do banco para o mesmo do servidor
Ajustar o Collate de todos os campos nas tabelas para o mesmo do servidor
Voltar o banco para multi user
Recriar os campos calculados
Recriar os índices

Remover todos os índices

Podemos usar o DBDIC para recriar todos os índices, mas se estivermos usando um cliente que tem índices customizados ou com otimizações feitas direto no banco de dados, vamos perder isso, para isso podemos montar um script que contenha a criação de todos os índices, podemos criar esse script rodando o seguinte comando no banco de dados do cliente

Script para criar os índices

SELECT ' CREATE ' +
CASE
WHEN I.is_unique = 1 THEN ' UNIQUE '
ELSE ''
END +
I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX [' +
I.name + '] ON ' +
SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
KeyColumns + ' ) ' +
ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
CASE
WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
ELSE ' PAD_INDEX = OFF '
END + ',' +
'FILLFACTOR = ' + CONVERT(
CHAR(5),
CASE
WHEN I.fill_factor = 0 THEN 100
ELSE I.fill_factor
END
) + ',' +
-- default value
'SORT_IN_TEMPDB = OFF ' + ',' +
CASE
WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
ELSE ' IGNORE_DUP_KEY = OFF '
END + ',' +
CASE
WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
ELSE ' STATISTICS_NORECOMPUTE = ON '
END + ',' +
' ONLINE = OFF ' + ',' +
CASE
WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
ELSE ' ALLOW_ROW_LOCKS = OFF '
END + ',' +
CASE
WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
ELSE ' ALLOW_PAGE_LOCKS = OFF '
END + ' ) ON [' +
DS.name + ' ] ;' [CreateIndexScript]
FROM sys.indexes I
JOIN sys.tables T
ON T.object_id = I.object_id
JOIN sys.sysindexes SI
ON I.object_id = SI.id
AND I.index_id = SI.indid
JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name + CASE
WHEN MAX(CONVERT(INT, IC1.is_descending_key))
= 1 THEN
' DESC '
ELSE
' ASC '
END
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
ORDER BY
MAX(IC1.key_ordinal)
FOR XML PATH('')
),
1,
2,
''
) KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp3
)tmp4
ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST
ON ST.object_id = I.object_id
AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
FOR XML PATH('')
),
1,
2,
''
) IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2
ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0

Feito isso salve os comandos em um arquivo txt para ser usado depois

Depois que salvamos os índices, vamos apaga-los com o seguinte comando

Scriipt para apagar os índices

SELECT ' DROP INDEX [' +
I.name + '] ON ' +
SCHEMA_NAME(T.schema_id) + '.' + T.name + ';' [DropIndexScript]
FROM sys.indexes I
JOIN sys.tables T
ON T.object_id = I.object_id
JOIN sys.sysindexes SI
ON I.object_id = SI.id
AND I.index_id = SI.indid
JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name + CASE
WHEN MAX(CONVERT(INT, IC1.is_descending_key))
= 1 THEN
' DESC '
ELSE
' ASC '
END
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
ORDER BY
MAX(IC1.key_ordinal)
FOR XML PATH('')
),
1,
2,
''
) KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp3
)tmp4
ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST
ON ST.object_id = I.object_id
AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
FOR XML PATH('')
),
1,
2,
''
) IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2
ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0

Pegue todo o conteúdo do Script e rode no management Studio ou no DBeaver

Remover os campos calculados

Para essa operação eu levantei alguns campos calculados da versão atual e monte o seguinte script

Remover campos Calculados

alter table saidas drop column tipo_cod_operacao;
alter table entradas drop column tipo_cod_operacao;
alter table transferencias drop column tipo_cod_operacao;
alter table lotes drop column lote_null_to_z;
alter table lancamentos drop column DUPL_COMPLETA_COBREBEM_S_DIGITO;
alter table lancamentos drop column TIT_BANCO_SEM_DIGITO;
alter table lancamentos drop column DUPLICATA_SEM_DIGITO;
alter table geradores drop column TRIM_CNPJ ;

Nota: Nas versões mais antigas, alguns desses campos não existe e existe ainda a TRIM_CNPJ na tabela iclientes, basta usar o mesmo comando da tabela geradores mudando para iclientes

Mudar o banco para Single User

Execute o seguinte comando

ALTER DATABASE NOMEDOBANCO SET SINGLE_USER WITH ROOLBACK IMMEDIATE

Onde NOMEDOBANCO é o nome do banco de dados que vamos ajustar

Ajustar o Collate do banco para o mesmo do servidor

Antes de fazermos isso precisamos achar o collate do servidor, para isso abra o Management Studio e clieque com o botão direito no nome do servidor e vá em propriedades (ou Properties) e na janela que abrir anote a ordenação que está configurada no servidor.

E execute então o seguinte comando

ALTER DATABASE NOMEDOBANCO COLLATE ORDENADAO

Onde NOMEDOBANCO é o nome do banco de dados que vamos ajustar e ORDENACAO é a ordenação que pegamos acima, guarde o nome da ordenação que vamos utilizá-la outras vezes

Ajustar o Collate de todos os campos nas tabelas para o mesmo do servidor

Agora que já ajustamos o banco, vamos ajustar as tabelas, rodando o seguinte script mudando o que está na palavra ORDENACAO para o nome que pegamos acima

Ajusta tabelas

select
comando = 'ALTER TABLE ' +table_schema+'.'+table_name+' ALTER COLUMN '+COLUMN_NAME+' '+DATA_TYPE+'('+case when CHARACTER_MAXIMUM_LENGTH=-1 then 'max' else cast(CHARACTER_MAXIMUM_LENGTH as varchar(5)) end+') COLLATE ORDENACAO;'
into #comandos
FROM information_schema.COLUMNS
WHERE collation_name<>'ORDENACAO';

Declare @ComandoAtual VarChar(500) = '';

While (Select Count(*) From #Comandos) > 0
Begin
Select top 1 @ComandoAtual = Comando From #Comandos
Exec (@ComandoAtual)
Delete #Comandos Where (Comando = @ComandoAtual)
End;

drop table #comandos;;

Voltar o banco para multi user

Execute o seguinte comando

ALTER DATABASE NOMEDOBANCO SET MULTI_USER

Onde NOMEDOBANCO é o nome do banco de dados que vamos ajustar

Recriar os campos calculados

Recria campos calculados

alter table saidas add tipo_cod_operacao as (CONVERT([varchar](8000),[TIPO_OPERACAO_S])+CONVERT([varchar](8000),[SAIDA]));
alter table entradas add tipo_cod_operacao as (CONVERT([varchar](8000),[TIPO_OPERACAO_E])+CONVERT([varchar](8000),[ENTRADA]));
alter table transferencias add tipo_cod_operacao as (CONVERT([varchar](8000),[TIPO_OPERACAO_T])+CONVERT([varchar](8000),[TRANSFERENCIA]));
alter table lotes add LOTE_NULL_TO_Z as (coalesce(CONVERT([varchar](8000),[LOTE]),CONVERT([varchar](254),'')));
alter table lancamentos add DUPL_COMPLETA_COBREBEM_S_DIGITO as (substring([DUPLICATA_COMPLETA_COBREBEM],(1),(19)))
alter table lancamentos add TIT_BANCO_SEM_DIGITO as (substring([TIT_BANCO],(1),(19)))
alter table lancamentos add DUPLICATA_SEM_DIGITO as (substring([DUPLICATA],(1),(19)))
alter table geradores add TRIM_CNPJ as (CONVERT([varchar](20),Trim([CNPJ])))

alter table iclientes add TRIM_CNPJ as (CONVERT([varchar](20),Trim([CNPJ])))

Recriar os índices

Agora pegue o txt que foi salvo com todos os índices e rode-o aqui

Atalhos

Árvore de páginas