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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.

911 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

20 Experts available now in Live!

Get 1:1 Help Now