Início / dba / Perguntas / 47797
Accepted
Federico Giust
Asked: 2013-08-09 08:11:31 +0800 CST

Converta todas as colunas ntext para nvarchar (max)

  • 772

Estou tentando criar um procedimento armazenado para converter todas as ntextcolunas do meu banco de dados em arquivos nvarchar(max).

Este é o código

ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;
 
  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR 

    SELECT so.name as table_name, sc.name as column_name
      FROM sys.objects so
      JOIN sys.columns sc ON so.object_id = sc.object_id
      JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                        AND stp.name = 'ntext'
     WHERE so.type = 'U' -- to show only user tables
  OPEN tables_cursor
  
  FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC ('ALTER TABLE Eventlogic.dbo.' + @table_name + ' ALTER COLUMN ' + @column_name + ' nvarchar(max);')
    EXEC ('UPDATE Eventlogic.dbo.' + @table_name + ' SET ' + @column_name + '=' + @column_name + ' ')
    SET @count = @count + 1;
    IF @count > 0
      PRINT ('Eventlogic.dbo.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_name, @column_name
  END 
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))  
  
END;

Sempre que tento executá-lo, recebo este erro:

Msg 16924, Nível 16, Estado 1, Procedimento usp_SL_ConvertNtextToNvarchar, Linha 37
Cursorfetch: O número de variáveis ​​declaradas na lista INTO deve corresponder ao das colunas selecionadas.

Temos 338 colunas em tabelas diferentes como ntext, devido ao legado.

Atualizamos para o SQL Server 2008 R2, portanto, gostaríamos de converter essas colunas em arquivos nvarchar(max).

Queremos seguir o conselho aqui ntext vs nvarchar(max) onde Conwell sugere fazer a atualização após a alteração.

O servidor SQL move o texto da estrutura LOB para a tabela (se for menor que 8.000 bytes). Portanto, quando executamos o select novamente com IO STATISTICS, obtemos 0 leituras de LOB.

Qualquer ajuda com esse erro seria ótima.

ATUALIZAR

Código atualizado como Martin mencionou. Na verdade, está alterando apenas o primeiro da lista antes de dar o erro.

2ª ATUALIZAÇÃO

Depois de fazer as alterações no código para corrigir a segunda busca, fechei o SQL Management Studio. Abri o SQL Management Studio novamente e executei e funcionou. Então, obrigado Martin novamente.

Desde já, obrigado.

Federico

stored-procedures sql-server-2008-r2

3 respostas

  1. Embora se você resolver isso antes de ler minha resposta, tornando improvável que seja útil para o seu caso específico (a menos que você precise fazer isso em vários bancos de dados), prefiro a concatenação de strings em vez de cursores para esse tipo de trabalho. Principalmente porque você pode imprimir a string em vez de executá-la, verificando se pelo menos o primeiro valor de 8K parece adequado e correto.

    DECLARE @sql NVARCHAR(MAX) = N'USE Eventlogic;';

