Solved

script to modify collation for all columns varchar for all tables

Posted on 2010-11-17
4
510 Views
Last Modified: 2012-05-10
Hello,

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

Thanks

bibi
0
Comment
Question by:bibi92
[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
4 Comments
 
LVL 31

Expert Comment

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

sorry not a great solution, but http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation is a good place to start

hope it helps - if not sorry
0
 
LVL 7

Accepted Solution

by:
Cboudroz earned 500 total points
ID: 34157702
check last column
SELECT 
	o.name as TableName
	, c.Name as ColumnName
	, t.name 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'
FROM 
	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 
WHERE 
	o.TYPE = 'U'
	and t.name like '%CHAR%'

Open in new window

0
 

Author Closing Comment

by:bibi92
ID: 34162635
Thanks a lot regards bibi
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 …
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 setup several different housekeeping processes for a SQL Server.

623 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