The Easy way of changing Collation of all Database objects in SQL Server

By | December 19, 2011
11 Comments

Did you encountered a problem where you wanted to change your database collation to default or even just changing it to a different type? I guess what you had initially done (like me) was to change the collation of the Database

well that does not quite work well as the existing columns will not be changed and retain its current collation type, only the newly created objects will use this new collation type. So you are left to the option of changing the columns one at a time by going to the column property and restoring it to default or choosing the collation type you want.


Well that’s great if you need to change 10 columns or less but what if you want to change the whole database? What if it’s a primary key or a foreign key? Well isn’t that a nightmare? Well I will give you an easy solution and all you need to do is to run 6 easy steps. If you don’t want to recreate the database and pump data by using DTS or SSIS then this is the solution for you just make sure backup and restore everything before doing any changes.

Step 1: Prepare your DB and Change the collation to your desired one.

Like I had said backup your database as a part of the preparation, once that’s done change your collation to the desired type by going to the database properties by right clicking on the database and choosing properties, once your on the properties window choose options and you can see the collation from there, choose what you want then hit OK. This will ensure that new objects created will be using the new collation.

Step 2: Create you Change Collation Script.

Next is to create a script to change the collation of every object in your database. You need to use the information_schema to extract columns needed to be changed and from there we run a loop on all objects creating alter scripts on each item. Since it is a collation change we will only need fields that uses character types and text types. What you need is to have a lot of commands similar to this


ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME varchar(100) COLLATE Latin1_General_CI_AS NULL

So here is the code to generate that

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

Run it then save the script for later use. Lets call the script “ChangeCollation.sql”

If you don’t have relationships, primary keys and foreign keys then you don’t need to do the next step.

Step 3: Create a Stored Procedure to Script Indexes and Relationships

Well if you have relationships, primary keys and foreign keys then that’s a good practice but you need to script them as you need to drop those before changing the collation. Initially I thought I can do this with the wizard and choose to script indexes but it does not create on its own the table creation is always included so with a little help from Google I don’t have to write a single piece of code. I found this really good script to do it and I got it from here http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx
I only separated the Create Indexes and Drop Indexes as we need to run a process in the middle.


Here is the Create Index script courtesy of Adam Machanic

/*
Script Table Keys
(C) 2010 Adam Machanic - amachanic@gmail.com
http://sqlblog.com/blogs/adam_machanic/archive/2010/04/04/rejuvinated-script-creates-and-drops-for-candidate-keys-and-referencing-foreign-keys.aspx
This script produces a script of all of the candidate keys (primary keys or unique 
constraints) as well as referencing foreign keys, for the target table. To use, put
SSMS into "results in text" mode and run the script. The output will be a formatted
script that you can cut and paste to use elsewhere.

Don't forget to configure the maximum text size before using. The default is 256
characters--not enough for many cases.

Tools->Options->Query Results->Results to Text->Maximum number of characters->8192
*/
CREATE PROC [dbo].[ScriptCreateTableKeys]
	@table_name SYSNAME
