Change Microsoft Sql Server Collation

Change Collation Name

Below script generate alter table query for all tables of selected db,

after executing below query and script then
1. Right click on database 
2. Select Properties
3. Left hand side select option
4. select your collation
5. Press OK

if any issue regrading collation depend on any function then generate a create query and delete it and try below step and execute generated create query on db after successfully changing collation name.

declare @TableName nvarchar(max)
declare @SQLText nvarchar(max)
declare @ColumnName nvarchar(max)
declare @DataType nvarchar(max)
declare @CharacterMaxLen nvarchar(max)
declare @CollationName nvarchar(max)
declare @IsNullable nvarchar(max)

set @CollationName = ‘Latin1_General_CI_AS’ — here specify your collation name

DECLARE MyTableCursor CURSOR FOR
select * from sys.tables

OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyColumnCursor Cursor
FOR
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from information_schema.columns
WHERE table_name = @TableName AND (Data_Type LIKE ‘%char%’ OR Data_Type LIKE ‘%text%’) AND COLLATION_NAME <> @CollationName
ORDER BY ordinal_position
Open MyColumnCursor

FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLText = ‘ALTER TABLE ‘ + @TableName + ‘ ALTER COLUMN [‘ + @ColumnName + ‘] ‘ + @DataType + ‘(‘ + CASE WHEN @CharacterMaxLen = -1 THEN ‘MAX’ ELSE @CharacterMaxLen END + ‘) COLLATE ‘ + @CollationName + ‘ ‘ + CASE WHEN @IsNullable = ‘NO’ THEN ‘NOT NULL’ ELSE ‘NULL’ END
PRINT @SQLText

FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, @CharacterMaxLen, @IsNullable
END
CLOSE MyColumnCursor
DEALLOCATE MyColumnCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor