Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
First of all, don’t forget to take backup for the security of your data.
You can change the database’s collation with the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 | USE master GO SELECT DATABASEPROPERTYEX('test','COLLATION') GO ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE test GO ALTER DATABASE test COLLATE Turkish_CI_AI GO SELECT DATABASEPROPERTYEX('test','COLLATION') GO ALTER DATABASE test SET MULTI_USER |
However, the database collation change does not apply to existing tables and columns.
If you want to make the same change for existing tables and all columns, you should run the following script after running the above script.
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @NewCollation sysname set @NewCollation = 'Turkish_CI_AS' select 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(stable.schema_id)) + '.' + QUOTENAME(stable.name) + ' ALTER COLUMN ' + QUOTENAME(scol.name) + ' ' + stype.name + '(' + CASE WHEN scol.max_length = -1 THEN 'max' ELSE CONVERT(varchar(10),scol.max_length) END + ') collate ' + @NewCollation + ' go ' from sys.columns scol inner join sys.tables stable on scol.object_id = stable.object_id inner join sys.types stype on scol.user_type_id = stype.user_type_id where scol.collation_name is not null and OBJECTPROPERTY(stable.object_id,N'IsMSShipped')=0 |
