unicode
-
the language needs in my database have changed and now i need to change all data types=varchar into nvarchar. does someone know how I can do this without having to go manually through all my tables and columns? is there maybe a script?
thank you
-
You still need some manual works in my suggestion.
1. Backup database and rename it.
2. Generate scripts for all database objects.
3. Replace varchar with nvarchar in the scripts.
4. Create new database as you need.
5. Run above scripts to create objects.
6. DTS/Import data from old database.
-
May help a bit!
Should be able to generate script to automate, keep mind constraints, indexes,etc.
Full backup before.
Select Object_Name(Id) as [Table],
name as [Column],
type_name(xusertype) as Type
From SysColumns
Where Type_Name(xusertype) in ('varchar','char') And
ID in (select ID
From SysObjects
Where xtype='U')
Order by Object_Name(Id),Name
Select Object_Name(Id) as [StoredProcedure],
name as [Parameter],
type_name(xusertype) as Type
From SysColumns
Where Type_Name(xusertype) in ('varchar','char') And
ID in (select ID
From SysObjects
Where xtype='P')
Order by Object_Name(Id),Name
-
Thanks a lot for the replies guys. I am not so savvy with DBs. Could you please tell me a little more detailed how I can run this below script? And what do I need to replace in the script?
My database consists of about 150 tables, of which each has five coumns, and four of those (named English, Spanish, French and German) need to be changed from varchar to nvarchar.
I really appreciate your help
-
Open QUery Analyzer.
Copy/paste the script and execute, it consist of two select statement, harmless. Make sure you have selected the relevant database.
The script should produce a list of the tables with varchar/char column, other words candidates to be changed.
The next step is to generate script to alter the tables. Can you post an example of a table.
Just remember to do a full backup before changing.
-
Thanks again.
Your script worked and showed all my tables with vachars in them.
I have about 150 tables, and they are all set up the same way. They have one column with ascending numbers. This column is the primary key and of type int called 'ID'. The other four columns are 4 varchars with text, called 'English', 'Spanish', 'French', 'German'.
Column Name Data type Length Allow Nulls
------------------------------------------
ID int 4
English varchar 100 check
Spanish varchar 100 check
French varchar 100 check
German varchar 100 check
-
BACKUP YOUR DATABASE BEFORE ATTEMPT ANY OF THE FOLLOWING.
Create a dummy table, like JNK, and copy a live table's data into it.
The script included will generate code to change the columns on at a time.
Execute the script below in Query Analyzer.
Then copy the results window into the top window and execute it.
Now run the script for JNK, if it succeed then run the next lot, maybe table by table.
Hopefully there is not a million rows in a table. Assumption there is 'ample' free space for the datbase and logs.
LASTLY MAKE A BACKUP BEFORE ATTEMPTING TO RUN THIS LOT.
Select 'ALTER TABLE '+Object_Name(Id)+
' ALTER COLUMN '+name+' N'+
type_name(xusertype)+' ('+
cast(COLUMNPROPERTY( Id,name,'PRECISION') as varchar(5))+')'+char(13)+char(10)+'Select ''Table '+
Object_Name(Id)+'-> Column '+Name+' Changed '',Replicate('' '',1024)'+Char(13)+char(10)+'GO'
From SysColumns
Where Type_Name(xusertype) in ('varchar','char') And
ID in (select ID From SysObjects Where xtype='U')
Order by Object_Name(Id),Name
-
OOPS!
Then copy the results window into the top window and execute it.
Now run the script for JNK, if it succeed then run the next lot, maybe table by table.
Change these two lines around in the previous post.
-
It worked like a charm! Thank you so much.
-
Don't forget change the 'varchar' in temp tables in your stored procedures, functions, user defined data type if you have any.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply