script to modify collation for all columns varchar for all tables

Posted on 2010-11-17
Last Modified: 2012-05-10

I search a script for modify collation for all columns varchar for all tables.


Question by:bibi92
LVL 31

Expert Comment

by:James Murrell
ID: 34157682
Apologies ... above should not have been a admin comment

sorry not a great solution, but is a good place to start

hope it helps - if not sorry

Accepted Solution

Cboudroz earned 500 total points
ID: 34157702
check last column
SELECT as TableName
	, c.Name as ColumnName
	, as Columntype
	, c.max_length as ColumnSize
	, c.collation_name as CurrentCollation -- if null = default collation of the database
	, 'ALTER TABLE ' + O.NAME + ' ALTER COLUMN ' + C.NAME + ' ' + T.NAME + '(' + CONVERT(VARCHAR(100), c.max_length) + ') COLLATE SQL_Latin1_General_CP1_CI_AI'
	sys.objects o
	INNER JOIN sys.columns c
		on o.object_id = c.object_id
	INNER JOIN sys.types t
		on t.system_type_id = c.system_type_id 
	o.TYPE = 'U'
	and like '%CHAR%'

Open in new window


Author Closing Comment

ID: 34162635
Thanks a lot regards bibi

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 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 …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

22 Experts available now in Live!

Get 1:1 Help Now