Any script to convert nvarchar to varchar

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Any script to convert nvarchar to varchar

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-14 : 14:14:39

I have a database where we have lots of nvarchar columns tables which are created during some import process of ms access db into sql server and then some wrong practicing of creating nvarchar new columns.
I need a script which will changes all occurence of nvarchar into varchar (first in tables then in all possible stored procedures, table value types, funcitons, e.t.c)

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-11-14 : 15:00:00

Why do you feel it is wrong practice to create nvarchar?

If you don't have the passion to help people, you have no passion

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-14 : 15:19:12

One thing is that my data is not Unicode but just a simple ASCII data.
I am importing them from simple text file and it is some time have 1000's to 100,000 of data there.
Also there is a limit on index size limit which exceeded due to useless data type while varchar is quite enough for me

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-14 : 15:20:32

You can use this to generate the alter statements to change the data types of the tables.
SELECT 'ALTER TABLE ' + Table_Name + ' ALTER COLUMN ' + column_name + ' varchar(' + convert(varchar(20),CHARACTER_MAXIMUM_LENGTH) +')'
FROM information_schema.columns
WHERE DATA_TYPE = 'nvarchar'

Jim

Everyday I learn something that somebody else already knew

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-11-14 : 15:22:13

so are you importing from access or text file? again why do you feel nvarchar is useless? what is your technical reason for saying that?

If you don't have the passion to help people, you have no passion

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-14 : 15:23:39

quote:
Originally posted by yosiasz

so are you importing from access or text file? again why do you feel nvarchar is useless? what is your technical reason for saying that?

It is pointless to use nvarchar if your data doesn't require it. It takes up twice as much space as varchar.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-15 : 00:26:15

Thanks every one,
following post have help me a lot in same question
[url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/745b6361-bea3-4793-a1a7-4a1ee1b54ecf/?prof=required[/url]

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources