SELECT 'ALTER TABLE ['+TABLE_NAME+'] ALTER COLUMN ['+COLUMN_NAME+'] '
+ DATA_TYPE
+ CASE WHEN DATA_TYPE IN('TEXT','NTEXT') THEN '' ELSE
QUOTENAME(CHARACTER_MAXIMUM_LENGTH,'(')
END
+ ' COLLATE Chinese_PRC_CI_AS '
+ CASE IS_NULLABLE WHEN 'NO' THEN ' NOT NULL ' ELSE '' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN('varchar','nvarchar','char','nchar','text','ntext')
and table_name IN ( 'IP_MD_VENDOR','IPLANT_LINE');