Thursday, 11 January 2007

Changing Database Collation

The existing Database connection can be changed using the ALTER Database command provided in SQL Server. To change the collation settings
  • Restrict the Database access to Single User Mode
  • Change the Database collation using the following syntax.

    ALTER DATABASE dbname COLLATE [Replace Actual Collation]
    GO
This will only change the collation of the Database and not the collation of the Database objects (Tables) if any exists already. We need to explicitly change the collation of the character (VARCHAR / NVARCHAR) columns for each table. To change the collation settings for columns
  • Take the backup of all the Indexes and constraints of the tables with Character columns in the Database
  • Drop all the Indexes and constraints (Primary Key, Foreign Key, Defaults etc)
  • Change the Collation setting for the table columns. The below script can be used to generate the script that identifies the character columns and replace it with the new collation in the Database


    SELECT 'ALTER TABLE ' + SYSOBJECTS.Name + ' ALTER COLUMN ' + SYSCOLUMNS.Name + ' ' +
    SYSTYPES.name + '(' + RTRIM(CONVERT(CHAR,SYSCOLUMNS.length)) + ') ' + ' COLLATE [Replace Actual Collation]' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' End +
    CHAR(13) + ' GO'
    FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
    WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
    AND SYSOBJECTS.TYPE = 'U'
    AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
    AND SYSCOLUMNS.COLLATION IS NOT NULL
    GO

27 comments:

Anonymous said...

Thanks for sharing the link, but unfortunately it seems to be offline... Does anybody have a mirror or another source? Please answer to my message if you do!

I would appreciate if a staff member here at samsudeenb.blogspot.com could post it.

Thanks,
Peter

Anonymous said...

Have you considered the fact that this might work another way? I am wondering if anyone else has come across something
like this in the past? Let me know your thoughts...

Anonymous said...

Hey,

I have a question for the webmaster/admin here at samsudeenb.blogspot.com.

May I use some of the information from this blog post right above if I give a link back to this site?

Thanks,
Charlie

Samsudeen B said...

Hi,

This blog is aimed for helping people who need SQL Information.

You are welcomed to use the content as long as it is for good

Regards,
Samsudeen B

Anonymous said...

Authentic Brandon Marshall Jersey axiotakix
Nike Robert Griffin III Jersey axiotakix
Robert Griffin III Youth Jersey axiotakix
http://www.nikenfljerseysauthentic.us

Anonymous said...

Excellent pieces. Keep posting such kind of information on your site.
Im really impressed by your site.
Hello there, You have performed a fantastic job. I will definitely digg it and in
my view suggest to my friends. I am sure they'll be benefited from this website.
Here is my site ; Promo Codes

Anonymous said...

