Changing COLLATION on a table

Hi
Can any of you help with the syntax for changing the COLLATION on all
columns in a table. I have no problem in changing the collation for the
database itself, but that doesn't affect existing columns in the database.
I can change it for one column at the time, but that's a bit cumbersome.
I've tried different variations of ALTER TABLE but it gives me a syntax
error unless I include the column name I want to change.
Regards
Steen
You need to do the columns individually. A table on its own does not have a
collation.
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>
|||COLLATION is defined on columns, not tables, so you will have to change one
column at a time.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>
|||By the way, you can quasi-automate this, e.g.
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
+ ' COLLATE <new collation>'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
You will still have to deal with constraints, computed columns etc.
yourself...
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:uYsbmAnxEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Hi
> Can any of you help with the syntax for changing the COLLATION on all
> columns in a table. I have no problem in changing the collation for the
> database itself, but that doesn't affect existing columns in the database.
> I can change it for one column at the time, but that's a bit cumbersome.
> I've tried different variations of ALTER TABLE but it gives me a syntax
> error unless I include the column name I want to change.
> Regards
> Steen
>
|||Aaron,
I think you may have some problems with TEXT/NTEXT columns, as you cannot
use ALTER TABLE on them.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
|||> I think you may have some problems with TEXT/NTEXT columns, as you cannot
> use ALTER TABLE on them.
Good catch... can you tell I reused an automation script from a completely
different task? ;-)
|||Also, the COLLATE and NULL clauses were in the wrong order...
Here's a fixed version:
DECLARE @.tablename VARCHAR(64)
SET @.tablename = 'something'
SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
+DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
+ ' COLLATE <new collation> '
+ CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@.tablename
AND RIGHT(DATA_TYPE,4) = 'char'
... and now to really automate it, I leave it up to you to plug it into
sp_execresultset
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...
> By the way, you can quasi-automate this, e.g.
>
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> + ' COLLATE <new collation>'
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) IN ('char', 'text')
>
> You will still have to deal with constraints, computed columns etc.
> yourself...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
|||Thanks guys...I'll give it a try.
I was aware that the collation was specific for the columns, but I thought
there was an "out of the box" funktion that could change it for all columns
in a table.
Regards
Steen
Adam Machanic wrote:[vbcol=seagreen]
> Also, the COLLATE and NULL clauses were in the wrong order...
> Here's a fixed version:
>
> DECLARE @.tablename VARCHAR(64)
> SET @.tablename = 'something'
> SELECT 'ALTER TABLE '+@.tablename +' ALTER COLUMN '+COLUMN_NAME+ ' '
> +DATA_TYPE+'('+RTRIM(CHARACTER_MAXIMUM_LENGTH)+') '
> + ' COLLATE <new collation> '
> + CASE IS_NULLABLE WHEN 'YES' THEN 'NULL' ELSE 'NOT NULL' END
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME=@.tablename
> AND RIGHT(DATA_TYPE,4) = 'char'
>
> ... and now to really automate it, I leave it up to you to plug it
> into sp_execresultset
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:Oh1bpGnxEHA.1396@.tk2msftngp13.phx.gbl...