none
Troca de Collate de um determinado banco de dados RRS feed

  • Pergunta

  • Prezados,

            Gostaria de alterar a collate de um determinado banco de dados, porém gostaria de saber para alterar um collate de um banco preciso também aplicar nas colunas das tabelas existente? Tem algum procedimento que faça em todas as colunas ou tenho que aplicar manualmente coluna a coluna ? Como posso fazer isto ?


    Grato

    Cláudio Bruno


    Bruno Avanso

    segunda-feira, 6 de janeiro de 2014 14:55

Respostas

  • Prezado Durval,

                    Como posso desativar o identify via linha de comando, sei apenas indo direto ao campo,

    Grato

    Cláudio Bruno


    Bruno Avanso

    Bruno,

    Para tabelas com colunas IDENTITY, você precisará declarar todos os campos no comando INSERT e no comando SELECT. Veja no exemplo abaixo:

    SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO ON
    
    INSERT INTO FrequenciasNova_Alunos_NOVO
    (ID_ALUNO, NM_ALUNO)
    SELECT 
    ID_ALUNO,
    NM_ALUNO COLLATE SQL_Latin1_General_CP1_CI_AI
    FROM FrequenciasNova_Alunos;
    
    SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO OFF

    Não esqueça de marcar o(s) post(s) que geraram sua solução como resposta ! Isto é importante para orientar, posteriormente, outros que tiverem problemas semelhantes ao seu.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Bruno Avanso quarta-feira, 8 de janeiro de 2014 04:25
    terça-feira, 7 de janeiro de 2014 23:30

