Wednesday, October 21, 2015

How to change database/server collation

Collation option will create troubles when you are migrating database. To change collation run below script.

-- **************** BEGIN INPUT **********************
USE [YourDBName]

DECLARE @collation NVARCHAR(128)
-- enter you collation name below
SET @collation = N'SQL_Latin1_General_CP1_CI_AS'--Type of Collation you want change--
-- **************** END INPUT ************************

-- **************** BEGIN LOGIC **********************
DECLARE @SqlCollate VARCHAR(2048)

DECLARE myCursor CURSOR LOCAL FOR
    SELECT 'ALTER TABLE [' + sys.objects.name + ']
        ALTER COLUMN ['+ sys.columns.name + '] ' + sys.types.name +
        CASE sys.types.name
            WHEN 'text' THEN ' '
            WHEN 'ntext' THEN ' '
            ELSE '(' + RTRIM(
                CASE
                    WHEN sys.columns.max_length = -1 THEN 'MAX'
                    WHEN sys.columns.max_length > 4000 THEN 'MAX'
                    ELSE CONVERT(CHAR,sys.columns.max_length)
                END) + ')'
        END
        + ' COLLATE ' + @collation + CASE sys.columns.is_nullable WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END
        FROM sys.columns , sys.objects , sys.types
        WHERE sys.columns.object_id = sys.objects.object_id
            AND sys.objects.TYPE = 'U'
            AND sys.types.system_type_id = sys.columns.system_type_id
            AND sys.columns.collation_name IS NOT NULL
            AND sys.columns.collation_name <> @collation
            AND NOT ( sys.objects.NAME LIKE 'sys%' )
            AND NOT ( sys.types.name LIKE 'sys%' )

OPEN myCursor
FETCH NEXT FROM myCursor INTO @SqlCollate

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'Executing: ' + @SqlCollate
    BEGIN TRY
        EXEC(@SqlCollate);
        PRINT 'Done!' + CHAR(10)
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE() + CHAR(10)
    END CATCH
    FETCH NEXT FROM myCursor INTO @SqlCollate
END

PRINT 'Finished!'
-- **************** END LOGIC **********************

After running above procedure, rebuild the index's on the tables 

Ex:-

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO