Solved

Automatically Alter Colation

Posted on 2004-03-31
3
585 Views
Last Modified: 2007-12-19
Hi All,

I have the following script I use to automatically alter the colation of all varchar fields in a database:

Declare @SQL VarChar(1000)
Declare AlterColation Cursor
For

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
Order By so.Name, sc.Name

Open AlterColation
Fetch Next From AlterColation
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From AlterColation
      INTO @SQL
      Print @SQL
End
Close AlterColation
DeAllocate AlterColation
Go    


My problems are:
1) When an error occurs in the execution of a piece of dynamic sql the script stops and doesn't continue with the remainder
2) It only works for varchar and I think my system of isolating varchar fields is flawed (ie I may end up changing char or nvarchar fields to varchar!)
3) I'd like to exclude variables that are being used as primary keys.

Thanks in advance,

Jackie Lee
0
Comment
Question by:billy21
  • 2
3 Comments
 
LVL 6

Author Comment

by:billy21
ID: 10724474
I've adjusted the script to ignore fields that already have the correct collation.


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 <> 'COLLATE SQL_Latin1_General_CP1_CI_AS'
Order By so.Name, sc.Name

Open AlterCollation
Fetch Next From AlterCollation
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From AlterCollation
      INTO @SQL
      Print @SQL
End
Close AlterCollation
DeAllocate AlterCollation
Go  
0
 
LVL 1

Expert Comment

by:JackieLee
ID: 10724567
No Probs stand by for answer...
0
 
LVL 1

Accepted Solution

by:
JackieLee earned 500 total points
ID: 10724599
Here is something I wrote to drop all foreign key constraints.  Hope it helps...

Declare @SQL VarChar(1000)
Declare DropFKs Cursor
For
select 'ALTER TABLE [' + so1.name + '] Drop Constraint [' + so2.name + ']'
from sysobjects so1
inner join sysobjects so2 on so2.parent_obj = so1.id
where so2.xtype = 'F'
Open DropFKs
Fetch Next From DropFKs
Into @SQL
WHILE @@FETCH_STATUS = 0
Begin
      Exec(@SQL)
      Fetch Next From DropFKs
      INTO @SQL
End
Close DropFKs
DeAllocate DropFKs
Go    
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

920 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

12 Experts available now in Live!

Get 1:1 Help Now