SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change database collation


Change database collation

Author
Message
n00bDBA
n00bDBA
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 467
Hi all,

I have a db that i am restoring from a SQL 2005 server to 2012, which is in the wrong collation (It was wrong for the old server as well). From what I understand if you do a ALTER DATABASE this will only change future data and the current data will still be in the old collation?

I found the following KB:

http://support.microsoft.com/default.aspx?scid=kb;en-us;325335

Is this still the only way to update everything to a new collation (the kb says its for 2000 and 2005)? or in 2012 can you get away with just an ALTER DB?

Hope someone can help.
Abhijit More
Abhijit More
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 767
this is one of the worst problems in SQL Server: you can only change only if there is no dependency on objects

Abhijit - http://abhijitmore.wordpress.com
n00bDBA
n00bDBA
SSC Veteran
SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)SSC Veteran (266 reputation)

Group: General Forum Members
Points: 266 Visits: 467
Hi Abhijit,

Thanks for the reply.

So if i cant find any dependacies on columns with the different collation i could do a simple ALTER DATABASE?

Am i on the right track trying the following:


SELECT DISTINCT OBJECT_NAME (OBJECT_ID) AS 'Table Name'
,name AS 'Column Name'
, collation_name
FROM sys.columns
WHERE OBJECT_ID IN (SELECT OBJECT_ID
FROM sys.objects
WHERE TYPE = 'U')
AND collation_name IS NOT NULL
ORDER BY OBJECT_NAME (OBJECT_ID)



To find any tables in the database that have a collation set.

and run this against all the tables from that list to find if they have any dependacies?


SELECT * FROM sys.sql_expression_dependencies
WHERE referencing_id = X



If none of the tables have dependancies I can just change it with ALTER and im good to go?

Sorry for the confusion.

S
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search