billy21
asked on
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.CONSTRA INT_COLUMN _USAGE Where so.Name = Table_Name and sc.Name = column_name)
and so.ID <> 1249439525
Order By so.Name, sc.Name
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_
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
And Not Exists (select * from information_schema.CONSTRA
and so.ID <> 1249439525
Order By so.Name, sc.Name
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
[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]
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]
ASKER
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.
ASKER
Scott,
I've reinstated the question here...
https://www.experts-exchange.com/questions/21090211/Script-to-Create-PK-and-Unique-Constraints.html
Thanks,
Bill
I've reinstated the question here...
https://www.experts-exchange.com/questions/21090211/Script-to-Create-PK-and-Unique-Constraints.html
Thanks,
Bill