Changing collation.
-
Collation can be a pain. It's definateley better to chose the right collation at install time because changing it later is painfull. The default collation for a database can be changed with
ALTER DATABASE MyDatabase COLLATE French_CI_AS
However it only changes collation of objects created after it. Existing objects have to be changed at the column level.
You can generate up a change script like this
CREATE
PROCEDURE [dbo].[upChangeCollation]
AS
SELECT
'ALTER TABLE [' +
o
.name +
'] ALTER COLUMN [' +
c
.name + '] ' +
t
.name +
CASE t.name
WHEN 'sysname' THEN ' COLLATE Latin1_General_CI_AS'
ELSE '(' + CAST(c.length AS VARCHAR(18)) + ') ' + ' COLLATE Latin1_General_CI_AS'
END
FROM
sysobjects o INNER JOIN syscolumns c
ON o.id = c.id
INNER JOIN systypes t
ON c.xtype = t.xtype
WHERE
o
.xtype = 'U'
AND t.name IN ('char','nchar','nvarchar','sysname','varchar')
-
Hi Timber...
Hope this is not too late...
Another thing to watch out for is when you "mix" database tables and temp. tables in your queries and stored procedures, errors can result if the default server collation is not compatible with your database collation.
One example is when your default server collation is case-sensitive and your database collation is case-insensitive...
This is probably because TEMPDB uses the default server collation, as it was created at installation time... I am not sure if TEMPDB's default collation can be changed, I have not tried it out or looked it up.
Thanks
Bernard
-
When you create something in TempDB you can of course specify the collation you want.
-
You can change TempDB's collation by changing the colation of then entire server. That will involve rebuilding the master database with the new collation. Look up Rebuildm in books online.
If you are looking to change the server's collation, make sure you have scripts to recreate logins and other server objects and that you detach all user database (and preferably make sure you have a backup)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply