database-schema

Alle Datentypen einer Tabelle in anderen Datentypen ändern

Zum Ausführen des Skripts einfach die beiden Bindestriche vor EXEC sp_executesql @SQL entfernen.

DECLARE @schema NVARCHAR(50)
DECLARE @table NVARCHAR(250)
DECLARE @tableToBeChanged NVARCHAR (250)
DECLARE @column NVARCHAR(250)
DECLARE @oldDataType NVARCHAR(255)
DECLARE @newDataType NVARCHAR(255)
DECLARE @oldDataTypeLength NVARCHAR(50)
DECLARE @sql NVARCHAR(MAX)

SET @oldDataType = 'char'
SET @newDataType = 'nchar'
SET @tableToBeChanged = 'TABLE_NAME'

DECLARE cursorColumns CURSOR FOR

SELECT C.TABLE_SCHEMA
,C.TABLE_NAME
,C.COLUMN_NAME
,C.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS C
INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
AND T.TABLE_TYPE = 'BASE TABLE'
WHERE C.DATA_TYPE LIKE @oldDataType
AND C.TABLE_NAME LIKE '%' + @tableToBeChanged + '%'

OPEN cursorColumns

FETCH NEXT FROM cursorColumns into @schema, @table, @column, @oldDataTypeLength

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER TABLE [' + @schema + '].[' + @table +
'] ALTER COLUMN [' + @column + '] ' + @newDataType + ' (' + @oldDataTypeLength + ')'
--EXEC sp_executesql @sql
PRINT @sql

FETCH NEXT FROM cursorColumns INTO @schema, @table, @column, @oldDataTypeLength
END

CLOSE cursorColumns
DEALLOCATE cursorColumns

[1] (Quelle: https://stackoverflow.com/questions/1327548/how-to-change-all-sql-columns-of-one-datatype-into-another, Abruf am 31.07.2017)

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*