none
Alterar Collate do BD RRS feed

  • Pergunta

  • Quando altero o collate de um banco somente, a alteração dos collates dos campos das tabelas também são alteradas?
    • Movido Gustavo Maia Aguiar quinta-feira, 24 de fevereiro de 2011 20:08 (De:Programação avançada com o SQL Server)
    sexta-feira, 22 de agosto de 2008 17:24

Respostas

  • Boa Noite,

     

    O Collate influencia como os caractéres são ordenados, comparados e armazenados. Temos o Collate até o nível de coluna. Se o Collate é definido até o nível de coluna e o dado já está armazenado, trocar o Collate do banco de dados não irá alterar o dado que já foi gravado. Isso significa que se você alterar o collate do banco de dados, as novas colunas o respeitarão, mas as colunas antigas não pois já tiveram seus dados gravados. É possível fazer um script para alterar a collation dos bancos de dados já gravados, mas três condições precisam ser garantidas

    • A coluna não faz parte da chave primária
    • A coluna não faz parte de um índice
    • A coluna não possui nenhuma constraint associada (Unique, Check Constraint, etc)
    • A coluna não está em uma tabela replicada

    Segue uma proposta

     

    Code Snippet

    -- Escolher uma collation

    DECLARE @COLLATION VARCHAR(50)

    SET @COLLATION = 'SQL_Latin1_General_CP1_CI_AI'

     

    -- Gerar todas as instruções de alteração

    SELECT

    'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' +

    CASE

    WHEN DATA_TYPE LIKE '%CHAR' AND CHARACTER_MAXIMUM_LENGTH > 0 THEN

    DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'

    WHEN DATA_TYPE LIKE '%CHAR' AND CHARACTER_MAXIMUM_LENGTH < 0 THEN

    DATA_TYPE + '(MAX)'

    ELSE DATA_TYPE END +

     

    'COLLATE ' + @COLLATION +

     

    CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLLATION_NAME IS NOT NULL

     

    -- Verificar que colunas não tem a nova collation

    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLLATION_NAME != @COLLATION

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 25 de agosto de 2008 01:15
  • Gustavo,

    Sua ajuda foi excepcional, me valeu muito te agradeço.
    segunda-feira, 25 de agosto de 2008 01:40

