How to Change Datatype of All Columns in SQL Server A Comprehensive Guide

To change the datatype of all columns in SQL Server, you can use the ALTER TABLE statement along with the ALTER COLUMN clause.

Here is an example of how you can change the datatype of all columns in a table named “myTable” to the new datatype “newDatatype”:

ALTER TABLE myTable
ALTER COLUMN column1 newDatatype,
ALTER COLUMN column2 newDatatype,
ALTER COLUMN column3 newDatatype,
...

Replace “column1”, “column2”, “column3” with the actual names of the columns in the table, and “newDatatype” with the new datatype that you want to use.

Alternatively, if you want to change the datatype of all columns in all tables in your database, you can use a   dynamic SQL query like this:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 'ALTER TABLE ' + QUOTENAME(schema_name(t.schema_id)) + '.' + QUOTENAME(t.name) + ' ALTER COLUMN ' + QUOTENAME(c.name) + ' newDatatype;'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.system_type_id <> newDatatype

EXEC sp_executesql @sql

Replace “newDatatype” with the new datatype that you want to use. This query generates a dynamic SQL statement that alters the datatype of all non-system columns in all tables in your database.