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)
50 Comments. Leave new
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), 0x427269616e277320656d61696c3a)
UNION ALL
SELECT CONVERT(VarChar(30), 0x6263696465726e20617420676d61696c20646f7420636f6d)
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
Hi Pinal,
I am new to MSSQL sever 2005, I am practising on Adventureworks Databse, whose collation is ‘Latin1_General_CS_AS’ and each column of tables say for eg.. ‘Person.Address’ is having ‘Latin1_General_CS_AS’ I tried to change its columns by the method u suggested , but of no use.. Can u please help me in this regard… Thanks in anticipation..
I am preparing for MS SQL Server 2005 70-431 exam, but not able to practice because due to the problem mentioned above the Queries are Case Sensitive and I am struck .. need ur help..
Brian,
I to would like to have a look at the scripts to create a level COLLATION database.
Could you please forward the script to me.
Much appreciated.
Mark
Mark,
My posts get discarded when they include contact info. Take note of the SELECT statement I posted in the comment to Tim and Horm.
Hi Brian,
I realise this thread is a bit old but I too would appreciate a copy of the scripts you mentioned. Our older clients have databases in SQL_Latin1_General_CP850_Bin and we are starting to get problems. I have been trying to change them to Latin1_General_CI_AS but there are so many columns and dependencies it is a hopeless task to attempt manually (plus my qualifications lie more in accounting than IT…).
Thank you for any help you can provide.
James
James…
I do not have any way of reaching you. If you provided an email address when you posted, it does NOT get published. And, I don’t work on the back side of this blog, so I don’t have access to and of the form field values.
Also, it seems that whenever I post contact information, the post gets discarded. So….
Please execute the following. The results will be painfully obvious.
SELECT CONVERT(VarChar(20), 0×427269616e277320656d61696c3a)
UNION ALL
SELECT CONVERT(VarChar(30), 0×6263696465726e20617420676d61696c20646f7420636f6d)
Hi Brian,
Thanks for the response. I have tried executing the code you posted but I just get a series of errors;
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘×’.
Msg 168, Level 15, State 1, Line 1
The floating point value ‘427269616e277320656’ is out of the range of computer representation (8 bytes).
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘×’.
Msg 168, Level 15, State 1, Line 1
The floating point value ‘6263696465726e20617420676’ is out of the range of computer representation (8 bytes).
I assume I’m doing something wrong..
Don’t know if this will get through but my handle is james.wishart and the domain is activa.com.au
Cheers,
James
Hi Brian,
I’ve actually managed to fix the problem. Our application has a built in ‘create database’ function which creates a copy of the existing database as a new one, including all the data in the tables. I changed the database collation to the correct one and when I ran the process it created a new database and assigned the default (correct) collation to all the columns.
Thanks for getting back to me though.
Cheers,
James
James,
I’m glad you got the issue sorted out. Incidentally, the code I posted above was only to reveal my email address. Nothing fancy, but it gets thru the spam filters, and I didn’t want any screen-scrapers to add me to their spam targets.
For all others, I will see if I can find the time to patch up the code and make it a bit more generic. Then I’ll write it up and pass it on to Pinal for posting, so that people can grab it a little easier. I don’t have a blog, so I’ll drop the scripts here.