none
Alterar collation RRS feed

  • Pergunta

  • Olá, boa tarde.

    Preciso alterar collation para Latin1_General_CI_AS do banco e 9000 mil tabelas.

    Tem algum script para fazer esse processo?

    quarta-feira, 29 de janeiro de 2020 19:00

Respostas

  • Marcos, talvez a solução proposta para outro tópico semelhante sirva para o seu caso. Avalie o seguinte código SQL:

    -- código #2
    -- Autor: José Diz / BH
    
    USE BDdestino;
    
    -- altera modelo de recuperação para simples
    ALTER DATABASE BDdestino set RECOVERY SIMPLE;
    BACKUP DATABASE BDdestino TO DISK = N'NUL';
    
    -- declaração do cursor e variáveis para obter nomes das tabelas
    declare PorTabela cursor 
      for SELECT name, schema_name (schema_id), object_id
            from sys.tables
            where type = 'U'
                  and is_ms_shipped <> 1
            order by schema_id, name
      for read only;
    
    declare @T_name sysname, @T_schema_name sysname, @T_object_id int;
    declare @ComandoSQL nvarchar(max), @erro int, @identity_on bit;
    declare @debug bit = 'false'; -- OPEN PorTabela; -- obtém primeira tabela FETCH next from PorTabela into @T_name, @T_schema_name, @T_object_id; -- while @@FETCH_STATUS = 0 begin -- set @ComandoSQL= space(0); -- ativa IDENTITY_INSERT, caso exista coluna identity na tabela IF exists (SELECT * from sys.columns where object_id = @T_object_id and is_identity = 1) begin set @ComandoSQL+= 'set IDENTITY_INSERT ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' on;' + char(13)+char(10); set @identity_on= 1; end else set @identity_on= 0; -- acrescenta tabela de destino no comando INSERT set @ComandoSQL+= 'INSERT into ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' with (tablock) (' + stuff ((SELECT ', ' + quotename (C.name) from sys.columns as C where C.object_id = @T_object_id order by C.name for xml path(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') + ') '; -- acrescenta tabela de origem no comando INSERT set @ComandoSQL+= 'SELECT '       + stuff ((SELECT ', ' + quotename (C.name)
    from sys.columns as C where C.object_id = @T_object_id order by C.name for xml path(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') + ' from BDorigem.' + quotename (@T_schema_name) + '.' + quotename (@T_name) + '; '+ char(13)+char(10); -- desativa IDENTITY_INSERT, caso tenha sido ativado IF @identity_on = 1 set @ComandoSQL+= 'set IDENTITY_INSERT ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' off;' + char(13)+char(10); -- executa o comando SQL PRINT '=========='; PRINT 'processando tabela ' + @T_schema_name + '.' + @T_name; IF @debug = 1 PRINT @ComandoSQL; set @erro= -1; EXECUTE @erro= sp_executesql @ComandoSQL; IF @erro <> 0 begin PRINT 'Ocorreu erro. Verifique'; PRINT @ComandoSQL; IF @debug = 0 BREAK; end; -- CHECKPOINT; -- lê próxima tabela FETCH next from PorTabela into @T_name, @T_schema_name, @T_object_id; -- end; CLOSE PorTabela; DEALLOCATE PorTabela; -- retorna modelo de recuperação para COMPLETO ALTER DATABASE BDdestino set RECOVERY FULL;

    Substitua BDdestino pelo nome do banco de dados de destino e BDorigem pelo nome do banco de dados de origem.

     


    José Diz     Belo Horizonte, MG - Brasil     [ Para onde foi o 23:59:59.999?! ]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Sugerido como Resposta José Diz quarta-feira, 5 de fevereiro de 2020 14:14
    • Marcado como Resposta Marcos Paulo - Xcelent quarta-feira, 5 de fevereiro de 2020 18:26
    • Editado José Diz quarta-feira, 5 de fevereiro de 2020 21:00
    quinta-feira, 30 de janeiro de 2020 20:53

Todas as Respostas

  • Marcos,

    Antes de realizar a alteração, gostaria de entender qual é a sua necessidade, pois a alteração do collation pode gerar impactos nos seus dados. Para saber mais sobre os collations e possíveis impactos, recomendo acessar a documentação oficial: Set or Change the Database Collation

    Para alterar o collation de um específico banco de dados, você pode utilizar a instrução Alter Database, veja o exemplo:

    ALTER DATABASE SeuBancoDeDados
    COLLATE Latin1_General_CI_AS
    Go

    No caso das tabelas, podemos elaborar um Script para realizar esta alteração, mas na verdade o collation não se aplica a tabela e sim as colunas que utilizam os tipos de dados: charvarchartextncharnvarchar, e ntext.

    Então, se for uma necessidade somente de mudar a apresentação de um dado em tela, podemos fazer uso da opção Collate no comando Select.

    Aproveito para compartilhar um exemplo de como podemos alterar o collation de uma respectiva coluna em uma determinada tabela:

    -- Alterando o Collate na Tabela Users para a Coluna Password --
    Alter Table Users
     Alter Column [Password] Varchar(12) Collate SQL_Latin1_General_CP1_CS_AS


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 29 de janeiro de 2020 21:14
    Moderador
  • Marcos Paulo, o banco de dados e as tabelas de destino já existem ou é necessário criá-los, à medida que se realiza a conversão?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quarta-feira, 29 de janeiro de 2020 23:25
  • Olá Junior, agradeço sua atenção.

    Então, instalaram SQL SERVER 2012 usando collation errado e vamos atualizar sistema é requisito que collation esteja correto.

    quinta-feira, 30 de janeiro de 2020 02:11
  • José, tenho banco teste para validar esse processo

    quinta-feira, 30 de janeiro de 2020 02:12
  • Marcos,

    Então, mas alterar o collation de 9000 é um trabalho grandioso. Pois como destaquei o collation se aplica no nível de coluna e não tabela.

    Não seria mais indicado inicialmente pensar alterar o collation do servidor?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    quinta-feira, 30 de janeiro de 2020 17:03
    Moderador
  • Marcos, dúvidas:

    • qual é o COLLATE do banco de dados de origem?
    • qual é o COLLATE da instância onde está o banco de dados de origem?
    • alguma coluna do banco de dados de origem possui COLLATE diferente do COLLATE do banco de dados de origem?

    ---

    Código SQL para obter COLLATE de instância e banco de dados:

    -- código #1
    USE nome_banco;

    SELECT SERVERPROPERTY('collation') as [COLLATE instância], DATABASEPROPERTYEX('nome_banco', 'Collation') as [COLLATE banco];

    Substitua nome_banco pelo nome do banco de dados de origem.

     

    Lembre-se de marcar esta resposta se ela te ajudou a resolver o problema.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quinta-feira, 30 de janeiro de 2020 21:06
    quinta-feira, 30 de janeiro de 2020 20:13
  • Marcos, talvez a solução proposta para outro tópico semelhante sirva para o seu caso. Avalie o seguinte código SQL:

    -- código #2
    -- Autor: José Diz / BH
    
    USE BDdestino;
    
    -- altera modelo de recuperação para simples
    ALTER DATABASE BDdestino set RECOVERY SIMPLE;
    BACKUP DATABASE BDdestino TO DISK = N'NUL';
    
    -- declaração do cursor e variáveis para obter nomes das tabelas
    declare PorTabela cursor 
      for SELECT name, schema_name (schema_id), object_id
            from sys.tables
            where type = 'U'
                  and is_ms_shipped <> 1
            order by schema_id, name
      for read only;
    
    declare @T_name sysname, @T_schema_name sysname, @T_object_id int;
    declare @ComandoSQL nvarchar(max), @erro int, @identity_on bit;
    declare @debug bit = 'false'; -- OPEN PorTabela; -- obtém primeira tabela FETCH next from PorTabela into @T_name, @T_schema_name, @T_object_id; -- while @@FETCH_STATUS = 0 begin -- set @ComandoSQL= space(0); -- ativa IDENTITY_INSERT, caso exista coluna identity na tabela IF exists (SELECT * from sys.columns where object_id = @T_object_id and is_identity = 1) begin set @ComandoSQL+= 'set IDENTITY_INSERT ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' on;' + char(13)+char(10); set @identity_on= 1; end else set @identity_on= 0; -- acrescenta tabela de destino no comando INSERT set @ComandoSQL+= 'INSERT into ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' with (tablock) (' + stuff ((SELECT ', ' + quotename (C.name) from sys.columns as C where C.object_id = @T_object_id order by C.name for xml path(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') + ') '; -- acrescenta tabela de origem no comando INSERT set @ComandoSQL+= 'SELECT '       + stuff ((SELECT ', ' + quotename (C.name)
    from sys.columns as C where C.object_id = @T_object_id order by C.name for xml path(''), TYPE).value('.', 'varchar(max)'), 1, 2, '') + ' from BDorigem.' + quotename (@T_schema_name) + '.' + quotename (@T_name) + '; '+ char(13)+char(10); -- desativa IDENTITY_INSERT, caso tenha sido ativado IF @identity_on = 1 set @ComandoSQL+= 'set IDENTITY_INSERT ' + quotename (@T_schema_name) + '.' + quotename (@T_name) + ' off;' + char(13)+char(10); -- executa o comando SQL PRINT '=========='; PRINT 'processando tabela ' + @T_schema_name + '.' + @T_name; IF @debug = 1 PRINT @ComandoSQL; set @erro= -1; EXECUTE @erro= sp_executesql @ComandoSQL; IF @erro <> 0 begin PRINT 'Ocorreu erro. Verifique'; PRINT @ComandoSQL; IF @debug = 0 BREAK; end; -- CHECKPOINT; -- lê próxima tabela FETCH next from PorTabela into @T_name, @T_schema_name, @T_object_id; -- end; CLOSE PorTabela; DEALLOCATE PorTabela; -- retorna modelo de recuperação para COMPLETO ALTER DATABASE BDdestino set RECOVERY FULL;

    Substitua BDdestino pelo nome do banco de dados de destino e BDorigem pelo nome do banco de dados de origem.

     


    José Diz     Belo Horizonte, MG - Brasil     [ Para onde foi o 23:59:59.999?! ]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Sugerido como Resposta José Diz quarta-feira, 5 de fevereiro de 2020 14:14
    • Marcado como Resposta Marcos Paulo - Xcelent quarta-feira, 5 de fevereiro de 2020 18:26
    • Editado José Diz quarta-feira, 5 de fevereiro de 2020 21:00
    quinta-feira, 30 de janeiro de 2020 20:53