Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.
Let us see following example.
USE AdventureWorks
GO
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Change the database collation */
ALTER TABLE TestTable
ALTER COLUMN FirstCol VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
GO
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
WHERE OBJECT_ID IN ( SELECT OBJECT_ID
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
GO
/* Database Cleanup */
DROP TABLE TestTable
GO
When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.
Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.
Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
Nice one Dave. You are doing a blog post everyday, it’s amazing. I have seen no one doing it, keep up the good work.
Hi sir,
How to compare the data in tow tables and also generating the Update Scripts Based on target table.
It’s very urgent please give me reply to this.
thanks
Hi Pinal,
Is there any way to change collation in one shot. We correct for one table and getting for another table after some time.
How can we change the collation of Server without reinstalling the SQL Server 2005? Can you please let me know the steps?
Also, I have found the following command on one of the blog
start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=Latin1_General_CI_AS
thanks
Dave,
I just went through all this in a SQL2K to ‘08 upgrade. Took the opportunity to get all our collations in synch. They were all over the place (we’re a multinational company and over the years, people go confused on which to use). Anyway, I’ve written a beautiful set of scripts for this purpose. Basically, the first script creates an object scriptor using DMO. The second script identifies all columns in a database who’s collation is not the desired value. DROP statements are generated on the fly. The second script generates the DDL for all the ALTER TABLE ALTER COLUMN statements. The third script uses the object scriptor (sp_scriptObject @db_name, @obj_type, @obj_name, @obj_subname) which generates all the DDL to re-create the objects dropped. So, you end up with three dynamically generated SQL scripts (DROP, ALTER, CREATE) to consolidate collation. If you’re interrested, let me know.
A little clarification on the above post…
The DROP statements are for collation dependent objects within the database.
They include indexes, primary and foreign keys, table functions, etc. If the object is tied to a column whose collation needs to be changed, it is identified and the DROP and CREATE statements are generated on the fly.
Also, CREATE objects script is generated in the reverse order as the DROP script in attempt to avoid dependency conflicts.
Dave, I would be tremendously interested in this script.
I have just run into a problem with regards to multiple systems we need to converge, and we have multiple collation values for different tables.
I need to migrate all to single collation.
Can you supply?
Clarification…interested in the script from “Brian”.
Brian, could you please please please provide the script?
Tim, Horm.
My posts to this keep getting discarded. Still trying to get you my contact details.
If this post shows up, execute the following. The results will be obvious.
SELECT CONVERT(VarChar(20), 0×427269616e277320656d61696c3a)
UNION ALL
SELECT CONVERT(VarChar(30), 0×6263696465726e20617420676d61696c20646f7420636f6d)
Hi Penal,
Can you please suggest how can we set collates for different languages. I am trying for Chinese, COLLATE Chinese_PRC_CI_AS NOT NULL, but it does not show data in Chinese.
Please suggest, how can we do this.
thanks,
Shyam