只需添加
+ CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END
另外,您需要添加CHARACTER_MAXIMUM_LENGTH
到nvarchar
。
至于存储过程中的参数,我最好手动进行。
完整查询
SELECT cmd = 'alter table [' + c.table_schema + '].[' + c.table_name
+ '] alter column [' + c.column_name + '] nvarchar('
+CASE WHEN CHARACTER_MAXIMUM_LENGTH<=4000
THEN CAST(CHARACTER_MAXIMUM_LENGTH as varchar(10)) ELSE 'max' END+')'
+ CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END,*
FROM information_schema.columns c
WHERE c.data_type='varchar'
ORDER BY CHARACTER_MAXIMUM_LENGTH desc