Automatically Alter Colation

Hi All,

I have the following script I use to automatically alter the colation of all varchar fields in a database:

Declare @SQL VarChar(1000)
Declare AlterColation Cursor
For

select 'ALTER TABLE [' + so.Name + '] ALTER COLUMN [' + sc.Name + '] varchar(' + Cast(sc.Length As VarChar(10)) + ') COLLATE SQL_Latin1_General_CP1_CI_AS'
from syscolumns sc
Inner Join sysObjects so on so.id = sc.id
Where Not (sc.CollationId Is Null)
And so.XType = 'U '
And sc.type = 39
Order By so.Name, sc.Name

Open AlterColation
Fetch Next From AlterColation
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From AlterColation
      INTO @SQL
      Print @SQL
End
Close AlterColation
DeAllocate AlterColation
Go    


My problems are:
1) When an error occurs in the execution of a piece of dynamic sql the script stops and doesn't continue with the remainder
2) It only works for varchar and I think my system of isolating varchar fields is flawed (ie I may end up changing char or nvarchar fields to varchar!)
3) I'd like to exclude variables that are being used as primary keys.

Thanks in advance,

Jackie Lee
LVL 6
billy21Asked:
Who is Participating?
 
JackieLeeConnect With a Mentor Commented:
Here is something I wrote to drop all foreign key constraints.  Hope it helps...

Declare @SQL VarChar(1000)
Declare DropFKs Cursor
For
select 'ALTER TABLE [' + so1.name + '] Drop Constraint [' + so2.name + ']'
from sysobjects so1
inner join sysobjects so2 on so2.parent_obj = so1.id
where so2.xtype = 'F'
Open DropFKs
Fetch Next From DropFKs
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From DropFKs
      INTO @SQL
End
Close DropFKs
DeAllocate DropFKs
Go    
0
 
billy21Author Commented:
I've adjusted the script to ignore fields that already have the correct collation.


select 'ALTER TABLE [' + so.Name + '] ALTER COLUMN [' + sc.Name + '] varchar(' + Cast(sc.Length As VarChar(10)) + ') COLLATE SQL_Latin1_General_CP1_CI_AS'
from syscolumns sc
Inner Join sysObjects so on so.id = sc.id
Where Not (sc.CollationId Is Null)
And so.XType = 'U '
And sc.type = 39
And sc.Collation <> 'COLLATE SQL_Latin1_General_CP1_CI_AS'
Order By so.Name, sc.Name

Open AlterCollation
Fetch Next From AlterCollation
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From AlterCollation
      INTO @SQL
      Print @SQL
End
Close AlterCollation
DeAllocate AlterCollation
Go  
0
 
JackieLeeCommented:
No Probs stand by for answer...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.