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

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Change Database Collation

By Arlo Fuller,

Name: sp_ChangeDatabaseCollation
Type: Stored Procedure
Version: 1.0
Date: 9/30/2014
Author: Arlo Fuller, with credit to:
Raymund Macaalay - http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database
Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx for the ScriptCreateTableKeys and ScriptDropTableKeys code
Purpose: Change the collation for an entire SQL Server database, compatible with SQL Server 2005 onwards
Parameters: (1) DBName - NVARCHAR(255) - the name of the database to change the collation of - MANDATORY
(2) NewCollation - NVARCHAR(255) - the name of the new collation to apply to the database - MANDATORY
(3) JustPrintStatements - BIT (default 1 if not provided) - If JustPrintStatements = 1 then the SP returns two resultsets (drop statements and then create statements)
If JustPrintStatements = 0 then the SP actually executes the generated SQL and applies the schema / collation changes
(4) DebugPrintSQL - Bit (default 0 if not provided) - If DebugPrintSQL = 0 then nothing is output to the Messages tab in SSMS or the output window in osql
If DebugPrintSQL = 1 then the statements being generated / run are output to the Messages tab in SSMS
 Description: This stored procedure (created in the master database by default) automates the process of changing the collation of a SQL Server database.  It takes into account the impact of
dependent objects that prevent collation from being changed at the database level e.g. foreign key constraints, default constraints, indexes etc.  The script writes out a series of
SQL DDL statements (in sequence order) to do the following:
(1) Drop Primary keys and foreign keys
(2) Drop Default constraints
(3) Drop Indexes
(4) Drop Check constraints
(5) Drop Computed columns
(6) Issues ALTER COLUMN statements for any (N)CHAR, (N)VARCHAR and (N)TEXT columns with the new collation.
(7) Drops UDF's
(8) Drops views
(9) Set the target database into SINGLE_USER mode - THIS WILL DROP ALL EXISTING CONNECTIONS TO THE DATABASE AND ANY UNCOMMITTED TRANSACTIONS WILL BE LOST
(10) Changes the database collation at the database level to the new collation
(11) Set the target database back to MULTI_USER
(12) Recreate Primary keys and foreign keys
(13) Re-add Default constraints
(14) Re-add Indexes
(15) Re-add Check constraints
(16) Re-add Computed columns
(17) Re-add UDF's
(18) Re-add views
(19) Reassign permissions to objects that were dropped and recreated
 This stored procedure is provided "as is".  Use it at your own risk and test it out first of all on a test system / database.  Always take a backup first!  Enjoy!!

Total article views: 1286 | Views in the last 30 days: 5
 
Related Articles
ARTICLE

Deep Dive into Changing Database Collation

Changing Database Collation Through Primary keys, Foreign Keys, Default and Check Constraints and mo...

FORUM

Collation Changes

Collation Changes

FORUM

Change Collation

Trying to change database/table collation to SQL_Latin1_General_CP1_CI_AS

SCRIPT

Change the collation of Columns

This script changes the collation of columns to default database collation in all the tables

FORUM

Change collation in SQL Serrver 2008 database.

Change collation in SQL Serrver 2008 database.

Tags
collation    
 
Contribute