?
Solved

Collation Script

Posted on 2004-08-11
5
Medium Priority
?
1,291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.
Suggested Courses

800 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