• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 518
  • Last Modified:

script to modify collation for all columns varchar for all tables

Hello,

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

Thanks

bibi
0
bibi92
Asked:
bibi92
1 Solution
 
James MurrellProduct SpecialistCommented:
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
 
CboudrozCommented:
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
 
bibi92Author Commented:
Thanks a lot regards bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now