How to change SQL Server database collation

\r\n

\r\n

\r\n\r\nIf you tried changing collation to from English to Turkish or other using the script as below but it will fail due to dependencies issues, You have to script them, drop them, change db collation and recreate them from script.\r\n\r\nThe Collation change script:\r\n

\r\n

Code:

\r\n

ALTER DATABASE {your database} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nALTER DATABASE {your database} COLLATE Turkish_CI_AS\r\nALTER DATABASE {your database} SET MULTI_USER

\r\n

\r\n

\r\n

\r\n

where you need changing {your database} with your database name i.e. mydb

\r\n

\r\n

\r\nThese links would help you:\r\n

\r\nKeep in mind that changing the colaltion of the database will NOT change the collation of the columns that are already in the database, only the default collation of any new columns you will create.\r\n\r\nThe error you may face while changing collation:\r\n

Error Code:

\r\n

Msg 5075, Level 16, State 1, Line 2\r\nThe object '{ObjectName}' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5075, Level 16, State 1, Line 2\r\nThe object '{objectName{' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5075, Level 16, State 1, Line 2\r\nThe object '{ObjectName}' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5072, Level 16, State 1, Line 2\r\nALTER DATABASE failed. The default collation of database 'myDB' cannot be set to Turkish_CI_AS.

\r\n

\r\n

4 thoughts on “How to change SQL Server database collation”

  1. Firefox 3.6.12 Firefox 3.6.12 Windows XP Windows XP
    Mozilla/5.0 (Windows; U; Windows NT 5.1; tr; rv:1.9.2.12) Gecko/20101026 Firefox/3.6.12 GTB7.1 (.NET CLR 3.5.30729)

    This is a good blog message, I will keep the post in my mind. If you can add more video and pictures can be much better. Because they help much clear understanding. 🙂 thanks Barbaresco.

    1. Google Chrome 12.0.742.100 Google Chrome 12.0.742.100 Windows 7 Windows 7
      Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.30 (KHTML, like Gecko) Chrome/12.0.742.100 Safari/534.30

      You’re welcomed to post articles here. \nWhat’s your area of interest to post here?\nPlease register with blog and let me know your ID and sample content.

  2. Google Chrome 12.0.742.100 Google Chrome 12.0.742.100 Windows 7 Windows 7
    Mozilla/5.0 (Windows NT 6.1) AppleWebKit/534.30 (KHTML, like Gecko) Chrome/12.0.742.100 Safari/534.30

    Barbaresco,\r\nThank you for your suggestion, I’ll try to add pictures / videos tutorials in topics.\r\nYour contribution much appreciated.

Comments are closed.