Todas as Respostas

  • Bruno,

    Sei que se você alterar o Collate de uma instância, ele irá alterar todos os BDs dessa instância, porém não tenho certeza se alterará a nível de colunas.

    Segue um link que encontrei no blog do Roberto Galvão que demonstra como realizar o procedimento.

    Abraço!


    "A vida é um paraíso, mas os homens não o sabem e não se preocupam em sabê-lo." Fiodor Dostoievski

    segunda-feira, 6 de janeiro de 2014 15:11
  • Bruno,

         Segue um outro exemplo:


    http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database


    Roberto Fonseca MCT / MCITP - Database Administrator 2008 MCITP - Database Developer 2008 MCITP - Business Intelligence 2008

    segunda-feira, 6 de janeiro de 2014 15:25
    Moderador
  • Bruno,

    A alteração de um collation de um banco de dados somente afetará novos campos, os demais com datatype: char, nchar, varchar e nvarchar; vão permanecer com o collation do momento que foram criados.

    Para alterar o collation dos campos nas tabelas já existentes você tem duas opções:

    - Recriar à(s) tabela(s) com outro nome, sem a definição do collation e após isso, importar os dados da tabela original dentro da nova tabela. Ao terminar o processo, você pode remover à tabela original(recomendo renomear antes como "_old" até você se sentir confortável em remove-la) e renomeie à nova tabela com o nome da tabela original, ou;

    - Executar uma alteração das colunas dos datatypes indicados acima para cada tabela desejada. Segue um exemplo abaixo:

    ALTER TABLE dbo.TB_EXEMPLO 
    ALTER COLUMN NM_USUARIO VARCHAR(80) COLLATE Latin1_General_CI_AS
    

    Espero que seja útil para você.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Sugerido como Resposta Edinaldo Junior terça-feira, 7 de janeiro de 2014 20:36
    terça-feira, 7 de janeiro de 2014 01:53
  • Prezado Durval,

                 Obrigado pela resposta,  tem alguns campos que retorna a seguinte mensagem,

    Msg 4902, Level 16, State 1, Line 4
    Cannot find the object "BibliografiaTipos" because it does not exist or you do not have permissions.

    A linha executada foi o seguinte,

    "ALTER TABLE BibliografiaTipos ALTER COLUMN Descricao varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AI"

    Estou como logado como "sa" e o objeto existe, o que mais pode ser ? Na linha não preciso definir se ele é null ou not null ?

    Grato

    Cláudio Bruno


    Bruno Avanso

    terça-feira, 7 de janeiro de 2014 02:03
  • Bruno,

    Você tem razão, faltou incluir após o collation a opção NULL ou NOT NULL. Fiz um teste com uma tabela de exemplo que tenho. Segue o print-screen como evidência de teste.

    Espero que seja útil para você.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    terça-feira, 7 de janeiro de 2014 02:14
  • Bruno,

    Você tem razão, faltou incluir após o collation a opção NULL ou NOT NULL. Fiz um teste com uma tabela de exemplo que tenho. Segue o print-screen como evidência de teste.

    Espero que seja útil para você.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"

    Bruno,

    Verifique também se a tabela BibliografiaTipos pertence mesmo ao schema "dbo". Já vi erros semelhantes com a tabela criada em outro schema do banco de dados. Para saber qual o schema da sua tabela utilize o comando sp_help, como indicado abaixo:

    sp_help BibliografiaTipos 

    O resultado apresentará um retorno semelhante ao print-screen abaixo:

    Espero que seja útil para você.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    terça-feira, 7 de janeiro de 2014 02:53
  • Fiz da seguinte forma, com auxilio do Blog do Galvão ( bobgalvao.wordpress.com/2013/08/27/alterar-collation-de-banco-de-dados-sql-server )

    select ‘ALTER TABLE ‘ + ts.name +’.'+ t.name + ‘ ALTER COLUMN ‘ + c.name + ‘ ‘ + ty.name + ‘(‘ + CONVERT(VARCHAR, c.max_length) + ‘) COLLATE SQL_Latin1_General_CP1_CI_AI’ + ‘ ‘ +
    case
    when c.is_nullable =1 then ‘null’
    else ‘not null’
    end

    from sys.columns as c
    inner join sys.tables as t on c.object_id = t.object_id
    inner join sys.schemas as ts on t.schema_id = ts.schema_id
    inner join sys.types as ty on ty.system_type_id = c.system_type_id
    where c.collation_name is not null
    and t.type = ‘U’;
    go

    Porém apareceu um novo problema, tenho campos que são identifier com tipo char e são chaves primarias ou secundárias, no momento da importação retorna seguinte mensagem,

    Msg 5074, Level 16, State 1, Line 5
    The object ‘FK_FrequenciasNova_Alunos’ is dependent on column ‘Aluno_RA’.
    Msg 4922, Level 16, State 9, Line 5
    ALTER TABLE ALTER COLUMN Aluno_RA failed because one or more objects access this column.

    Como poderia resolver isto ?

    Grato


    Bruno Avanso

    terça-feira, 7 de janeiro de 2014 19:33
  • Bruno,

    Se fosse apenas Constraints sem relacionamento, acredito que seu problema seria menor.

    Neste caso, acredito que o melhor à fazer é recriar as tabelas nesta situação com outro nome (sem definir collation, porque ele vai herdar o novo collation do banco) e logo à seguir efetuar a carga dos dados da antiga tabela para a tabela nova. No final, você substitui à tabela errada pela tabela com o collation correto, renomeando a nova tabela com o nome utilizado em produção após excluir ou renomear à tabela com o collation incorreto.

    Para efetuar a carga para a tabelas do mesmo banco com collation diferentes, você pode usar o "Import/Export Data" do SSMS ou executar um comando semelhante ao exemplo abaixo:

    INSERT INTO FrequenciasNova_Alunos_NOVO
    (ID_ALUNO, NM_ALUNO)
    SELECT 
    ID_ALUNO,
    NM_ALUNO COLLATE SQL_Latin1_General_CP1_CI_AI
    FROM FrequenciasNova_Alunos;
    Se algum campo for IDENTITY, será necessário "desligar" e "religar" o IDENTITY_INSERT.

    Isto é mais trabalhoso, mas não vejo outra saída já que é um risco você conviver com parte das tabelas no novo collation e às tabelas que tem campos texto como PK e FK com outro collation.

    Analise bem a situação, porque manter mais de um collation também pode afetar à performance de suas consultas em JOIN´s entre tabelas de diferentes collations.

    Se for útil não esqueça de marcar o post !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Sugerido como Resposta Edinaldo Junior terça-feira, 7 de janeiro de 2014 20:35
    terça-feira, 7 de janeiro de 2014 20:33
  • Prezado Durval,

                    Como posso desativar o identify via linha de comando, sei apenas indo direto ao campo,

    Grato

    Cláudio Bruno


    Bruno Avanso

    terça-feira, 7 de janeiro de 2014 22:28
  • Prezado Durval,

                    Como posso desativar o identify via linha de comando, sei apenas indo direto ao campo,

    Grato

    Cláudio Bruno


    Bruno Avanso

    Bruno,

    Para tabelas com colunas IDENTITY, você precisará declarar todos os campos no comando INSERT e no comando SELECT. Veja no exemplo abaixo:

    SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO ON
    
    INSERT INTO FrequenciasNova_Alunos_NOVO
    (ID_ALUNO, NM_ALUNO)
    SELECT 
    ID_ALUNO,
    NM_ALUNO COLLATE SQL_Latin1_General_CP1_CI_AI
    FROM FrequenciasNova_Alunos;
    
    SET IDENTITY_INSERT FrequenciasNova_Alunos_NOVO OFF

    Não esqueça de marcar o(s) post(s) que geraram sua solução como resposta ! Isto é importante para orientar, posteriormente, outros que tiverem problemas semelhantes ao seu.

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Bruno Avanso quarta-feira, 8 de janeiro de 2014 04:25
    terça-feira, 7 de janeiro de 2014 23:30
  • Valeu obrigado pela atenção.

    Bruno Avanso

    quarta-feira, 8 de janeiro de 2014 04:25