SELECT @sql += N'
  ALTER TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
  + ' ALTER COLUMN ' + QUOTENAME(c.name) 
  + CASE WHEN c.system_type_id = 99 THEN ' N' ELSE ' ' END
  + 'VARCHAR(MAX)' + CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END 
  + ';
  UPDATE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) 
    + ' SET ' + QUOTENAME(c.name) + ' = ' + QUOTENAME(c.name) + ';
  PRINT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(REPLACE(t.name,'''','''''')) 
    + '.' + QUOTENAME(c.name) + ' (' 
    + CONVERT(VARCHAR(12), COUNT(*) OVER(PARTITION BY c.[object_id])) 
    + ' columns)'';'
FROM sys.columns AS c
INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE c.system_type_id IN (35,99);

SET @sql += N'
PRINT ''Total columns updated: ' + CONVERT(VARCHAR(12), @@ROWCOUNT) + ''';';

PRINT @sql;
--EXEC sp_executesql @sql;

    Quando estiver satisfeito com o PRINTresultado, remova o comentário EXECe execute-o novamente.

    O script de Jon Seigel oferece funcionalidade adicional (lidando com colunas que participam de índices FULLTEXT).

    • 5
  2. Best Answer

    Ok, eu reescrevi seu SP para você com QUOTENAMEo schemaque @MartinSmith sugeriu. Embora eu não saiba ao certo por que você está usando um SP para isso. Parece que um pedaço de código autônomo sem o wrapper SP faria mais sentido para algo assim.

    Você usa QUOTENAMEpara lidar com caracteres estranhos no nome. Por exemplo Table-Test, é um nome de tabela válido, mas não funcionará em seu código, a menos que você coloque []s em torno dele [Table-Test]. QUOTENAMEcuida disso para você. Ele também lida se você tiver ']'s em seu nome também. Geralmente é considerada uma prática recomendada quando você está fazendo sql dinâmico para incluir schemase usar QUOTENAMEquando necessário.

    USE [EventLogic]
GO

/****** Object:  StoredProcedure [dbo].[usp_SL_ConvertNtextToNvarchar]    Script Date: 08/08/2013 16:28:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar]
AS
/*
*/
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql nvarchar(max)
  DECLARE @table_schema nvarchar(128)
  DECLARE @table_name nvarchar(128)
  DECLARE @column_name nvarchar(128)
  DECLARE @totalCount int
  DECLARE @count int

  SET @totalCount = 0;
  SET @count = 0;
  SET @sql = '';

  -- Eventlogic
  DECLARE tables_cursor CURSOR FOR 
    SELECT SCHEMA_NAME(so.schema_id) AS table_schema, so.name as table_name, 
            sc.name as column_name
      FROM sys.objects so
      JOIN sys.columns sc ON so.object_id = sc.object_id
      JOIN sys.types stp ON sc.user_type_id = stp.user_type_id
                        AND stp.name = 'ntext'
     WHERE so.type = 'U' -- to show only user tables
  OPEN tables_cursor

  FETCH NEXT FROM tables_cursor INTO @table_schema, @table_name, @column_name
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @sql = 'ALTER TABLE Eventlogic.'+QUOTENAME(@table_schema)+'.' + 
                    QUOTENAME(@table_name) + ' ALTER COLUMN ' + 
                    QUOTENAME(@column_name) + ' nvarchar(max);'
    EXEC sp_executesql @sql
    --PRINT @sql

    SET @sql = 'UPDATE Eventlogic.'+QUOTENAME(@table_schema)+'.' + 
                    QUOTENAME(@table_name) + ' SET ' + 
                    QUOTENAME(@column_name) + '=' + 
                    QUOTENAME(@column_name) + ' '
    EXEC sp_executesql @sql
    --PRINT @sql

    SET @count = @count + 1;
    IF @count > 0
      PRINT ('Eventlogic.'+@table_schema+'.' + @table_name + '.' + @column_name + ' ' + CAST(@count AS nvarchar(10)))
    SET @totalCount = @totalCount  + @count;
    FETCH NEXT FROM tables_cursor INTO @table_schema, @table_name, @column_name
  END 
  CLOSE tables_cursor
  DEALLOCATE tables_cursor
  PRINT ('Total columns updated: ' + CAST(@totalCount AS nvarchar(10)))  

END;

GO
    • 1

  3. Abaixo está uma variação que irá imprimir os demonstrativos para você analisar e então você poderá executá-los:

    DECLARE @CurrentDataType VARCHAR(max)
DECLARE @DataTypeToChange VARCHAR(max)

SET @CurrentDataType = 'datetime'       ---CHANGE HERE !!
SET @DataTypeToChange = 'smalldatetime' ---CHANGE HERE !!

SELECT 'alter table ' + OBJECT_SCHEMA_NAME(T.[object_id], DB_ID()) + '.' + T.[name] + CHAR(10) + ' alter column ' + C.[name] + ' ' + @DataTypeToChange + ' ' + CASE 
        WHEN C.[is_nullable] = 0
            THEN 'not null '
        ELSE ' null '
        END
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] C ON T.[object_id] = C.[object_id]
INNER JOIN sys.[types] DTY ON C.[system_type_id] = DTY.[system_type_id]
    AND C.[user_type_id] = DTY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
    AND DTY.NAME = '' + @CurrentDataType + ''
ORDER BY T.[name]
    ,C.[column_id]
    • 1

relate perguntas