Hello. Facebook takes a [url=http://www.freecasinogames.gd]baccarat[/url] venture on 888 casino see trade: Facebook is expanding its efforts to push aside speed up real-money gaming to millions of British users after announcing a practise with the online gambling toss 888 Holdings.And Bye.

Anonymous said...

[url=http://www.49ersonlineofficialstore.com/]Nike 49ers Super Bowl Jersey[/url] emurbursofs
[url=http://www.footballravensprostore.com/justin_tucker_jersey_super_bowl]Justin Tucker Jersey[/url] TynckeyncBype
[url=http://www.footballravensprostore.com/justin_tucker_jersey_super_bowl]Justin Tucker Jersey[/url] Proonseorek

Anonymous said...


There is certainly a lot to know about this topic. I like all the points you've made.

Anonymous said...

top [url=http://www.001casino.com/]free casino games[/url] coincide the latest [url=http://www.realcazinoz.com/]casino[/url] autonomous no consign perk at the leading [url=http://www.baywatchcasino.com/]online casino
[/url].

Anonymous said...

Printing beetle has serveF its purchasers higher than anF beyonF with high-quality anF low cost sticker printing service[url=http://www.germanylovelv.com/]Louis Vuitton kopierte Tasche kaufen[/url]
Our company priFes alone in giving buyers throughout the globe with the most inexpensive, small charge, trusteF & aFForFable sticker printing experience than anywhere online. Our FeFicateF staFF is available 24 / 7 For all our low cost sticker printing orFers [url=http://www.germanylovelv.com/]Louis Vuitton Outlet/[/url]
give ultimate satisFaction in all sticker relateF services at very aFForFable rate[url=http://www.germanylovelv.com/]Louis Vuitton Outlet[/url]

|
|

Anonymous said...

simply stopping by to say hi

Anonymous said...

[url=http://longchampsoldesk.fotopages.com/]sac longchamps[/url] I was cleaning the bathroom with my trusty Dyson canister vacuum with the little brushy thingy attachment, and I vacuumed the base of the toilet and guess what? Some of the grout had discinerated and when I vacuumed Mulberry Small Heathcliffe Laptop Briefcase Light Coffee for Men,Buy cheap Mulberry bags from Mulberry uk official factory shop. the dust away a PILE of ants came pouring out! My compassion disappeared right then and there. I searched the house for some bug spray but, as luck would have it, there was none to be found. So, I sent a text to my husband saying BUY SOME ANT SPRAY WE HAVE ANTS UNDER THE TOILET!!! Quote, unquote.
[url=http://longchampsoldesa.tumblr.com/]sac longchamp[/url] Each year, it has been estimated that Californians alone use more than 19 billion plastic bags with less than 5% getting recycled. Plastic bags take over 400 years to biodegrade with the use of approximately 60-100 million barrels of oil to make the world's plastic bags each year while adding tonnes of carbon emissions into the air annually. In addition, plastic bags cause over 100,000 sea turtle New Fashion Mulberry Women's Mini Alexa Leather Satchel Light Coffee Bag, Mulberry Outlet online offer you high level quality Mulberry Bags at a low price. and other marine animal deaths every year when animals mistaken them for food.
[url=http://longchampsoldes.weebly.com/]longchamp soldes[/url] We can all agree that the right slogan that can be applied for this season is "cherchez la femme fatale" which simply means "finding the fatal woman". Being able to spot her is very simple and all that you will have to look at is her handbag that is very sophisticated and how mysterious she looks. For you to be able to get such a lady, all that you will have to do Outlet Sale Women's Mulberry Bayswater Trimming Oxford & Leather Tote Bag Light Coffee outlet sale, is to go to the best shop online for handbags.. Handbags are used to carry important things like keys, cellphones, makeups and lipsticks, and extra clothes, and other toiletries. There are also handbags that are specifically made for work. These types are used to carry important files and documents, gadgets such as a laptop and other work-related stuff.. See whats on offer in each of these stores. Compare the prices and the discounts as well. See to it that you purchase Bargain Handbags from a store or stores which vouch for the authenticity of the product/products they are selling.

Anonymous said...

And everything, and variants?
Rather useful piece
I very much would like to talk to you.
I think, that you are not right. I am assured. I can prove it. Write to me in PM, we will discuss.
You are mistaken. I can prove it.

[url=http://michaelkorscheap2.snappages.com/blog/2013/01/16/michaelkorscheap2][b]michael kors outlet online[/b][/url]
[url=http://shenenmaoyif.webnode.cn/][b]michael kors outlet online[/b][/url]
[url=http://mvpmichaelkors8.exteen.com/20130119/owning-high-end-designer-handbags-a-privilege][b]michael kors outlet online[/b][/url]
[url=http://mvpmichaelkors0.soup.io/][b]michael kors outlet online[/b][/url]
[url=http://shenenmaoyipp.blog.hr/][b]michael kors outlet online[/b][/url]

Anonymous said...

Woω, thаt's what I was seeking for, what a data! existing here at this blog, thanks admin of this website.

My web site :: raspberry ketone

Anonymous said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Anonymous said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

Unknown said...

Here is a bit improved version:

SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name + '(' + CASE SYSCOLUMNS.length WHEN -1 THEN 'MAX' ELSE RTRIM(CONVERT(CHAR,SYSCOLUMNS.length)) END + ') ' + ' COLLATE DATABASE_DEFAULT ' + CASE SYSCOLUMNS.isnullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' End +
CHAR(13) + ' GO'
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL AND SYSTYPES.name<>'sysname'
GO

marketing plus5 said...

مظلات وسواتر
تركيب سواتر الرياض
تركيب مظلات الرياض
سواتر الرياض
بيوت شعر الرياض
مظلات سيارات الرياض
هناجر ومستودعات
قرميد
تركيب قرميد حديد
تركيب هناجر الرياض

blogger said...

زيت الحشيش طول شعري بسرعه
زيت الحشيش لتطويل الشعر
فوائد زيت الحشيش الافغاني
فوائد زيت الحشيش
فوائد زيت الحشيش للشعر الخفيف
فوائد زيت الحشيش للشعر
الفرق بين زيت الحشيش الاصلي والمغشوش
فوائد زيت الحشيش للذقن
فوائد زيت الحشيش للشنب
فوائد زيت الحشيش الافغاني للشعر


رضا جمال said...

شركة جوهرة الجنوب للخدمات المنزلية وخدمات العزل تقدم لكم بعض الخدمات التي تقدمها في مدينة ابها وخميس مشيط وجازان

شركة عزل فوم بجازان
شركة عزل خزانات بخميس مشيط
شركة كشف تسربات المياه بخميس مشيط

يحي سامي said...


شركة تنظيف منازل بخميس مشيط
شركة تنظيف خزانات بابها
شركة تنظيف خزانات بخميس مشيط
شركة مكافحة حشرات بابها
شركة مكافحة حشرات بخميس مشيط

Mansi Devgan said...

Jaipur to Vrindavan Taxi

Mai said...

خدمات تلال
شركات تعقيم فنادق ضد فيروس كورونا بالفجيرة
شركات التعقيم المعتمدة من البلدية بالفجيرة

Unknown said...

شركة كشف تسربات المياه بالمدينة المنورة
شركة تنظيف مكيفات بالدمام
شركة تنظيف بينبع
شركة تنظيف مكيفات بالمدينة المنورة

Unknown said...

مظلات
شركة تنظيف خزانات بجدة
تنسيق حدائق الرياض
دهان الرياض
شركة عزل اسطح بجدة 
شركة تنظيف بخميس مشيط
شركة نقل عفش بجدة

Unknown said...

سباك جدة
شركة مكافحة حشرات بجدة
شركة مكافحة حشرات بحفر الباطن
شركة تنظيف بالبخار بالدمام
شركة عزل اسطح بالرياض
شركة تركيب ستائر بالرياض
شركة تركيب طارد حمام بالرياض