Collation Script

I have a script I use to update the collation of fields in the database that are set to the wrong collation type.

The problem with this script is that if I have a feild that does not accept nulls, this script changes that field to accept nulls.  How can I find out whether or not the field is supposed to accept nulls using the system tables?


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 <> 'SQL_Latin1_General_CP1_CI_AS'
And Not Exists (select * from information_schema.CONSTRAINT_COLUMN_USAGE Where so.Name = Table_Name and sc.Name = column_name)
and so.ID <> 1249439525
Order By so.Name, sc.Name
LVL 6
billy21Asked:
Who is Participating?
 
BillAn1Commented:
i.e.

select 'ALTER TABLE [' + so.Name + '] ALTER COLUMN [' + sc.Name + '] varchar(' + Cast(sc.Length As VarChar(10)) + ') COLLATE SQL_Latin1_General_CP1_CI_AS' + ( case when isnullable = 0 then 'NOT NULL' else 'NULL' end)
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 <> 'SQL_Latin1_General_CP1_CI_AS'
And Not Exists (select * from information_schema.CONSTRAINT_COLUMN_USAGE Where so.Name = Table_Name and sc.Name = column_name)
and so.ID <> 1249439525
Order By so.Name, sc.Name
0
 
BillAn1Commented:
syscolumns has a 'isnullable' field (0/1 for NOT NULL / NULL), so you could add a NULL or NOT NULL to your script, depending on the value of this field.
0
 
Scott PletcherSenior DBACommented:
[Off-topic]
FYI, I was working on your previous Q "Script to Create all Primary Key and Unique Constraints" and think I have something if you're still interested (it wasn't that easy!)
[/Off-topic]
0
 
billy21Author Commented:
Yeah sure.  I'll repost the question.  Sorry I didn't know anyone was working on it.  I came up with a solution for the PK bur not unique constraints.
0
 
billy21Author Commented:
Scott,

I've reinstated the question here...
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21090211.html

Thanks,

Bill
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.