November 23, 2011 at 8:13 am
alter table statement.
i want to change the collation for all the columns in a table. i already have all the columns that need to be changed but currently can only alter 1 column at a time. So a large table with 170 columns that need to be altered will take a while.
Basically i written me a program, walks thru a database changing all of the collations that need to be changed. (Database, Tables & Columns)
November 23, 2011 at 8:48 am
As far as I know, you only have two options:
1) Create a new table with the correct column collations, load the data from the original table into it, and replace the original with it.
2) Write a while loop that uses data from INFORMATION_SCHEMA to dynamically build and execute your ALTER statements
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
November 23, 2011 at 8:56 am
Has to be one at a time, or rebuild the table all at once.
The way I usually do this kind of thing looks like:
select 'alter table dbo.MyTable alter column [' + name + '] ' +
(select name from sys.types where types.system_type_id = columns.system_type_id and name != 'sysname')
+ '(' + cast(max_length as varchar(10)) + ') collate WhateverCollationYouWant ;'
from sys.columns
where object_id = OBJECT_ID('dbo.MyTable')
and system_type_id in (167,175,231,239);
Output the results as text, and you have a script built for you for altering each column.
Copy and paste that into a new connection, validate it, run it, done.
Usually only takes a few seconds to build the query once you get comfortable with it. Intellisense helps on that, of course.
- 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
November 23, 2011 at 8:57 am
pretty much use the looping process i was looking for something along the lines of...
alter table xxxxx
alter column yyyyyy char(10) collate aaaaaaaaaaa,
alter column zzzzzzz char(25) collate aaaaaaaaaaa
well thanks anyway. for now i will just tell clients that it is best to start it after close on Friday and let is run through the weekend. I built my app with a stop/start option/logic, so we can just keep running it on Friday thru Monday until it is all done.
November 24, 2011 at 2:03 am
Or this, which uses INFORMATION_SCHEMA.COLUMNS, so that no joins or subqueries are necessary. Also adds supports for text and varchar(max) data types, nullability, and table schemas. The syntax error at the beginning is deliberate since a firewall here prevents me from posting certain DDL statements.
SELECT
'XLTER TABLE dbo.Mytable ALTER COLUMN [' + COLUMN_NAME
+'] ' + DATA_TYPE
+CASE CHARACTER_MAXIMUM_LENGTH
WHEN 2147483647 THEN '' -- text data type
WHEN -1 THEN '(max)'
ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(10)) + ')'
END
+' COLLATE NewCollation '
+CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
ELSE 'NOT NULL'
END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'dbo'
AND
TABLE_NAME = 'MyTable'
AND
(DATA_TYPE LIKE '%char' OR DATA_TYPE = 'text')
John
November 24, 2011 at 6:12 pm
Well i really do appreciate all the info for changing the collation of columns, but like i said i have already written a delphi app that already does this. My problem is that we have 2 tables that are relatively huge and one of the tables has 208 columns that have/use collation (Char/text types) the 2nd table has 179 collation related columns. My program clicks along at a pretty good pace until it hits these 2 files and that is when it comes to a crawl.
with the first table my program has to issue 208 (alter table ... alter column...) commands which takes a lot of time, then when it hits the 2nd large table it pretty much comes to a halt again.
After I saw that it was dogging down on these 2 tables i went into the console manager and issued the command manually to change the collation of a column in the table1 and then timed it. It took about 25 minutes to complete. So that is why i was looking for a way to alter ALL of the columns in one pass in ONE command.
I don't need a script to change it, my program already does all it needs to do and it works fine. It just that running the alter command 208 times and it takes about 25 minutes is pretty slow.
I was hoping that the alter table/alter column command could actually be use to alter all 208 columns in the same command.
November 25, 2011 at 1:33 am
Is this something you do regularly, or is it just a one-off? Have you considered using COLLATE clauses in your code instead of changing the structure of your database?
John
November 27, 2011 at 7:10 am
not a regular thing but more than just a one time issue. i have about 50 customers to run this for.
of those 50 about 30 will have large amounts of rows in the 2 problem tables.
November 27, 2011 at 7:54 am
If it's a performance issue you might consider to recreate the table with the proper collation and copy the data into the new table (as already suggested). Rename the table when you're done. Most probably faster than the separate ALTER commands on a column base.
As to my knowledge you can't issue a single command to alter more than one column.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply