{"id":102,"date":"2009-02-23T00:43:57","date_gmt":"2009-02-23T05:43:57","guid":{"rendered":"\/\/www.beauchamp.me\/techno\/?p=102"},"modified":"2012-05-17T11:05:47","modified_gmt":"2012-05-17T16:05:47","slug":"changing-collation","status":"publish","type":"post","link":"https:\/\/www.beauchamp.me\/techno\/blog\/2009\/02\/23\/changing-collation\/","title":{"rendered":"Changing collation"},"content":{"rendered":"<p>Once, I was in a situation where I had to change the collation of a database. This is not an easy task, since collations are defined at three levels: Database, Table and Columns. So even, if you change the database collation, all the table and fields will still retain the original collation. Changing collation in all tables and all fields properties could be tedious, but I found a script to do that <a href=\"http:\/\/www.sqlservercentral.com\/scripts\/Miscellaneous\/30471\/\" target=\"_blank\">here<\/a>. I modified the script though, because it was miscalculating the space taken by multibyte strings and it did not work on databases with special caracters in table and field names. <\/p>\n<p>On top of that script, you&#8217;ll have to run a command like the following one to change the database collation:<\/p>\n<p>ALTER DATABASE &lt;database name&gt; COLLATE &lt;collation name&gt;<\/p>\n<p>Example:<\/p>\n<pre class=\"brush:sql\">ALTER DATABASE [TestsImportation]\r\nCOLLATE SQL_Latin1_General_CP1_CI_AI ;<\/pre>\n<p>Here is the modified version of the script:<\/p>\n<pre class=\"brush:sql\">\/****** Object:\u00a0 StoredProcedure [dbo].[LM_ChangeCollation]\u00a0\u00a0\u00a0 Script Date: 02\/23\/2009 00:15:14 ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n------------------------------------------------------------\r\n-- LM_ChangeCollation - Change collation in all tables\r\n--\r\n--\r\n--\r\n--\u00a0 made by Luis Monteiro - ljmonteiro@eurociber.pt\r\n--\u00a0 Modified by Jean-Fran\u00e7ois Beauchamp to support\r\n--\u00a0 field names containing special characters\r\n--\u00a0 and to fix a field size bug with double byte types\r\n------------------------------------------------------------\r\nCREATE PROCEDURE [dbo].[LM_ChangeCollation]\r\n@new_collation varchar(100)=NULL\r\nAS\r\n\r\nDECLARE\r\n@table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(50),\r\n@column\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(60),\r\n@type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(20),\r\n@length\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 int,\r\n@nullable\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(8),\r\n@sql\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(8000),\r\n@msg\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 varchar(8000),\r\n@servercollation\u00a0\u00a0\u00a0 varchar(120)\r\n\r\nselect @servercollation = convert(sysname, serverproperty('collation'))\r\n\r\nDECLARE C1 CURSOR FOR\r\n\r\nselect\r\n'Table'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = b.name,\r\n'Column'\u00a0\u00a0\u00a0 = a.name,\r\n'Type'\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 = type_name(a.xusertype),\r\n'Length'\u00a0\u00a0\u00a0 = a.length,\r\n'Nullable'\u00a0\u00a0\u00a0 = case when a.isnullable = 0 then 'NOT NULL' else ' ' end\r\nfrom syscolumns a, sysobjects b\r\nwhere a.number = 0\r\nand a.id =b.id\r\nand b.xtype='U'\r\nand b.name not like 'dt%'\r\nand type_name(a.xusertype) in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')\r\norder by b.name,a.colid\r\n\r\nOPEN C1\r\nFETCH NEXT FROM C1\r\nINTO\u00a0\u00a0\u00a0\u00a0 @table,@column,@type,@length,@nullable\r\n\r\nWHILE @@FETCH_STATUS=0\r\nBEGIN\r\n\r\nIF @new_collation is null\r\nset @new_collation=@servercollation\r\n\r\nset @sql='ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column + '] '\r\nIF (@type IN ('nchar', 'nvarchar', 'ntext'))\r\nBEGIN\r\n\tIF (@length = -1)\r\n\t\tset @sql=@sql+ @type+'(MAX) COLLATE '+@new_collation+' '+@nullable;\r\n\tELSE\r\n\t\tset @sql=@sql+ @type+'(' + CAST((@length \/ 2) AS VARCHAR(4)) + ') COLLATE '+@new_collation+' '+@nullable;\r\nEND\r\nELSE\r\n\tset @sql=@sql+ @type+'(' + CAST(@length AS VARCHAR(4)) + ') COLLATE '+@new_collation+' '+@nullable;\r\n\r\nEXEC (@sql)\r\n\r\nFETCH NEXT FROM C1\r\nINTO\u00a0\u00a0\u00a0\u00a0 @table,@column,@type,@length,@nullable\r\n\r\nEND\r\nCLOSE C1\r\nDEALLOCATE C1<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Once, I was in a situation where I had to change the collation of a database. This is not an easy task, since collations are defined at three levels: Database, Table and Columns. So even, if you change the database collation, all the table and fields will still retain the original collation. Changing collation in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[3,12],"tags":[],"class_list":["post-102","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/comments?post=102"}],"version-history":[{"count":9,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/102\/revisions"}],"predecessor-version":[{"id":200,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/102\/revisions\/200"}],"wp:attachment":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/media?parent=102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/categories?post=102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/tags?post=102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}