Change column collations back to database default

,

This script will create a single-line ALTER TABLE statement for each column in any table to change the collation to match the 'database default'. This is best accomplished in the Query Analyzer -- simply run the script, select all the records, and paste them into a new window to run them.

SELECT 'ALTER TABLE ['+USER_NAME(o.uid)+'].['+o.[name]+'] ALTER COLUMN ['+c.[name]+'] '+
	CASE
		WHEN c.prec IS NULL THEN t.[name]
		ELSE t.[name]+'('+CONVERT(varchar(5),c.prec)+')'
	END+' COLLATE '+t.collation
FROM syscolumns c
	JOIN sysobjects o ON (c.id = o.id)
	JOIN systypes t ON (c.xusertype = t.xusertype)
WHERE c.collation IS NOT NULL
	AND o.type = 'U' -- NOT IN ('P','FN','TF','IF','S','V')
	AND c.collation <> t.collation
ORDER BY o.[name]

Rate

Share

Share

Rate