We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Automatically Alter Colation

billy21
billy21 asked
on
Medium Priority
662 Views
Last Modified: 2007-12-19
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
Comment
Watch Question

Author

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  
No Probs stand by for answer...
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.