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
hi sir this is gopi
while intalling sql server 2005 i did not install sql server profiler now i want that profiler it is possible sir reply me
how to restore the database with out mdf,and log files
Hi Pinal, nice post as always. I have personally made a little script to help me understanding which columns i have to change in order to maintain the correct collation on a Database.
Here’s the script if anyone needs something like this.
NOTE: it doesn’t do nothing, but just prints the T-Sql instructions that allow to change the collation, this because based on my experience, i prefer to double check a couple of times before messing up with DB Settings :)
DECLARE @collationType VARCHAR(100)
SELECT @collationType = ‘Latin1_General_CI_AS’
DECLARE collationToChangeColumns CURSOR
FOR
SELECT colonne.NAME ,
‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + tabelle.NAME + ‘]’ ,
colonne.SYSTEM_type_id ,
colonne.max_length
FROM sys.columns colonne
JOIN sys.tables tabelle
ON colonne.OBJECT_ID = tabelle.OBJECT_ID
WHERE collation_name @collationType
AND tabelle.TYPE = ‘u’
DECLARE @tableName AS VARCHAR(100)
DECLARE @columnName AS VARCHAR(100)
DECLARE @typeID AS INT
DECLARE @maxLength AS INT
DECLARE @sqlString AS NVARCHAR(4000)
OPEN collationToChangeColumns
FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID, @maxLength
WHILE @@FETCH_STATUS -1
BEGIN
IF @typeID NOT IN ( 35, 99 )
BEGIN
IF @maxLength > 0
BEGIN
SET @sqlString = ‘ALTER TABLE ‘ + @tableName
+ ‘ alter column [‘ + @columnName + ‘] ‘
+ CAST(TYPE_NAME(@typeID) AS VARCHAR(20)) + ‘(‘
+ CAST(@maxLength AS VARCHAR(10)) + ‘) collate ‘
+ @collationType + ‘;’
PRINT @sqlString
END
END
FETCH collationToChangeColumns INTO @columnName, @tableName, @typeID,
@maxLength
END
CLOSE collationToChangeColumns
DEALLOCATE collationToChangeColumns
eabidi syntax without CURSOR
DECLARE @OldCollation VARCHAR(100), @NewCollation VARCHAR(100)
SELECT @OldCollation = ‘SQL_Latin1_General_CP1_CI_AS’,
@NewCollation=’Estonian_ci_as’;
WITH XChange (XColumn,XTable,XType,XLen)
as(
SELECT c.NAME ,
‘[‘ + SCHEMA_NAME(schema_id) + ‘].[‘ + t.NAME + ‘]’ ,
c.SYSTEM_type_id ,
c.max_length
FROM sys.columns c
JOIN sys.tables t ON c.OBJECT_ID = t.OBJECT_ID
WHERE
collation_name=@OldCollation
AND t.TYPE = ‘u’)
SELECT ‘ALTER TABLE ‘ + XTable
+ ‘ alter column [‘ + XColumn + ‘] ‘
+ CAST(TYPE_NAME(XType) AS VARCHAR(20)) + ‘(‘
+ CAST(XLen AS VARCHAR(10)) + ‘) collate ‘
+ @NewCollation + ‘;’
FROM XChange
where XType not in (35,99)
and XLen>0;
Hi Pinal,
How to convert the Arabic_CI_AS collation records into SQL_Latin1_General_CP1_CS_AS format using TSQL.
EXCELLENT THANKS
Thanks @Ahmad