change collation for all tables

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 2005 Forums

Transact-SQL (2005)

change collation for all tables

Author

Topic

netcom7
Starting Member

1 Post

Posted - 2008-04-30 : 09:02:55

Problem is, the database collation is "SQL_Latin1_General_CP1_CI_AS" and some fields of some tables have the collation "Latin1_General_CI_AS". You know the Problem ... ;-)

As I don't want to change the columns one by one, I want to ask how can I change collation of all column in a table by 1 Transact SQL? Or how can I change the collation of all the tables?

Thanks, ON

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-04-30 : 16:15:29

Well you could change the default for the database via ALTER DATABASE but I guess that won't change existing columns. You could generate the statements using information_schema.columns then execute the output but you will likely run into problems because collation can't be changed for any of the following conditions:

quote:

from Books Online:

If a CHECK constraint, FOREIGN KEY constraint, or computed columns reference the column changed.
If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed are dropped if the column collation is changed.
If a schema-bound view or function references the column.

But this code could get you started for how to generate the ALTER statements:


select 'alter table [' + table_name + '] '
       + 'alter column [' + column_name + '] '
       + data_type + '(' + convert(varchar, character_maximum_length) + ') '
       + 'collate Latin1_General_CI_AS '
       + case when is_nullable = 'no' then 'not ' else '' end
       + 'null '
from information_schema.columns
where  collation_name = 'SQL_Latin1_General_CP1_CI_AS'
order by table_name, ordinal_position

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources