nvarchar to varchar
-
This is my first post . . . I need to step through a 1000 fields that are a mix of nvarchar and other data types and convert only the nvarchar to varchar. Could someone make me a little sql sample of how to find and replace these datatypes in one table. Thanks!
-
OK this is to help you with an starting point
select 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' varchar(' + cast( CHARACTER_MAXIMUM_LENGTH as varchar(4) ) + ') '+ CASE WHEN IS_NULLABLE = 'No' THEN ' NOT NULL' ELSE ' NULL'
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_TYPE = 'nvarchar' AND TABLE_NAME = 'YourTableName'
Make sure QA have output in text then copy and paste the generated text in QA query window and voila!
Note: I am not checking for defaults nor constraints on those columns if any of those are present you will need to drop them first
Cheers,
* Noel -
Noel - before you wag your finger disapprovingly at this - I know that directly manipulating system tables in not recommended..but for convenience it can't be beat..
update syscolumns set xusertype = 167 where name in (select sc.name from syscolumns sc inner join sysobjects so on sc.id = so.id where so.name = 'tblName' and sc.xusertype = 231)
**ASCII stupid question, get a stupid ANSI !!!** -
hehe
you knew that I was coming don't you There is a problem with that approach. Eventhough the catalog information is updated NO Data conversion took place and that means that corruption may happen when interpreting the content because nvarchar uses two bytes for each character. That being said IF and only IF the characters on those columns are not in the ones that use the actual two bytes then you may be OK. The other problem is that the defaults and constraints that could have been specified are compiled with the wrong datatype after you run that update so I leave to you the analisys of how 'convenient' is that
* Noel -
system tables updates are pretty 'cool' and usually very efficient ... but it's even 'cooler' when you know when NOT to use them ... every one I've seen on this site to date omits the most important part of any adhoc update ...
begin tran
your system updates
selects or whatever to check things out
--commit tran
rollback tran
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
All that you say is true oh wise one!
However, I think back to when I was first handed my present database (4 years ago) when every single non-numeric datatype was specified as nvarchar (much to the dismay of the conservationist and minimalist in me)...usually (at least in my experience) one switches from nvarchar to varchar only when use of nvarchar is a result of ignorance (as was the case with my predecessors) and this was a handy method for me to use....guess I was also lucky in that it didn't affect the data adversely (& I also had a backup of a backup of a backup before making changes)..
Rudy - point noted - thanks!
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks - it works great Noel. I just changed it a bit . . . column_type to data_type and I don't need the case statement. And, if you have time to answer another newbie question - how would you execute the generated text immediately? My guess is to redirect it to a stored procedure and execute it.
select 'ALTER TABLE owner.' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' varchar(' + cast( CHARACTER_MAXIMUM_LENGTH as varchar(4) ) + ') ' + 'Null'
FROM INFORMATION_SCHEMA.COLUMNS where DATA_TYPE = 'nvarchar' AND TABLE_NAME = 'TEST'
results to text:
ALTER TABLE owner.TEST ALTER COLUMN AHEATINGSYSTEMTYPE varchar(50) Null
ALTER TABLE owner.TEST ALTER COLUMN AMISCSQFTADJREASON varchar(16) Null
(2 row(s) affected)
What is the syntax to execute it without pasting it back to QA? I'm not a database admin . . . as you can tell. thanks again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply