?
Solved

change collation on all varchar fields in database

Posted on 2004-09-09
10
Medium Priority
?
674 Views
Last Modified: 2012-06-21
how can i do this?

is Something like this possible:

'update varchar field set collation = DANISH_NORWEGIAN Where varchar field in (select all varchar fields in all tables in database)'
0
Comment
Question by:Darth_helge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015119
You might find some scripts that try to do this, but there are TOO many things that won't work in a automated way.
The preferred method is to create a new database with all the new collations in place, and copy over the database data.
This is a job you won't run many times on a database...
see here:
http://support.microsoft.com/?kbid=325335#7

CHeers
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12015141
the following SQL will generate ALTER statements for you, which will work in the simplest of cases.
However, as angelIII says, a lot can go wrong - particularly, you cannot alter collation on an indexed column, or a column that has a FK relationship. If you have 1 or 2 indexes, try dropping them and recrating them afterwards.  If you have a lot of these, you're probably best recreating the whole database, as stated.

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' +
column_name + '] ' + data_type + '(' + convert(varchar(4),character_maximum_length) + ')' +
'collate DANISH_NORWEGIAN_CI_AS' +
case when is_nullable = 'YES' then ' NULL' else ' NOT NULL' end
from information_schema.columns
where data_type in ('varchar','char', 'nvarchar', 'nchar')
0
 
LVL 1

Expert Comment

by:ratb3rt
ID: 12015201
This should work (please excuse my shoddy sql ;)



declare @tabname sysname, @colname sysname, @collen int, @colnull int
declare @sqlstr nvarchar(1000)
declare mycur cursor for
 select so.name,sc.name, sc.length, sc.isnullable from sysobjects so (nolock), syscolumns sc (nolock),systypes st (nolock)
 where so.type='U' and sc.id=so.id and st.xtype=sc.xtype and st.name='varchar'
open mycur
fetch next from mycur into @tabname, @colname, @collen, @colnull
while @@fetch_status=0
begin
 set @sqlstr='ALTER TABLE '+rtrim(@tabname)+' ALTER COLUMN '+rtrim(@colname)+
            ' varchar('+rtrim(@collen)+')COLLATE DANISH_NORWEGIAN '
 if @colnull=1
      set @sqlstr=@sqlstr+' NULL'
 else
   set @sqlstr=@sqlstr+' NOT NULL'
print @sqlstr
 exec sp_executesql @sqlstr
 fetch next from mycur into @tabname, @colname, @collen, @colnull
end
close mycur
deallocate mycur


0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 5

Author Comment

by:Darth_helge
ID: 12015232
ok, I tried creating a new database, and then copy the data from the old to the new one, but it failed. Both the choices copy tables and views and copy objects and data, failed.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 2000 total points
ID: 12015306
What failed? What was the error message? How did you create the database? You need to create a database, and all the teables etc, no good just creating an empty database, then copying the objects across.
The best way to create the database is to script out the existing database (Enterprise Manager All tasks -> generate SQL script) , then edit the script to replace the collation with what you want, (and change the database name / filenames!) then run this script in QA.
0
 
LVL 5

Author Comment

by:Darth_helge
ID: 12015332
ok, I will try that thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12015408
>>Both the choices copy tables and views and copy objects and data, failed.
this is obvious because the collections between the databases differ.
As BillAn1 indicates, first create the objects with the new collation, and then copy the data
CHeers
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question