2

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.

Nezam
  • 4,122
  • 3
  • 32
  • 49

1 Answers1

4

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
Igor Borisenko
  • 3,806
  • 3
  • 34
  • 49
  • can you please modify the full query and post it in your answer to be crystal clear. Thanks – Nezam Jul 29 '15 at 06:39
  • and i need to change the non nullable `varchar` to non nullable `nvarchar` too. Will this query work ? – Nezam Jul 29 '15 at 06:53
  • @Nezam I edited the answer. " i need to change the non nullable `varchar` to non nullable `nvarchar`". Yes, if you mean `NOT NULL` – Igor Borisenko Jul 29 '15 at 07:12
  • Thanks a lot! and the size of 8000 will not be an issue of nvarchar ? – Nezam Jul 29 '15 at 07:17
  • @Nezam Yes, it will be. `Nvarchar` cannot be longer than 4000, for this cases you need to use `nvarchar(max)`. – Igor Borisenko Jul 29 '15 at 07:44
  • so suppose if this generated query has somewhere `nvarchar(8000)` , what will be the possible outcome of this execution ? – Nezam Jul 30 '15 at 08:24
  • @Nezam You'll get the error. I edited the full script for this scenario: CHARACTER_MAXIMUM_LENGTH greater than 4000 will be replaced with MAX. – Igor Borisenko Jul 30 '15 at 08:56
  • one of the queries which this query returned was `alter table [dbo].[Merchant] alter column [Address] nvarchar(-1)` because the column was `varchar(MAX)` already. – Nezam Aug 05 '15 at 06:33