Todas as Respostas

  • Os campos que estiverem com o Collation "database default" assumirão o collate do servidor, caso contrário não.

    Att,
    Marcel Inowe
    sexta-feira, 22 de agosto de 2008 19:06
  • O collate do BD foi para cada tabela. Existe alguma procedure que possa me auxiliar nessa alteração? Pq a base já está em produção e é necessário alterar com muita cautela.

    Como posso visualizar o collate que está sendo usado nas tabelas?
    sexta-feira, 22 de agosto de 2008 20:42
  • Boa Noite,

     

    O Collate influencia como os caractéres são ordenados, comparados e armazenados. Temos o Collate até o nível de coluna. Se o Collate é definido até o nível de coluna e o dado já está armazenado, trocar o Collate do banco de dados não irá alterar o dado que já foi gravado. Isso significa que se você alterar o collate do banco de dados, as novas colunas o respeitarão, mas as colunas antigas não pois já tiveram seus dados gravados. É possível fazer um script para alterar a collation dos bancos de dados já gravados, mas três condições precisam ser garantidas

    • A coluna não faz parte da chave primária
    • A coluna não faz parte de um índice
    • A coluna não possui nenhuma constraint associada (Unique, Check Constraint, etc)
    • A coluna não está em uma tabela replicada

    Segue uma proposta

     

    Code Snippet

    -- Escolher uma collation

    DECLARE @COLLATION VARCHAR(50)

    SET @COLLATION = 'SQL_Latin1_General_CP1_CI_AI'

     

    -- Gerar todas as instruções de alteração

    SELECT

    'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' +

    CASE

    WHEN DATA_TYPE LIKE '%CHAR' AND CHARACTER_MAXIMUM_LENGTH > 0 THEN

    DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'

    WHEN DATA_TYPE LIKE '%CHAR' AND CHARACTER_MAXIMUM_LENGTH < 0 THEN

    DATA_TYPE + '(MAX)'

    ELSE DATA_TYPE END +

     

    'COLLATE ' + @COLLATION +

     

    CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLLATION_NAME IS NOT NULL

     

    -- Verificar que colunas não tem a nova collation

    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLLATION_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE COLLATION_NAME != @COLLATION

     

     

    [ ]s,

     

    Gustavo

    segunda-feira, 25 de agosto de 2008 01:15
  • Gustavo,

    Sua ajuda foi excepcional, me valeu muito te agradeço.
    segunda-feira, 25 de agosto de 2008 01:40
  • Olá José,

     

    Espero que o script lhe tenha sido útil. Se puder classifique a resposta.

     

    [ ]s,

     

    Gustavo

    segunda-feira, 25 de agosto de 2008 02:34
  • Olá Gustavo,

    Li os posts acima e eles se indentificam com o meu problema.

    Eu tenho que alterar as collations das colunas das minhas tabelas para SQL_Latin1_General_CP1_CI_AI. Porém algumas delas recaem nessa sua lista. Algumas delas são PK e são associadas a constraints, outras ligadas a Indices e por isso apresentou erro. Tive problemas também para alterar collation de colunas de tipo INT
    Já tentei desabilitar as constraints e tentar alterar, mas não funcionou.
    Como fazer para trocar a collation dessas colunas que recaem em algumas dessas condições??

    Obrigado a todos!
    quinta-feira, 6 de novembro de 2008 20:14
  • Boa Tarde Thiago,

     

    Não adianta desabilitar as constraints, elas continuaram a existir e o resultado continuará na collation antiga. Constraints do tipo PK e índices tem uma chave e essa chave é do mesmo tipo de dados da PK e do índice (inclusive a collation).

     

    O único jeito será alterar via interface gráfica (nesse caso o SQL Server se encarregará de reconstruir os índices e constraints) ou delete-as, altere a collation e reconstrua-as.

     

    Adicionalmente consulte o artigo abaixo:

     

    Como resolver problemas relacionados a conflitos de Collation

    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!183.entry

     

    Acredito que no caso do INT haja algum equívoco. Pode ser que ele faça parte de um índice composto ou de uma chave composta, mas não impactos de collation relacionados a INT. O INT não é variável de acordo com características como CHARSET, Unicode, etc. Um INT de valor 8 será sempre o mesmo em qualquer lugar do mundo.

     

    [ ]s,

     

    Gustavo

     

    quinta-feira, 6 de novembro de 2008 20:55
  • Olá Gustavo,
    li no artigo que eu posso alterar manualmente.
    No meu caso são poucas colunas, então posso alterar manualmente. Porém quando peço pra salvar a alteração dá timeout e a alteração não se concretiza. A maioria das colunas eu conseguir alterar graficamente, mas em algumas poucas eu tenho esse problema de TIMEOUT.

    Alguma idéia?
    quinta-feira, 6 de novembro de 2008 22:43
  • Ola de novo..


    usei a solução de deletar e refazer os indices..aparentemente funcionou.

    Obrigado Gustavo e fica aí a experiência a todos
    quinta-feira, 6 de novembro de 2008 23:21
  • Olá a todos,

     

    Gustavo, quero registrar aqui meu agradecimento!!! Sua resposta foi muito clara e de extrema ajuda. Estava com uma situação parecida com a dos colegas e consegui resolver com seus scripts.

     

    []´s

     

    PH.

    quarta-feira, 14 de janeiro de 2009 11:13
  • Olá Phscuritiba,

     

    Seja bem vindo ao fórum de SQL Server no MSDN.

    Obrigado pelo agradecimentos. Nós participantes do fórum estamos sempre tentando ajudar da melhor maneira possível.

     

    [ ]s,

     

    Gustavo Maia Aguiar

    http://gustavomaiaaguiar.spaces.live.com

    quarta-feira, 14 de janeiro de 2009 11:26
  • Obrigado pelo Script.

    Muito Útil já deixei como Padrão no meu repositório.

     

    Att

    Jean Carlo

    quinta-feira, 24 de fevereiro de 2011 15:40
  • Gustavo Boa Tarde;

    Estou com problemas de conflitos de collation, onde a minha aplicaçao solicita o collation "SQL_Latin1_General_CP850_CI_AS" e o meu banco atual esta instalado o collation "SQL_Latin1_General_CP1_CI_AS", sendo assim executei alguns Query (ALTER DATABASE SBOTeste COLLATE SQL_Latin1_General_CP850_CI_AS ) para alterar o collation do banco e das tabelas (Foi executado com exito) e tambem ja fiz uso do script acima citado. Logo os atributos das minhas 22656 tabelas nao foram alterados. Alguem tem algum scritp para fazer esta alteração? Veja como ficou o meu cenario abaixo:

    SERVIDOR:Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

    BANCO DADOS = COLLATE SQL_Latin1_General_CP850_CI_AS

    Tabelas = COLLATE SQL_Latin1_General_CP850_CI_AS

    Atributo da minha tabela = SQL_Latin1_General_CP1_CI_AS 

    Logo apos da execucao do script citado eu fç um select

    "SELECT

    TABLE_NAME, COLUMN_NAME, COLLATION_NAME

    FROM

    SBOTesteTowertel.

     

    WHERE

    TABLE_NAME = 'ORIN' "

    em uma das minhas tabelas e a mesma continua com os seus atributos como collation antigo (SQL_Latin1_General_CP1_CI_AS). Porem as novas tabelas vem com o novo collation (SQL_Latin1_General_CP850_CI_AS).

    Desde já agradeço pela atenção e o trabalho .

    INFORMATION_SCHEMA.COLUMNS

    quarta-feira, 28 de setembro de 2011 15:11