How do you convert all varchar columns/variables/parameters to nvarchar type in one go?

Question

I have a database which has nearly 1000 tables. All the textual type field is of varchar type. It has nearly 1000+ stored procedures and functions too. These procedures and functions got their own varchar parameters and varchar variables. I need to convert every varchar type field to nvarchar type in one go in every table, procedures and functions. Of-course I could do it one by one but that would take years to do it manually.

I don't need to change the size. If its varchar(50). I want output like nvarchar(50). No change in size.

Look into this - https://stackoverflow.com/questions/1327548/how-to-change-all-sql-columns-of-one-datatype-into-another — DatabaseCoder, Apr 27 '18 at 04:31
thanks I did search for similar question but i didn't find it. — Anuj Tamrakar, Apr 27 '18 at 04:34
but does it convert the parameters and variables of procedure too? — Anuj Tamrakar, Apr 27 '18 at 04:35
No, Above link solution will not convert parameters and variables of procedure. — DatabaseCoder, Apr 27 '18 at 04:38
For SPs, you can script them out using SSMS and then search/replace using your favourite text editor. You can re-deploy them afterwards. — Alex, Apr 27 '18 at 13:28

score 0 · Answer 1 · answered Apr 30 '18 at 14:01

Of-course I could do it one by one but that would take years to do it manually.

This appears to be the answer to your question, as if 'in one go' would take years in a single transaction then this would have to be executed in digestible chunks based on what maintenance windows you have to do this.

Tables first considering any PK-FK ordering of execution
Views next unless any views are schemabinding, then you'd have to drop view - alter table - recreate view in that order.
Then functions and SP's.

OR

If it's possible, get a copy of your database, apply all of your changes, then do a switch between your current prod database and that one. This assumes you can arrange to make sure all prod data in the current db is in the copy when you do the switch.

How do you convert all varchar columns/variables/parameters to nvarchar type in one go?

1 Answers1