SQL Script to Change Collation Settings for user database

Leave a comment (0) Go to comments

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Today, One of the past trainee attendee asked me how to change the Collation setting at the database level, straight forwardly, we can change the collation at database level using the following script.

-- Changing Collation setting at Database Level
ALTER DATABASE DBATAG COLLATE French_CI_AI ;

Note : this script does not change collation of the objects that have already been created.

Script that returns all your databases’ collations

--  Script that returns all your databases' collations
select name, collation_name from sys.databases

Script  that returns all available collations

-- Script  that returns all available collations
select * from fn_helpcollations()

If you are looking for, Script to compare all Collation settings, Check OUT my previous Post


  1. Databases where collation is different from server setting
  2. Columns where collation is different from database setting
EOF - SQL Script to Change Collation Settings for user database, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.