I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.
I have succesfully edited a single column with the following code:
ALTER TABLE PudfTest
DISCHARGE VARCHAR(6) NULL
However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.
select 'alter table ' + table_name + ' alter column ' + column_name + ' ' +
when 'int' then 'varchar(25)'
when 'datetime' then 'char(10)'
end + ' null'