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

26 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

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

oakleyses said...

north face, sac michael kors, abercrombie and fitch, scarpe hogan, nike air max, hollister, louis vuitton, nike free, true religion outlet, ray ban uk, vans pas cher, oakley pas cher, ray ban pas cher, lululemon, hollister, air max pas cher, michael kors canada, north face pas cher, air jordan, longchamp pas cher, nike trainers, nike free pas cher, hermes pas cher, air max, sac louis vuitton, true religion outlet, guess pas cher, converse pas cher, true religion, mulberry, nike huarache, timberland, ralph lauren pas cher, burberry pas cher, nike air force, sac vanessa bruno, lacoste pas cher, louis vuitton uk, nike roshe run, chaussure louboutin, longchamp, ralph lauren, barbour, nike roshe, nike blazer pas cher, michael kors uk, tn pas cher, new balance pas cher, louis vuitton pas cher, nike air max

oakleyses said...

giuseppe zanotti, insanity workout, canada goose, valentino shoes, bottega veneta, new balance outlet, canada goose pas cher, marc jacobs outlet, moncler, asics shoes, beats headphones, hollister, reebok shoes, ugg boots, canada goose outlet, vans outlet, rolex watches, ugg outlet, moncler outlet, canada goose outlet, mac cosmetics, lululemon outlet, uggs outlet, wedding dresses, ferragamo shoes, soccer shoes, babyliss pro, north face outlet, mont blanc pens, jimmy choo shoes, mcm handbags, longchamp, ugg, canada goose uk, soccer jerseys, ghd, birkin bag, uggs on sale, celine handbags, moncler, nfl jerseys, roshe run, chi flat iron, instyler ionic styler, herve leger, p90x workout, ugg soldes, abercrombie and fitch, north face jackets, canada goose outlet

شركة الطيب said...


شركة تنظيف بالدمام
شركة تنظيف كنب بالدمام
شركة تنظيف مجالس بالدمام
شركة تنظيف شقق بالدمام
شركة تنظيف بيوت بالدمام
شركة تنظيف منازل بالدمام
شركة تنظيف فلل بالدمام
شركة تنظيف عمائر بالدمام
شركة تنظيف خزانات بالدمام
شركة تنظيف مسابح بالدمام
شركة تنظيف اواني بالدمام
شركة تنظيف كشف تسربات بالدمام
شركة نقل اثاث بالدمام
شركة نقل اثاث بالدمام رخيصة
شركة نقل عفش بالدمام
شركة تخزين اثاث بالدمام

شركة الطيب said...


شركة تنظيف بجدة
شركة تنظيف واجهات زجاج بجدة
شركة تنظيف واجهات حجر بجدة
شركة رش مبيدات بجدة
شركة نقل اثاث بجدة
شركة تنظيف منازل بجدة
شركة تنظيف خزانات بجدة
شركة تنظيف شقق بجدة
شركة كشف تسربات بجدة
شركة مكافحة حشرات بجدة
شركة مكافحة صراصير بجدة
شركة مكافحة فئران بجدة
شركة رش مبيدات حشرية بجدة



ninest123 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

ninest123 said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

ninest123 said...

ralph lauren, babyliss, bottega veneta, ipad cases, louboutin, baseball bats, ferragamo shoes, iphone 6 plus cases, mac cosmetics, iphone 6s plus cases, longchamp, lululemon, herve leger, chi flat iron, nfl jerseys, iphone cases, valentino shoes, hollister, new balance, nike trainers, hollister, beats by dre, iphone 5s cases, iphone 6 cases, celine handbags, north face outlet, oakley, mcm handbags, abercrombie and fitch, wedding dresses, soccer jerseys, insanity workout, soccer shoes, nike air max, vans shoes, instyler, nike huarache, nike roshe, north face outlet, p90x workout, ghd, hollister, iphone 6s cases, nike air max, birkin bag, timberland boots, jimmy choo shoes, s5 cases, reebok shoes, giuseppe zanotti, mont blanc, asics running shoes

ninest123 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

ismail mujde 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

Esraa Abbas said...

https://decor-ksa.com/
https://3kary.com/property-status/shops-for-sale/
https://3kary.com/property-status/shops-for-rent/