Home > Paul Busby, Work > Updating database collation

Updating database collation

Had some fun trying to update a database collation. I didn’t have to worry about the data i just needed to the collation changed so that some cross database scripts would still function in order to carry out some testing.

 

Here is a nice little script I found that will generate scripts to change the collation on the required columns. Some of it fails if primary keys or indexes are against the columns but this will do 90%.

 

SELECT
      CAST(o.table_name + ‘ -> ‘ + c.name AS VARCHAR(60))
    , ‘ALTER TABLE ‘ + o.table_name +
        ‘ ALTER COLUMN [' + c.name + '] ‘ +
        UPPER(t.name) + ‘(‘ +
        CASE
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length != -1 THEN CAST(c.max_length / 2 AS NVARCHAR(10))
            WHEN t.name IN (‘nchar’, ‘nvarchar’) AND c.max_length = -1 THEN ‘MAX’
            ELSE CAST(c.max_length AS NVARCHAR(10))
        END + ‘) COLLATE ‘ + @collate +
        CASE WHEN c.is_nullable = 1
            THEN ‘ NULL’
            ELSE ‘ NOT NULL’
        END
FROM sys.columns c WITH (NOWAIT)
JOIN (
    SELECT
          table_name = ‘[' + s.name + '].[' + o.name + ']‘
        , o.[object_id]
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = ‘U’
) o ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH (NOWAIT) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN (‘char’, ‘varchar’, ‘text’, ‘nvarchar’, ‘ntext’, ‘nchar’)
    –AND c.collation_name != @collate
ORDER BY
      o.table_name
    , c.column_id

via Buzz Blog http://paulbuzzblog.wordpress.com/2014/01/10/updating-database-collation/

Chris Stretton
Paul is a an expert SharePoint and Project Server developer and is responsible for designing and implementing custom solutions on client systems using the latest SharePoint and .NET technologies.
Paul has extensive experience with SharePoint systems across all sizes of implementation, ranging from small to large farms and has an excellent understanding of all the elements of SharePoint.

This article has been cross posted from paulbuzzblog.wordpress.com (original article)

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,669 other followers

%d bloggers like this: