Solved

Collation Script

Posted on 2004-08-11
5
1,286 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
ID: 11775063
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
ID: 11775095
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:Scott Pletcher
ID: 11775140
[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
ID: 11775207
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
ID: 11775251
Scott,

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

Thanks,

Bill
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

786 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