Solved

change collation on all varchar fields in database

Posted on 2004-09-09
10
662 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
  • 2
  • 2
  • 2
  • +1
10 Comments
 
LVL 142

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 500 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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now