December 17, 2012 at 10:57 am
Hi,
We have a SW that is made in VB6 so it doesn't support UNICODE, so I want to change all the NVARCHAR, NCHAR columns to VARCHAR and CHAR type.
Is there any easy way to do this ?
So far I came across with FKs, Indexes, Default and check Constraints and views with SCHEMABINDING and I'm trying to write a script to generate the create and drop of FKs, Idx (all), DF and CHK constraints...
Thanks,
Pedro
December 17, 2012 at 11:30 am
Before worrying about how to script it, you have to worry about the data. If you know beyond a shadow of a doubt that NONE of the characters in ANY of these fields are actually UNICODE specific, then you could just script the whole thing, piece by piece (as painful as that might be), and make the change. On the other hand, if you have any genuine UNICODE data, you have an entirely different and far more complicated problem. Also, if any such UNICODE data is indexed in any way, query results may change for existing logic, so you really need to know for sure that you don't have any UNICODE data.
Assuming you don't have any UNICODE data, there's probably no simple and easy way to script the whole thing, and by the time you figure out how to automate the process, you could probably have done the scripting work piece by piece in less time. If the quantity of things to change is actually truly large in quantity, you can look at INFORMATION_SCHEMA.COLUMNS to find all the columns with the NCHAR, NVARCHAR, and NTEXT data types, and that might lead to some easy level of automation.
Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 17, 2012 at 12:59 pm
Hi,
There's no UNICODE data for sure... but there are 5506 columns that need to be converted, involving 2013 indexes and 2740 FK.... So manually isn't an option.
I've already managed to get a script for dropping and recreating all the indexes, fks, default constraints and check constraints... the views with schemabinding are only 4 so these I'll do it manually..
Does the Management Studio script manager generate the scripts for the dependent objects and not the tables, since I don't want to drop and create the tables?!
Thanks,
Pedro
December 17, 2012 at 1:26 pm
You should be able to get Drop and Create scripts for the views that way, without it affecting the tables.
If at all concerned about data loss, take a copy of the database, run the script on the copy, then use something like RedGate Data Compare on the two copies. I haven't tried that in this kind of scenario, but I think it should work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 17, 2012 at 2:10 pm
PiMané (12/17/2012)
Hi,There's no UNICODE data for sure... but there are 5506 columns that need to be converted, involving 2013 indexes and 2740 FK.... So manually isn't an option.
I've already managed to get a script for dropping and recreating all the indexes, fks, default constraints and check constraints... the views with schemabinding are only 4 so these I'll do it manually..
Does the Management Studio script manager generate the scripts for the dependent objects and not the tables, since I don't want to drop and create the tables?!
Thanks,
Pedro
Well that certainly calls for the automation. As GSquared indicates doing this on a copy would sure be a good thing to do, but I don't see any reason why you couldn't write a query to script an ALTER TABLE / ALTER COLUMN combination, based on the info in INFORMATION_SCHEMA.COLLUMNS, to provide for making the table changes.
As to the scripting from within SSMS, I think it generates the script for the specific object, and in the case of tables, primary key definitions, but not for dependent objects, so you can script the 4 views separately, and you already have your indexes, constraints, and foreign keys. Let us know how this goes...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 17, 2012 at 4:00 pm
Since you have to ALTER columns only one at a time (why, MS, why??), I'd create a new table and INSERT every row into it, converting every column at one time (and keeping the data compact).
Then, create the same indexes as on the original table.
Finish up by dropping the original table, renaming the new table to the original name, and recreating any views, etc..
Edit: I should have asked "Why, ANSI, why??", since MS is just following the ANSI standard.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 17, 2012 at 6:01 pm
ScottPletcher (12/17/2012)
Since you have to ALTER columns only one at a time (why, MS, why??), I'd create a new table and INSERT every row into it, converting every column at one time (and keeping the data compact).Then, create the same indexes as on the original table.
Finish up by dropping the original table, renaming the new table to the original name, and recreating any views, etc..
Edit: I should have asked "Why, ANSI, why??", since MS is just following the ANSI standard.
Depending on whether or not the disk space is available to just create new tables might determine whether this method would be practical. I do think that if done without recreating the tables, that there may well be considerable fragmentation that could take place if disk space is tight enough, but sometimes, that's the only option. I do like your idea though, as it would likely avoid the fragmentation issues that could otherwise arise.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 18, 2012 at 3:04 am
ScottPletcher (12/17/2012)
Since you have to ALTER columns only one at a time (why, MS, why??), I'd create a new table and INSERT every row into it, converting every column at one time (and keeping the data compact).Then, create the same indexes as on the original table.
Finish up by dropping the original table, renaming the new table to the original name, and recreating any views, etc..
Edit: I should have asked "Why, ANSI, why??", since MS is just following the ANSI standard.
cause the database has 1294 tables and all of them need to convert columns... and the database is 10G (this one), other customers can have bigger databases....
December 18, 2012 at 9:17 am
PiMané (12/18/2012)
ScottPletcher (12/17/2012)
Since you have to ALTER columns only one at a time (why, MS, why??), I'd create a new table and INSERT every row into it, converting every column at one time (and keeping the data compact).Then, create the same indexes as on the original table.
Finish up by dropping the original table, renaming the new table to the original name, and recreating any views, etc..
Edit: I should have asked "Why, ANSI, why??", since MS is just following the ANSI standard.
cause the database has 1294 tables and all of them need to convert columns... and the database is 10G (this one), other customers can have bigger databases....
A script could be generated to do the whole thing either way.
The bigger the db, the more you save by doing all columns at once. Every ALTER statement is a full scan of the table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 4, 2014 at 1:48 am
Hi,
I know this is a very old post, but out of curiosity, how did you determine that data in the nvarchar, nchar columns does not have unicode specific characters?
I also have a similar scenario of converting all NVARCHAR columns to VARCHAR and would like to make sure that there are no actual unicode characters in the nvarchar columns before altering the columns.
February 4, 2014 at 3:22 am
Yusuf Ali Bhiwandiwala (2/4/2014)
Hi,I know this is a very old post, but out of curiosity, how did you determine that data in the nvarchar, nchar columns does not have unicode specific characters?
I also have a similar scenario of converting all NVARCHAR columns to VARCHAR and would like to make sure that there are no actual unicode characters in the nvarchar columns before altering the columns.
Our VB6 controls don't support UNICODE so the database doesn't have them.
If you want to check if there's any unicode strings on your database you can compare the data with a cast to VARCHAR:
CREATE TABLE tbl (
ID INT,
Name NVARCHAR(100),
Address NVARCHAR(100)
)
...
SELECT ID FROM tbl WHERE Name <> CAST(Name AS VARCHAR(100)) OR Address <> CAST(Address AS VARCHAR(100))
Pedro
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply