Solved

Collation Script

Posted on 2004-08-11
5
1,284 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:billy21
  • 2
  • 2
5 Comments
 
LVL 17

Expert Comment

by:BillAn1
Comment Utility
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
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
[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
 
LVL 6

Author Comment

by:billy21
Comment Utility
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
 
LVL 6

Author Comment

by:billy21
Comment Utility
Scott,

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

Thanks,

Bill
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Distinct values from two tables 14 16
SQL JOIN 6 27
Report Builder 9 22
SQL Split character from numbers 3 16
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now