Deutsch

Englisch

T-SQL

Deutsch

SQL Script zum automatischen Konvertieren der Text-Datentypen.

Da die SQL-Datentypen "text" und "ntext" endgültig von Microsoft abgekündigt sind und da dies auch inzwischen Software-Hersteller wie Sage für deren Produkte verlangen, nachfolgend ein SQL-Script, welches die Konvertierung für eine gesamte Datenbank durchführt.

Konvertiert werden solche Tabellen, die einem bestimmten Suchmuster entsprechen und natürlich auch lediglich solche Felder, die noch nicht umgestellt sind.

Anmerkung: Im "Catch"-Block sind Variablen für eine weitere Fehlerbehandlung eingebaut (als Beispiel), falls Bedarf besteht, weitere Fehlerdetails auszugeben. Natürlich sind auch andere Wege der Fehlerbehandlund denkbar. Es ist nur ein Beispiel.

Englisch

SQL script for automatic conversion of text data types.

Since the SQL data types "text" and "ntext" have been discontinued by Microsoft for good and since software manufacturers such as Sage now also require this for their products, the following is an SQL script that performs the conversion for an entire database.

Tables that match a specific search pattern are converted, and of course only those fields will be converted which have not been converted yet.

Note: Variables are built into the "Catch" block for further error handling (as an example) if there is a need to output more error details. There are other approaches how to handle errors, of course. It is just an example.

T-SQL

-- * Selects all tables which contain "TODO" in the name.
-- * Converts all data types "text" to "varchar(max)" and "ntext" to "nvarchar(max)"
 
-- * See "TODO" markings in script !
 
-- Originally Created: 2021-08-02
 
-- Define variables
DECLARE @sTbl NVARCHAR(2048)
DECLARE @sCol NVARCHAR(2048)
DECLARE @sType NVARCHAR(50)
 
-- Define cursor
DECLARE cSAGINTTextDataTypeConvert CURSOR FOR
  SELECT
  A.[TABLE_NAME]
  ,A.[COLUMN_NAME]
  ,A.[DATA_TYPE] 
  FROM INFORMATION_SCHEMA.COLUMNS a WITH(NOLOCK)
  LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS bviews WITH(NOLOCK)
  ON a.TABLE_CATALOG = bviews.TABLE_CATALOG
  AND a.TABLE_SCHEMA = bviews.TABLE_SCHEMA
  AND a.TABLE_NAME = bviews.TABLE_NAME
  WHERE
  A.[TABLE_NAME] LIKE N'%TODO%' -- TODO: Update table filter according to your needs!
  AND A.[DATA_TYPE] IN (N'text', N'ntext')
  AND bviews.[TABLE_NAME] IS NULL -- No views!!
  ORDER BY A.[TABLE_NAME]
  ,A.[COLUMN_NAME]
 
-- Open cursor
OPEN cSAGINTTextDataTypeConvert
 
-- Continue only if data is present
IF @@Cursor_Rows <> 0
BEGIN
 
  DECLARE @lRC INT
  DECLARE @sSQL NVARCHAR(MAX)
  DECLARE @sMsg VARCHAR(1024)
  DECLARE @sNewType NVARCHAR(50)
  DECLARE @lError INT
  DECLARE @sError VARCHAR(4000)
 
  -- Variable inits
  SET @lRC = 0
  SET @sSQL = N''
  SET @sMsg = ''
  SET @sNewType = N''
  SET @lError = 0
  SET @sError = ''

  -- Select first record
  FETCH NEXT FROM cSAGINTTextDataTypeConvert INTO @sTbl , @sCol , @sType
 
  -- Loop all records
  WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
 
    -- Main work
 
    -- PRINT @sTbl + '.' + @sCol
 
    BEGIN TRY
 
      SET @sNewType = N'varchar(max)' 
 
      IF @sType LIKE N'n%' SET @sNewType = N'nvarchar(max)'
 
      IF LEFT(@sCol, 1) <> N'[' SET @sCol = N'[' + @sCol + N']'
 
      IF LEFT(@sTbl, 1) <> N'[' SET @sTbl = N'[' + @sTbl + N']'
 
      IF LEFT(@sTbl, 4) <> N'dbo.' SET @sTbl = N'dbo.' + @sTbl
 
      SET @sSQL = N'ALTER TABLE ' + @sTbl + N' ALTER COLUMN ' + @sCol + N' ' + @sNewType + N' '
 
      PRINT @sSQL
 
      EXEC @lRC = sp_executesql @sSQL
                   
    END TRY
    BEGIN CATCH
 
      SET @lError = ERROR_NUMBER()
      SET @sError = ERROR_MESSAGE()
 
      SET @lRC = 20210801
                   
      PRINT 'Fehler ' + CAST(@lRC AS VARCHAR(50)) + ' bei der Skriptausführung!! ' + @sError
              
      IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION
      END
 
    END CATCH
                        
    IF @lRC <> 0
    BEGIN

      SET @sMsg = CAST(ISNULL(@lRC, 0) AS VARCHAR(50)) + ': Fehler beim Ausführen einer SQL-Anweisung! / Error while executing a SQL statement! ' + ISNULL(@sSQL, '') + ' ' + CAST(ISNULL(@lError, 0) AS VARCHAR(50)) + ' ' + ISNULL(@sError, '')

      SELECT @sMsg

    END
 
    -- Next record
    FETCH NEXT FROM cSAGINTTextDataTypeConvert INTO @sTbl , @sCol , @sType
 
  END
 
  -- Close cursor 
  CLOSE cSAGINTTextDataTypeConvert
 
END
 
-- Free cursor
DEALLOCATE cSAGINTTextDataTypeConvert