如何将db对象数据类型从varchar更改为nvarchar，而不缺省为null？ [英] How to alter db object datatype from varchar to nvarchar without defaulting to null?

问题描述

我有表，存储过程和函数使用 varchar 但是支持阿拉伯文本，我需要将它们更改为 nvarchar 。我有表的这个脚本

  select cmd ='alter table ['+ c.table_schema +']。 .table_name +'] alter column ['+ c.column_name +'] nvarchar'，* from information_schema.columns c其中c.data_type ='varchar'order by CHARACTER_MAXIMUM_LENGTH desc 
  pre> 
 
 
使用这个，我得到一个查询，我可以复制粘贴和执行将varchar更改为nvarchar，但他们默认不可空列包括null这是一个大问题。我需要保留nullable为可空和不可为空的非可空。 
 
 
 
接下来，问题是我必须手动编辑存储过程和函数，以便在需要时实施此更改。有没有一些解决方法脚本atleast安全地改变参数？
 
 
 
注意，没有在数据库中的视图和一些 varchar 
解决方案
 
 
 
请给我一个安全的解决方案。 / div> 
只需添加
  + CASE WHEN IS_NULLABLE ='NO'THEN'NOT NULL'ELSE''END 
  
此外，您还需要将 CHARACTER_MAXIMUM_LENGTH  code> 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 
  
 
I have tables,stored procedures and functions using varchar but to support arabic text i would need to change them to nvarchar. I have this script for tables
select cmd = 'alter table [' + c.table_schema + '].[' + c.table_name + '] alter column [' + c.column_name + '] nvarchar',* from information_schema.columns c where c.data_type='varchar' order by CHARACTER_MAXIMUM_LENGTH desc
Using this i get a query which i can copy paste and execute to change varchar to nvarchar but they default the non nullable columns to include null which is a big issue. I need to retain the nullable as nullable and non nullable as non nullable. 

Next, issue is that i would have to manually edit the stored procedures and functions to implement this change wherever needed. Is there some workaround script for atleast altering the parameters safely ?

Note that do not have views in the database and some varchar declarations have size to 8000 too.

Please give me a safe solution.
解决方案
Just add
+ CASE WHEN IS_NULLABLE='NO' THEN ' NOT NULL' ELSE '' END
Also, you need to add CHARACTER_MAXIMUM_LENGTH to nvarchar.

As for parameters in stored procedures, I'd better did it manually.

EDITED 
Full query
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


                        
这篇关于如何将db对象数据类型从varchar更改为nvarchar，而不缺省为null？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！