Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asm
Posting Yak Master
140 Posts |
 Posted - 2010-09-11 : 11:14:58
|
| Hi,I want to change the datatype from varchar to nvarchar in all tables of database.Guide me how to do that through query.Thanksasm |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-12 : 03:38:10
|
try this:alter table MyTable alter column MyColumn nvarchar(20) where MyTable is the name of your table and MyColumn is the column with previous type varchar(10) that you are changing it to nvarchar(20). I also double the size of the column. |
 |
|
|
asm
Posting Yak Master
140 Posts |
Posted - 2010-09-12 : 05:33:47
|
| Sorry, i was not clarify my question in details:--In my database more than 500+ tables and each tables contains the varchar(..) datatype but i want now to replace all the varchar(..) to nvarchar(..)This will done due to storing the multilingual concept in my application.So i want in one shot i can do it... else opening a each table to doing manually it will take a lot of time.thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-09-12 : 06:11:04
|
| There's no one-shot way of doing this. You need to run the ALTER TABLE... ALTER COLUMN for each table and column you want to change. It should be possible (though not trivial) to use the system tables to generate the ALTER TABLE statments. Won't be quick to write, but will be faster than writing each manually.Or you can look at something like Redgate's SQLRefactor and see if it does what you want.--Gail ShawSQL Server MVP |
|
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-09-13 : 00:26:44
|
| @asm,since i'm managing also several languages in different databases, i suggest you to rethink at the beginning what are you trying to achieve, that do you need to change, etc. etc. Altering varchar to nvarchar will 100% not solve all the issues. you have as well collation that will be indigenious to your original table and might need changes as well, local settings as @@language, dateformat, @@datefirst, etc.I suggest you to thoroughly test this things in advance before you starting to write a dynamic sql query for altering all the columns in your database.you might end up with completelly new data model.and as for your case, i would write dynamic sql query that would alter your columns with one run. retrieve all the columns either from information_schema.columns or from sys.columns and alter the needed information. the time needed will not be, you writing this query, but the system converting fields.p.s.: before you enter this venture, make sure your disk has enough space and that the database in - hope not - production one or at least you do not have any direct applications against this table. |
|
|
|
|
|
|
|
|
Datatype