Solved

Collation Script

Posted on 2004-08-11
5
1,288 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

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 …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

685 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