Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

change collation on all varchar fields in database

Posted on 2004-09-09
10
Medium Priority
?
677 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
7 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

580 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