Posted on 2004-08-11
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