AS
BEGIN
	SET NOCOUNT ON

	--Note: Disabled keys and constraints are ignored
	--TODO: Drop and re-create referencing XML indexes, FTS catalogs

	DECLARE @crlf CHAR(2)
	SET @crlf = CHAR(13) + CHAR(10)
	DECLARE @version CHAR(4)
	SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
	DECLARE @object_id INT
	SET @object_id = OBJECT_ID(@table_name)
	DECLARE @sql NVARCHAR(MAX)

	IF @version NOT IN ('2005', '2008')
	BEGIN
		RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
		RETURN
	END

	SET @sql = '' +
		'SELECT ' +
			'CASE ' +
				'WHEN 1 IN (i.is_primary_key, i.is_unique_constraint) THEN ' +
					'''ALTER TABLE '' + ' +
						'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
						'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
					'''ADD '' + ' +
						'CASE k.is_system_named ' +
							'WHEN 0 THEN ''CONSTRAINT '' + QUOTENAME(k.name) + @crlf ' +
							'ELSE '''' ' +
						'END + ' +
					'CASE k.type ' +
						'WHEN ''UQ'' THEN ''UNIQUE'' ' +
						'ELSE ''PRIMARY KEY'' ' +
					'END + '' '' + ' +
					'i.type_desc  + @crlf + ' +
					'kc.key_columns + @crlf ' +
				'ELSE ' +
					'''CREATE UNIQUE '' + i.type_desc + '' INDEX '' + ' +
						'QUOTENAME(i.name) + @crlf + ' +
					'''ON '' + ' +
						'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
						'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
					'kc.key_columns + @crlf + ' +
					'COALESCE ' +
					'( ' +
						'''INCLUDE '' + @crlf + ' +
						'''( '' + @crlf + ' +
							'STUFF ' +
							'( ' +
								'( ' +
									'SELECT ' +
									'( ' +
										'SELECT ' +
											''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
										'FROM sys.index_columns AS ic ' +
										'JOIN sys.columns AS c ON ' +
											'c.object_id = ic.object_id ' +
											'AND c.column_id = ic.column_id ' +
										'WHERE ' +
											'ic.object_id = i.object_id ' +
											'AND ic.index_id = i.index_id ' +
											'AND ic.is_included_column = 1 ' +
										'ORDER BY ' +
											'ic.key_ordinal ' +
										'FOR XML PATH(''''), TYPE ' +
									').value(''.'', ''VARCHAR(MAX)'') ' +
								'), ' +
								'1, ' +
								'3, ' +
								''''' ' +
							') + @crlf + ' +
						''')'' + @crlf, ' +
						''''' ' +
					') ' +
			'END + ' +
			'''WITH '' + @crlf + ' +
			'''('' + @crlf + ' +
				''' PAD_INDEX = '' + ' +
						'CASE CONVERT(VARCHAR, i.is_padded) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				'CASE i.fill_factor ' +
					'WHEN 0 THEN '''' ' +
					'ELSE ' +
						''' FILLFACTOR = '' + ' +
								'CONVERT(VARCHAR, i.fill_factor) + '','' + @crlf ' +
				'END + ' +
				''' IGNORE_DUP_KEY = '' + ' +
						'CASE CONVERT(VARCHAR, i.ignore_dup_key) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				''' ALLOW_ROW_LOCKS = '' + ' +
						'CASE CONVERT(VARCHAR, i.allow_row_locks) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + '','' + @crlf + ' +
				''' ALLOW_PAGE_LOCKS = '' + ' +
						'CASE CONVERT(VARCHAR, i.allow_page_locks) ' +
							'WHEN 1 THEN ''ON'' ' +
							'ELSE ''OFF'' ' +
						'END + ' +
				CASE @version
					WHEN '2005' THEN ''
					ELSE			 
						''','' + @crlf + ' +
						''' DATA_COMPRESSION = '' + ' +
							'( ' +
								'SELECT ' +
									'CASE ' +
										'WHEN MIN(p.data_compression_desc) = MAX(p.data_compression_desc) THEN MAX(p.data_compression_desc) ' +
										'ELSE ''[PARTITIONS USE MULTIPLE COMPRESSION TYPES]'' ' +
									'END ' +
								'FROM sys.partitions AS p ' +
								'WHERE ' +
									'p.object_id = i.object_id ' +
									'AND p.index_id = i.index_id ' +
							') '
				END + '+ @crlf + ' +
			''') '' + @crlf + ' +
			'''ON '' + ds.data_space + '';'' + ' +
				'@crlf + @crlf COLLATE database_default AS [-- Create Candidate Keys] ' +
		'FROM sys.indexes AS i ' +
		'LEFT OUTER JOIN sys.key_constraints AS k ON ' +
			'k.parent_object_id = i.object_id ' +
			'AND k.unique_index_id = i.index_id ' +
		'CROSS APPLY ' +
		'( ' +
			'SELECT ' +
				'''( '' + @crlf + ' +
					'STUFF ' +
					'( ' +
						'( ' +
							'SELECT ' +
							'( ' +
								'SELECT ' +
									''','' + @crlf + '' '' + QUOTENAME(c.name) AS [text()] ' +
								'FROM sys.index_columns AS ic ' +
								'JOIN sys.columns AS c ON ' +
									'c.object_id = ic.object_id ' +
									'AND c.column_id = ic.column_id ' +
								'WHERE ' +
									'ic.object_id = i.object_id ' +
									'AND ic.index_id = i.index_id ' +
									'AND ic.key_ordinal > 0 ' +
								'ORDER BY ' +
									'ic.key_ordinal ' +
								'FOR XML PATH(''''), TYPE ' +
							').value(''.'', ''VARCHAR(MAX)'') ' +
						'), ' +
						'1, ' +
						'3, ' +
						''''' ' +
					') + @crlf + ' +
				''')'' ' +
		') AS kc (key_columns) ' +
		'CROSS APPLY ' +
		'( ' +
			'SELECT ' +
				'QUOTENAME(d.name) + ' +
					'CASE d.type ' +
						'WHEN ''PS'' THEN ' +
							'+ ' +
							'''('' + ' +
								'( ' +
									'SELECT ' +
										'QUOTENAME(c.name) ' +
									'FROM sys.index_columns AS ic ' +
									'JOIN sys.columns AS c ON ' +
										'c.object_id = ic.object_id ' +
										'AND c.column_id = ic.column_id ' +
									'WHERE ' +
										'ic.object_id = i.object_id ' +
										'AND ic.index_id = i.index_id ' +
										'AND ic.partition_ordinal = 1 ' +
								') + ' +
							''')'' ' +
						'ELSE '''' ' +
					'END ' +
			'FROM sys.data_spaces AS d ' +
			'WHERE ' +
				'd.data_space_id = i.data_space_id ' +
		') AS ds (data_space) ' +
		'WHERE ' +
			'i.object_id = @object_id ' +
			'AND i.is_unique = 1 ' +
			--filtered and hypothetical indexes cannot be candidate keys
			CASE @version
				WHEN '2008' THEN 'AND i.has_filter = 0 '
				ELSE ''
			END +
			'AND i.is_hypothetical = 0 ' +
			'AND i.is_disabled = 0 ' +
		'ORDER BY ' +
			'i.index_id '

	EXEC sp_executesql
@sql,
		N'@object_id INT, @crlf CHAR(2)',
		@object_id, @crlf

	SELECT
		'ALTER TABLE ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(fk.parent_object_id)) + @crlf +
		CASE fk.is_not_trusted
			WHEN 0 THEN 'WITH CHECK '
			ELSE 'WITH NOCHECK '
		END + 
			'ADD ' +
				CASE fk.is_system_named
					WHEN 0 THEN 'CONSTRAINT ' + QUOTENAME(name) + @crlf
					ELSE ''
				END +
		'FOREIGN KEY ' + @crlf + 
		'( ' + @crlf + 
			STUFF
(
(
					SELECT
(
						SELECT 
							',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
						FROM sys.foreign_key_columns AS fc
						JOIN sys.columns AS c ON
							c.object_id = fc.parent_object_id
							AND c.column_id = fc.parent_column_id
						WHERE 
							fc.constraint_object_id = fk.object_id
						ORDER BY
							fc.constraint_column_id
						FOR XML PATH(''), TYPE
					).value('.', 'VARCHAR(MAX)')
				),
				1,
				3,
				''
			) + @crlf + 
		') ' +
		'REFERENCES ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + @crlf +
		'( ' + @crlf + 
			STUFF
(
(
					SELECT
(
						SELECT 
							',' + @crlf + ' ' + QUOTENAME(c.name) AS [text()]
						FROM sys.foreign_key_columns AS fc
						JOIN sys.columns AS c ON
							c.object_id = fc.referenced_object_id
							AND c.column_id = fc.referenced_column_id
						WHERE 
							fc.constraint_object_id = fk.object_id
						ORDER BY
							fc.constraint_column_id
						FOR XML PATH(''), TYPE
					).value('.', 'VARCHAR(MAX)')
				),
				1,
				3,
				''
			) + @crlf + 
		');' + 
			@crlf + @crlf COLLATE database_default AS [-- Create Referencing FKs]
	FROM sys.foreign_keys AS fk
	WHERE
		referenced_object_id = @object_id
		AND is_disabled = 0
	ORDER BY
		key_index_id

END

Step 4: Create Stored Procedure to Script Drop Indexes and Relationships

Now you also need to create the drop scripts, these is the other half of Adam Machanic’s script

CREATE PROC [dbo].[ScriptDropTableKeys]
	@table_name SYSNAME
AS
BEGIN
	SET NOCOUNT ON

	--Note: Disabled keys and constraints are ignored
	--TODO: Drop and re-create referencing XML indexes, FTS catalogs

	DECLARE @crlf CHAR(2)
	SET @crlf = CHAR(13) + CHAR(10)
	DECLARE @version CHAR(4)
	SET @version = SUBSTRING(@@VERSION, LEN('Microsoft SQL Server') + 2, 4)
	DECLARE @object_id INT
	SET @object_id = OBJECT_ID(@table_name)
	DECLARE @sql NVARCHAR(MAX)

	IF @version NOT IN ('2005', '2008')
	BEGIN
		RAISERROR('This script only supports SQL Server 2005 and 2008', 16, 1)
		RETURN
	END

	SELECT
		'ALTER TABLE ' + 
			QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + 
			QUOTENAME(OBJECT_NAME(parent_object_id)) + @crlf +
		'DROP CONSTRAINT ' + QUOTENAME(name) + ';' + 
			@crlf + @crlf COLLATE database_default AS [-- Drop Referencing FKs]
	FROM sys.foreign_keys
	WHERE
		referenced_object_id = @object_id
		AND is_disabled = 0
	ORDER BY
		key_index_id DESC

	SET @sql = '' +
		'SELECT ' +
			'statement AS [-- Drop Candidate Keys] ' +
		'FROM ' +
		'( ' +
			'SELECT ' +
				'CASE ' +
					'WHEN 1 IN (i.is_unique_constraint, i.is_primary_key) THEN ' +
						'''ALTER TABLE '' + ' +
							'QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)) + ''.'' + ' +
							'QUOTENAME(OBJECT_NAME(i.object_id)) + @crlf + ' +
						'''DROP CONSTRAINT '' + QUOTENAME(i.name) + '';'' + ' +
							'@crlf + @crlf COLLATE database_default ' +
					'ELSE ' +
						'''DROP INDEX '' + QUOTENAME(i.name) + @crlf + ' +
						'''ON '' + ' +
							'QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''.'' + ' +
							'QUOTENAME(OBJECT_NAME(object_id)) + '';'' + ' +
								'@crlf + @crlf COLLATE database_default ' +
				'END AS statement, ' +
				'i.index_id ' +
			'FROM sys.indexes AS i ' +
			'WHERE ' +
				'i.object_id = @object_id ' +
				'AND i.is_unique = 1 ' +
				--filtered and hypothetical indexes cannot be candidate keys
				CASE @version
					WHEN '2008' THEN 'AND i.has_filter = 0 '
					ELSE ''
				END +
				'AND i.is_hypothetical = 0 ' +
				'AND i.is_disabled = 0 ' +
		') AS x ' +
		'ORDER BY ' +
			'index_id DESC '

	EXEC sp_executesql 
@sql,
		N'@object_id INT, @crlf CHAR(2)',
		@object_id, @crlf

END

Step 5: Bringing them all together

Now you have the two stored procedure all you have to do is to loop though all tables in you database and pass that as the parameter of the Stored Procedure. First we use the ScriptCreateTableKeys.

DECLARE @TableName nvarchar(255)
DECLARE MyTableCursor Cursor
FOR 
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
	EXEC ScriptCreateTableKeys @TableName

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

Then lets use the ScriptDropTableKeys

DECLARE @TableName nvarchar(255)
DECLARE MyTableCursor Cursor
FOR 
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
	BEGIN
	 EXEC ScriptDropTableKeys @TableName

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

Just make sure when you execute them output the results as text so you can easily copy and paste the results.
Save the first results as “CreateKeysAndIndexes.sql” and the second as “DropKeysAndIndexes.sql”


Step 6: Run you saved scripts

In this order run your scripts and wait for the results, time wait might vary depending on your database size.

a. DropKeysAndIndexes.sql
b. ChangeCollation.sql
c. CreateKeysAndIndexes.sql

Recommended

11 thoughts on “The Easy way of changing Collation of all Database objects in SQL Server”

  1. ttrus

    The change collation sample is missing its variable declarations. Thanks for sharing your ideas on changing collation.

    Reply ↓
    1. tBone

      Yes, variable declarations are missing, including the declaration for the MyTableCursor

      Reply ↓
  2. kris

    I am failing at Step-1 itself as my database has check constraints and computed columns. I cannot change the collation of the database from Properties dialog. I want to change the collation from ‘SQL_Latin1_General_Pref_CP1_CI_AS’ to ‘SQL_Latin1_General_CP1_CI_AS’.

    I am stuck at the below error(partial). All check constraints and computed columns are schema bound.

    Any help/advice please?

    Alter failed for Database ‘testdb’. (Microsoft.SqlServer.Smo)
    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    The object ‘pager_phone_format’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

    ALTER DATABASE failed. The default collation of database ‘testdb’ cannot be set to SQL_Latin1_General_CP1_CI_AS. (Microsoft SQL Server, Error: 5075)

    Reply ↓
      1. Kris

        I am looking for a script/scripts that will script out all the check constraints like the stored procs you provided for foriegn keys and also the drop scripts to remove the dependency. Was hoping if you have them already. Thanks for the reply!

        Reply ↓
  3. Victor

    You guys are the bomb….Thanks for saving plenty hours of work time to figure this out

    Reply ↓
  4. Jairo.

    In the following link is the first script complete, in order to create de alter table collation… queries…

    This is a wonderfull Post.

    Reply ↓

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.