Change Database Collation

ChangeDatabaseCollation

/************************************************************************************************************************************************************************************************************************************
************************************************************************************************************************************************************************************************************************************
Author		: 	Kin Shah

Bug Identified		The incuded columns were not generated as a part of original script.
and Fixed	:	Modified the script to include the included columns.


Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.

The following disclaimer applies to all code, scripts and demos available on my posts:

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. 

I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: 

(i) 	to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded; 
(ii) 	to include a valid copyright notice on Your software product in which the Sample Code is embedded; and 
(iii) 	to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
************************************************************************************************************************************************************************************************************************************
*************************************************************************************************************************************************************************************************************************************/



DECLARE @FullName VARCHAR(200)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @statName VARCHAR(100)
DECLARE @columnName VARCHAR(100)
DECLARE @column VARCHAR(100)
DECLARE @indexName VARCHAR(100)
DECLARE @indexDesc VARCHAR(500)
DECLARE @indexUnique BIT
DECLARE @constraintName VARCHAR(100)
DECLARE @constraintType VARCHAR(100)
DECLARE @createStatSQL VARCHAR(MAX)
DECLARE @norecompute BIT
DECLARE @count INT
DECLARE @colLength INT
DECLARE @dataType VARCHAR(25)
DECLARE @keyColumn VARCHAR(MAX)
DECLARE @keyField VARCHAR(MAX)
DECLARE @definition VARCHAR(100)
DECLARE @isNullable VARCHAR(3)
DECLARE @NullStatus VARCHAR(8)
DECLARE @Required_Collation VARCHAR(100)
DECLARE @ViewSchema VARCHAR(100)
DECLARE @ViewName VARCHAR(100)
DECLARE @text VARCHAR(200)
DECLARE @view VARCHAR(200)
DECLARE @FKCOLUMNS VARCHAR(MAX)
DECLARE @PKCOLUMNS VARCHAR(MAX)
DECLARE @COUNTER INT
DECLARE @func_schema VARCHAR(100)
DECLARE @func_name VARCHAR(100)
DECLARE @full_func_name VARCHAR(100)
DECLARE @def_line VARCHAR(MAX)
DECLARE @dropFUNC NVARCHAR(MAX)
DECLARE @ALLdropFUNC NVARCHAR(MAX)
DECLARE @createFK VARCHAR(MAX)
DECLARE @included_columns VARCHAR(max)

SET NOCOUNT ON
SET @Required_Collation = 'SQL_Latin1_General_CP1_CI_AS'

IF OBJECT_ID('tempdb..#collationsToChange') IS NOT NULL
	DROP TABLE #collationsToChange

-- Store the collations
SELECT T.TABLE_SCHEMA
	,T.TABLE_NAME
	,C.COLUMN_NAME
	,C.DATA_TYPE
	,C.CHARACTER_MAXIMUM_LENGTH
	,C.IS_NULLABLE
INTO #collationsToChange
FROM [INFORMATION_SCHEMA].[TABLES] T
	,[INFORMATION_SCHEMA].[COLUMNS] C
	,[sys].[columns] SC
WHERE T.TABLE_SCHEMA = C.TABLE_SCHEMA
	AND T.TABLE_NAME = C.TABLE_NAME
	AND SC.[object_id] = object_id(T.TABLE_SCHEMA + '.' + T.TABLE_NAME)
	AND SC.[name] = C.COLUMN_NAME
	AND T.TABLE_TYPE = 'BASE TABLE'
	AND C.COLLATION_NAME <> @Required_Collation
	AND SC.[is_computed] = 0

IF (
		SELECT count(*)
		FROM #collationsToChange
		) = 0
	PRINT 'No columns need altering to : ' + @Required_Collation
ELSE
BEGIN
	IF OBJECT_ID('tempdb..#func_drop') IS NOT NULL
		DROP TABLE #func_drop

	CREATE TABLE #func_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#func_create') IS NOT NULL
		DROP TABLE #func_create

	CREATE TABLE #func_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#fk_drop') IS NOT NULL
		DROP TABLE #fk_drop

	CREATE TABLE #fk_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#fk_create') IS NOT NULL
		DROP TABLE #fk_create

	CREATE TABLE #fk_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#constraint_drop') IS NOT NULL
		DROP TABLE #constraint_drop

	CREATE TABLE #constraint_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#constraint_create') IS NOT NULL
		DROP TABLE #constraint_create

	CREATE TABLE #constraint_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#index_drop') IS NOT NULL
		DROP TABLE #index_drop

	CREATE TABLE #index_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#index_create') IS NOT NULL
		DROP TABLE #index_create

	CREATE TABLE #index_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#view_drop') IS NOT NULL
		DROP TABLE #view_drop

	CREATE TABLE #view_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#view_create') IS NOT NULL
		DROP TABLE #view_create

	CREATE TABLE #view_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#view_index_create') IS NOT NULL
		DROP TABLE #view_index_create

	CREATE TABLE #view_index_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#stats_drop') IS NOT NULL
		DROP TABLE #stats_drop

	CREATE TABLE #stats_drop (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#stats_create') IS NOT NULL
		DROP TABLE #stats_create

	CREATE TABLE #stats_create (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#collation') IS NOT NULL
		DROP TABLE #collation

	CREATE TABLE #collation (def_line VARCHAR(max) NULL)

	IF OBJECT_ID('tempdb..#indexNames') IS NOT NULL
		DROP TABLE #indexNames

	CREATE TABLE #indexNames (
		index_name SYSNAME
		,index_description VARCHAR(max)
		,index_keys NVARCHAR(max)
		)

	/* change made by Kin .. starts*/
	IF OBJECT_ID('tempdb..#included_columns') IS NOT NULL
		DROP TABLE #included_columns;

	CREATE TABLE #included_columns (
		index_name SYSNAME 
		,index_description VARCHAR(max)
		,index_keys NVARCHAR(max)
		,included_columns NVARCHAR(max)
		);

	INSERT INTO #included_columns
	SELECT Ind.[name] AS index_name
		,ind.type_desc AS index_description
		,SUBSTRING((
				SELECT ', ' + AC.NAME
				FROM sys.[tables] AS T
				INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
				INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
				INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				WHERE Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 0
				ORDER BY IC.key_ordinal
				FOR XML PATH('')
				), 2, 8000) AS index_keys
		,SUBSTRING((
				SELECT ', ' + AC.NAME
				FROM sys.[tables] AS T
				INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
				INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
					AND I.[index_id] = IC.[index_id]
				INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
					AND IC.[column_id] = AC.[column_id]
				WHERE Ind.[object_id] = I.[object_id]
					AND Ind.index_id = I.index_id
					AND IC.is_included_column = 1
				ORDER BY IC.key_ordinal
				FOR XML PATH('')
				), 2, 8000) AS included_columns
	FROM sys.[indexes] Ind
	INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
	INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
	where Ind.[name] is not NULL
	/*
	Filter out NULL index names
	Cannot insert the value NULL into column 'index_name', table 'tempdb.dbo.#included_columns___________________________________________________________________________________________________000000001757'; column does not allow nulls. INSERT fails.
	*/
	ORDER BY (Sch.NAME + Tab.[name])

	/* change made by Kin .. ends */
	-- Create script for all Functions
	SET @ALLdropFUNC = ''

	DECLARE cursor_functions CURSOR
	FOR
	SELECT ROUTINE_SCHEMA
		,ROUTINE_NAME
	FROM INFORMATION_SCHEMA.ROUTINES
	WHERE ROUTINE_TYPE = 'FUNCTION'
	ORDER BY ROUTINE_SCHEMA
		,ROUTINE_NAME

	OPEN cursor_functions

	FETCH NEXT
	FROM cursor_functions
	INTO @func_schema
		,@func_name

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @full_func_name = '[' + @func_schema + '].[' + @func_name + ']'

		INSERT #func_drop
		VALUES ('PRINT ''DROP FUNCTION : ' + @full_func_name + '''')

		INSERT #func_drop
		VALUES ('GO')

		INSERT #func_drop
		VALUES ('DROP FUNCTION ' + @full_func_name)

		INSERT #func_drop
		VALUES ('GO')

		INSERT #func_create
		VALUES ('PRINT ''CREATE FUNCTION : ' + @full_func_name + '''')

		INSERT #func_create
		VALUES ('GO')

		INSERT #func_create
		EXEC sp_helptext @full_func_name

		INSERT #func_create
		VALUES ('GO')

		SET @ALLdropFUNC = @ALLdropFUNC + @dropFUNC

		FETCH NEXT
		FROM cursor_functions
		INTO @func_schema
			,@func_name
	END

	CLOSE cursor_functions

	DEALLOCATE cursor_functions

	-- Create script for ALL VIEWS
	--Get the views
	DECLARE viewsCursor CURSOR FORWARD_ONLY
	FOR
	SELECT TABLE_SCHEMA
		,TABLE_NAME
	FROM INFORMATION_SCHEMA.VIEWS

	OPEN viewsCursor

	FETCH NEXT
	FROM viewsCursor
	INTO @ViewSchema
		,@ViewName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @view = '[' + @ViewSchema + '].[' + @ViewName + ']'

		INSERT #view_drop
		VALUES ('PRINT ''DROP VIEW : ' + @view + '''')

		INSERT #view_drop
		VALUES ('GO')

		INSERT #view_drop
		VALUES ('DROP VIEW ' + @view)

		INSERT #view_drop
		VALUES ('GO')

		INSERT #view_create
		VALUES ('PRINT ''CREATE VIEW : ' + @view + '''')

		INSERT #view_create
		VALUES ('GO')

		INSERT #view_create
		EXEC sp_helptext @view

		INSERT #view_create
		VALUES ('GO')

		-- Get indexes created on the current view
		DECLARE ViewIndexesCursor CURSOR FORWARD_ONLY
		FOR
		SELECT i.NAME AS index_name
			,i.type_desc
			,is_unique
		FROM sys.indexes AS i
		WHERE is_hypothetical = 0
			AND i.index_id <> 0
			AND is_unique_constraint <> 1
			AND i.object_id = OBJECT_ID(@view)

		OPEN ViewIndexesCursor

		FETCH NEXT
		FROM ViewIndexesCursor
		INTO @indexName
			,@indexDesc
			,@indexUnique

		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @keyColumn = index_keys
			FROM #indexNames
			WHERE index_name = @indexName

			INSERT #view_index_create
			VALUES ('PRINT ''CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @view + ' (' + @keyColumn + ')''')

			INSERT #view_index_create
			VALUES ('CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @view + ' (' + @keyColumn + ')')

			FETCH NEXT
			FROM ViewIndexesCursor
			INTO @indexName
				,@indexDesc
				,@indexUnique
		END

		CLOSE ViewIndexesCursor

		DEALLOCATE ViewIndexesCursor

		FETCH NEXT
		FROM viewsCursor
		INTO @ViewSchema
			,@ViewName
	END

	CLOSE viewsCursor

	DEALLOCATE viewsCursor

	-------------------------------------------------------------------
	-- Create script for ALL Foreign Keys
	DECLARE cursor_tables CURSOR
	FOR
	SELECT TABLE_SCHEMA
		,TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

	OPEN cursor_tables

	FETCH NEXT
	FROM cursor_tables
	INTO @schemaName
		,@tableName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE @cmd NVARCHAR(MAX)
		DECLARE @FK_NAME SYSNAME
			,@FK_OBJECTID INT
			,@FK_DISABLED INT
			,@FK_NOT_FOR_REPLICATION INT
			,@DELETE_RULE SMALLINT
			,@UPDATE_RULE SMALLINT
			,@FKTABLE_NAME SYSNAME
			,@FKTABLE_OWNER SYSNAME
			,@PKTABLE_NAME SYSNAME
			,@PKTABLE_OWNER SYSNAME
			,@FKCOLUMN_NAME SYSNAME
			,@PKCOLUMN_NAME SYSNAME
			,@CONSTRAINT_COLID INT

		DECLARE cursor_fkeys CURSOR
		FOR
		SELECT Fk.NAME
			,Fk.OBJECT_ID
			,Fk.is_disabled
			,Fk.is_not_for_replication
			,Fk.DELETE_referential_action
			,Fk.update_referential_action
			,OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name
			,schema_name(Fk.schema_id) AS Fk_table_schema
			,TbR.NAME AS Pk_table_name
			,schema_name(TbR.schema_id) Pk_table_schema
		FROM sys.foreign_keys Fk
		LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   
		WHERE TbR.NAME = @tableName
			AND schema_name(TbR.schema_id) = @schemaName

		OPEN cursor_fkeys

		FETCH NEXT
		FROM cursor_fkeys
		INTO @FK_NAME
			,@FK_OBJECTID
			,@FK_DISABLED
			,@FK_NOT_FOR_REPLICATION
			,@DELETE_RULE
			,@UPDATE_RULE
			,@FKTABLE_NAME
			,@FKTABLE_OWNER
			,@PKTABLE_NAME
			,@PKTABLE_OWNER

		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- create statement for dropping FK and also for recreating FK  
			-- drop statement  
			INSERT #fk_drop
			VALUES ('PRINT ''ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  DROP CONSTRAINT [' + @FK_NAME + ']''')

			INSERT #fk_drop
			VALUES ('GO')

			INSERT #fk_drop
			VALUES ('ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  DROP CONSTRAINT [' + @FK_NAME + ']')

			INSERT #fk_drop
			VALUES ('GO')

			-- create process  
			-- create cursor to get FK columns  
			DECLARE cursor_fkeyCols CURSOR
			FOR
			SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name
				,COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
			FROM sys.foreign_keys Fk
			LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id
			INNER JOIN sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
			WHERE TbR.NAME = @tableName
				AND schema_name(TbR.schema_id) = @schemaName
				AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008  
			ORDER BY Fk_Cl.constraint_column_id

			OPEN cursor_fkeyCols

			FETCH NEXT
			FROM cursor_fkeyCols
			INTO @FKCOLUMN_NAME
				,@PKCOLUMN_NAME

			SET @COUNTER = 1
			SET @FKCOLUMNS = ''
			SET @PKCOLUMNS = ''

			WHILE @@FETCH_STATUS = 0
			BEGIN
				IF @COUNTER > 1
				BEGIN
					SET @FKCOLUMNS = @FKCOLUMNS + ','
					SET @PKCOLUMNS = @PKCOLUMNS + ','
				END

				SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
				SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
				SET @COUNTER = @COUNTER + 1

				FETCH NEXT
				FROM cursor_fkeyCols
				INTO @FKCOLUMN_NAME
					,@PKCOLUMN_NAME
			END

			CLOSE cursor_fkeyCols

			DEALLOCATE cursor_fkeyCols

			-- generate create FK statement  
			SET @createFK = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' + CASE @FK_DISABLED
					WHEN 0
						THEN ' CHECK '
					WHEN 1
						THEN ' NOCHECK '
					END + ' ADD CONSTRAINT [' + @FK_NAME + '] FOREIGN KEY (' + @FKCOLUMNS + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] (' + @PKCOLUMNS + ') ON UPDATE ' + CASE @UPDATE_RULE
					WHEN 0
						THEN ' NO ACTION '
					WHEN 1
						THEN ' CASCADE '
					WHEN 2
						THEN ' SET_NULL '
					END + ' ON DELETE ' + CASE @DELETE_RULE
					WHEN 0
						THEN ' NO ACTION '
					WHEN 1
						THEN ' CASCADE '
					WHEN 2
						THEN ' SET_NULL '
					END + '' + CASE @FK_NOT_FOR_REPLICATION
					WHEN 0
						THEN ''
					WHEN 1
						THEN ' NOT FOR REPLICATION '
					END

			INSERT #fk_create
			VALUES ('PRINT ''' + @createFK + '''')

			INSERT #fk_create
			VALUES ('GO')

			INSERT #fk_create
			VALUES (@createFK)

			INSERT #fk_create
			VALUES ('GO')

			FETCH NEXT
			FROM cursor_fkeys
			INTO @FK_NAME
				,@FK_OBJECTID
				,@FK_DISABLED
				,@FK_NOT_FOR_REPLICATION
				,@DELETE_RULE
				,@UPDATE_RULE
				,@FKTABLE_NAME
				,@FKTABLE_OWNER
				,@PKTABLE_NAME
				,@PKTABLE_OWNER
		END

		CLOSE cursor_fkeys

		DEALLOCATE cursor_fkeys

		FETCH NEXT
		FROM cursor_tables
		INTO @schemaName
			,@tableName
	END

	CLOSE cursor_tables

	DEALLOCATE cursor_tables

	-------------------------------------------------------------------
	DECLARE tablescursor CURSOR FORWARD_ONLY
	FOR
	SELECT TABLE_SCHEMA
		,TABLE_NAME
	FROM #collationsToChange
	GROUP BY TABLE_SCHEMA
		,TABLE_NAME

	OPEN tablescursor

	FETCH NEXT
	FROM tablescursor
	INTO @SchemaName
		,@TableName

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @FullName = '[' + @SchemaName + '].[' + @tableName + ']'

		INSERT #collation
		VALUES ('PRINT ''----------------------------------------------------------------------''')

		INSERT #collation
		VALUES ('PRINT ''-- Processing table: ' + @FullName + '''')

		INSERT #collation
		VALUES ('PRINT ''----------------------------------------------------------------------''')

		INSERT #collation
		VALUES ('GO')

		INSERT #constraint_drop
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #constraint_drop
		VALUES ('GO')

		INSERT #constraint_create
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #constraint_create
		VALUES ('GO')

		INSERT #index_drop
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #index_drop
		VALUES ('GO')

		INSERT #index_create
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #index_create
		VALUES ('GO')

		INSERT #stats_drop
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #stats_drop
		VALUES ('GO')

		INSERT #stats_create
		VALUES ('PRINT ''-- Table: ' + @FullName + '''')

		INSERT #stats_create
		VALUES ('GO')

		DELETE #indexNames

		IF (
				SELECT count(*)
				FROM sys.index_columns
				WHERE object_id = object_id(@FullName)
				) > 0
		BEGIN
			--Grab the index info from the system stored proc
			INSERT INTO #indexNames
			EXEC sp_helpindex @FullName
		END

		--Get the constraints that are not FOREIGN KEYS
		DECLARE constraintsCursor CURSOR FORWARD_ONLY
		FOR
		SELECT Constraint_Name
			,Constraint_Type
		FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
		WHERE table_name = @TableName
			AND table_schema = @SchemaName
			AND Constraint_Type <> 'FOREIGN KEY'

		OPEN constraintsCursor

		FETCH NEXT
		FROM constraintsCursor
		INTO @constraintName
			,@constraintType

		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT #constraint_drop
			VALUES ('PRINT ''          DROP CONSTRAINT [' + @constraintName + ']''')

			INSERT #constraint_drop
			VALUES ('GO')

			INSERT #constraint_drop
			VALUES ('ALTER TABLE ' + @FullName + ' DROP CONSTRAINT [' + @constraintName + ']')

			INSERT #constraint_drop
			VALUES ('GO')

			IF @constraintType = 'CHECK'
			BEGIN
				SELECT @definition = DEFINITION
				FROM sys.check_constraints
				WHERE [name] = @constraintName
					AND [object_id] = object_id(@FullName)

				INSERT #constraint_create
				VALUES ('PRINT ''          ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @definition + ')''')

				INSERT #constraint_create
				VALUES ('GO')

				INSERT #constraint_create
				VALUES ('ALTER TABLE ' + @FullName + ' WITH CHECK ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @definition + ')')

				INSERT #constraint_create
				VALUES ('GO')
			END
			ELSE
			BEGIN
				SELECT @keyField = index_keys
				FROM #indexNames
				WHERE index_name = @constraintName

				INSERT #constraint_create
				VALUES ('PRINT ''          ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @keyField + ')''')

				INSERT #constraint_create
				VALUES ('GO')

				INSERT #constraint_create
				VALUES ('ALTER TABLE ' + @FullName + ' WITH CHECK ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @keyField + ')')

				INSERT #constraint_create
				VALUES ('GO')
			END

			FETCH NEXT
			FROM constraintsCursor
			INTO @constraintName
				,@constraintType
		END

		CLOSE constraintsCursor

		DEALLOCATE constraintsCursor

		-- Get indexes but do not get primary keys as they cannot be dropped in this way
		-- They get dropped as constraints above
		DECLARE indexesCursor CURSOR FORWARD_ONLY
		FOR
		SELECT i.NAME AS index_name
			,i.type_desc
			,is_unique
		FROM sys.indexes AS i
		WHERE is_hypothetical = 0
			AND i.index_id <> 0
			AND is_unique_constraint <> 1
			AND i.object_id = OBJECT_ID(@fullName)
			--**************		
			AND i.NAME NOT LIKE 'PK%' -- is_primary_key != 1 --

		OPEN indexesCursor

		FETCH NEXT
		FROM indexesCursor
		INTO @indexName
			,@indexDesc
			,@indexUnique

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--print 'dropping '+@indexName
			SELECT @keyColumn = index_keys
			FROM #indexNames
			WHERE index_name = @indexName

			SELECT @included_columns = included_columns
			FROM #included_columns
			WHERE index_name = @indexName

			--select * from #included_columns where index_name = 'I_Staging_Investor_ComplianceId'
			--and included_columns is null
			INSERT #index_drop
			VALUES ('PRINT ''          DROP INDEX [' + @indexName + '] ON ' + @fullName + '''')

			INSERT #index_drop
			VALUES ('GO')

			INSERT #index_drop
			VALUES ('DROP INDEX [' + @indexName + '] ON ' + @fullName)

			INSERT #index_drop
			VALUES ('GO')

			--************************
			INSERT #index_create
			VALUES (
				'PRINT ''          CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @fullName + ' (' + @keyColumn + ')' + CASE 
					WHEN @included_columns IS NOT NULL
						THEN ' include(' + @included_columns + ')'''
					ELSE ''''
					END
				) --# in print you need quote to complete the print statement

			INSERT #index_create
			VALUES ('GO')

			INSERT #index_create
			VALUES (
				'CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @fullName + ' (' + @keyColumn + ')' + CASE 
					WHEN @included_columns IS NOT NULL
						THEN ' include(' + @included_columns + ')'
					ELSE ''
					END
				) -- # in create you do not need the quote

			INSERT #index_create
			VALUES ('GO')

			--print 'creating '+@indexName
			FETCH NEXT
			FROM indexesCursor
			INTO @indexName
				,@indexDesc
				,@indexUnique
		END

		CLOSE indexesCursor

		DEALLOCATE indexesCursor

		--Get the use created statistics
		DECLARE statisticsCursor CURSOR FORWARD_ONLY
		FOR
		SELECT [name]
			,[no_recompute]
		FROM sys.stats
		WHERE user_created = 1
			AND object_id = object_id(@FullName)

		OPEN statisticsCursor

		FETCH NEXT
		FROM statisticsCursor
		INTO @statName
			,@norecompute

		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT #stats_drop
			VALUES ('PRINT ''          DROP STATISTICS ' + @FullName + '.[' + @statName + ']''')

			INSERT #stats_drop
			VALUES ('GO')

			INSERT #stats_drop
			VALUES ('DROP STATISTICS ' + @FullName + '.[' + @statName + ']')

			INSERT #stats_drop
			VALUES ('GO')

			SET @createStatSQL = 'CREATE STATISTICS [' + @statName + '] on ' + @FullName + '('

			DECLARE statColumnCursor CURSOR FORWARD_ONLY
			FOR
			SELECT c.NAME
			FROM sys.stats s
			INNER JOIN sys.stats_columns sc ON sc.object_id = s.object_id
				AND sc.stats_id = s.stats_id
			INNER JOIN sys.columns c ON c.object_id = sc.object_id
				AND c.column_id = sc.column_id
			WHERE s.user_created = 1
				AND s.object_id = object_id(@FullName)
				AND s.NAME = @statName

			OPEN statColumnCursor

			FETCH NEXT
			FROM statColumnCursor
			INTO @columnName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @createStatSQL = @createStatSQL + '[' + @columnName + '],'

				FETCH NEXT
				FROM statColumnCursor
				INTO @columnName
			END

			CLOSE statColumnCursor

			DEALLOCATE statColumnCursor

			SET @createStatSQL = substring(@createStatSQL, 1, len(@createStatSQL) - 1)

			IF @norecompute = 1
				SET @createStatSQL = @createStatSQL + ') with NORECOMPUTE'
			ELSE
				SET @createStatSQL = @createStatSQL + ')'

			INSERT #stats_create
			VALUES ('PRINT           ' + @createStatSQL)

			INSERT #stats_create
			VALUES ('GO')

			INSERT #stats_create
			VALUES (@createStatSQL)

			INSERT #stats_create
			VALUES ('GO')

			FETCH NEXT
			FROM statisticsCursor
			INTO @statName
				,@norecompute
		END

		CLOSE statisticsCursor

		DEALLOCATE statisticsCursor

		--Get the columns for this table
		DECLARE columnsCursor CURSOR FORWARD_ONLY
		FOR
		SELECT column_name
			,character_maximum_length
			,data_type
			,is_nullable
		FROM #collationsToChange
		WHERE table_name = @TableName
			AND table_schema = @SchemaName

		OPEN columnsCursor

		FETCH NEXT
		FROM columnsCursor
		INTO @column
			,@colLength
			,@dataType
			,@isNullable

		WHILE @@FETCH_STATUS = 0
		BEGIN
			--ALTER the table to SET the collation
			SET @NullStatus = ''

			IF @isNullable = 'YES'
				SET @NullStatus = 'NULL'
			ELSE
				SET @NullStatus = 'NOT NULL'

			IF @dataType IN (
					'text'
					,'ntext'
					)
			BEGIN
				INSERT #collation
				VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + ' COLLATE ' + @Required_Collation + ' ' + @NullStatus)
			END
			ELSE
			BEGIN
				IF @colLength = - 1 -- MAX --
					INSERT #collation
					VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + '(max) COLLATE ' + @Required_Collation + ' ' + @NullStatus)
				ELSE
					INSERT #collation
					VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + '(' + CONVERT(VARCHAR(6), @colLength) + ') COLLATE ' + @Required_Collation + ' ' + @NullStatus)
			END

			FETCH NEXT
			FROM columnsCursor
			INTO @column
				,@colLength
				,@dataType
				,@isNullable
		END

		CLOSE columnsCursor

		DEALLOCATE columnsCursor

		INSERT #collation
		VALUES ('GO')

		--Get the next table
		FETCH NEXT
		FROM tablescursor
		INTO @SchemaName
			,@TableName
	END

	CLOSE tablescursor

	DEALLOCATE tablescursor

	--------------------------------------------------------------------------
	PRINT ' '
	PRINT 'PRINT '' NOTE: Delete all preceeding lines ***'' '
	PRINT 'PRINT '' '' '
	PRINT 'PRINT ''--------------------------------------------------'''
	PRINT 'PRINT ''--------------------------------------------------'''
	PRINT 'PRINT ''-- ***** Update collation for all tables  ***** --'''
	PRINT 'PRINT ''--------------------------------------------------'''
	PRINT 'PRINT ''--------------------------------------------------'''
	PRINT 'PRINT ''--'''
	PRINT 'PRINT ''-- New collation: ' + @Required_Collation + ''''
	PRINT 'PRINT ''--'''
	PRINT 'PRINT ''--------------------------------------------------'''
	PRINT 'PRINT '' '' '
	PRINT 'GO'

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #fk_drop
			) > 0
	BEGIN
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL FOREIGN KEYS'''
		PRINT 'GO'

		DECLARE cursor_fk CURSOR
		FOR
		SELECT def_line
		FROM #fk_drop

		OPEN cursor_fk

		FETCH NEXT
		FROM cursor_fk
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_fk
			INTO @def_line
		END

		CLOSE cursor_fk

		DEALLOCATE cursor_fk
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #constraint_drop
			) > 0
	BEGIN
		-- Drop Constraints
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL CONSTRAINTS'''
		PRINT 'GO'

		DECLARE cursor_constraint CURSOR
		FOR
		SELECT def_line
		FROM #constraint_drop

		OPEN cursor_constraint

		FETCH NEXT
		FROM cursor_constraint
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_constraint
			INTO @def_line
		END

		CLOSE cursor_constraint

		DEALLOCATE cursor_constraint

		PRINT 'GO'
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #func_drop
			) > 0
	BEGIN
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL FUNCTIONS'''
		PRINT 'GO'

		DECLARE cursor_func CURSOR
		FOR
		SELECT def_line
		FROM #func_drop

		OPEN cursor_func

		FETCH NEXT
		FROM cursor_func
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_func
			INTO @def_line
		END

		CLOSE cursor_func

		DEALLOCATE cursor_func

		PRINT 'GO'
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #view_drop
			) > 0
	BEGIN
		-- Drop Views
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL VIEWS'''
		PRINT 'GO'

		DECLARE cursor_views CURSOR
		FOR
		SELECT def_line
		FROM #view_drop

		OPEN cursor_views

		FETCH NEXT
		FROM cursor_views
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_views
			INTO @def_line
		END

		CLOSE cursor_views

		DEALLOCATE cursor_views

		PRINT 'GO'
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #stats_drop
			) > 0
	BEGIN
		-- Drop Stats
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL user created STATISTICS'''
		PRINT 'GO'

		DECLARE cursor_stats CURSOR
		FOR
		SELECT def_line
		FROM #stats_drop

		OPEN cursor_stats

		FETCH NEXT
		FROM cursor_stats
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_stats
			INTO @def_line
		END

		CLOSE cursor_stats

		DEALLOCATE cursor_stats

		PRINT 'GO'
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #index_drop
			) > 0
	BEGIN
		-- Drop Indexes
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Drop ALL Table INDEXES'''
		PRINT 'GO'

		DECLARE cursor_index CURSOR
		FOR
		SELECT def_line
		FROM #index_drop

		OPEN cursor_index

		FETCH NEXT
		FROM cursor_index
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_index
			INTO @def_line
		END

		CLOSE cursor_index

		DEALLOCATE cursor_index

		PRINT 'GO'
	END

	--------------------------------------------------------------------------
	--------------------------------------------------------------------------
	-- Alter Collation
	PRINT 'PRINT ''------------------------------------------------------------------------------'''
	PRINT 'PRINT ''-- Alter COLLATION'''
	PRINT 'GO'

	DECLARE cursor_alter CURSOR
	FOR
	SELECT def_line
	FROM #collation

	OPEN cursor_alter

	FETCH NEXT
	FROM cursor_alter
	INTO @def_line

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT @def_line

		FETCH NEXT
		FROM cursor_alter
		INTO @def_line
	END

	CLOSE cursor_alter

	DEALLOCATE cursor_alter

	PRINT 'GO'
	--------------------------------------------------------------------------
	--------------------------------------------------------------------------
	-- Change default database collation
	PRINT 'PRINT ''------------------------------------------------------------------------------'''
	PRINT 'PRINT ''-- Alter default database COLLATION'''
	PRINT 'GO'
	PRINT 'alter database ' + db_name() + ' set single_user with rollback immediate'
	PRINT 'alter database ' + db_name() + ' collate ' + @Required_Collation
	PRINT 'alter database ' + db_name() + ' set multi_user'
	PRINT 'GO'

	--------------------------------------------------------------------------
	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #constraint_create
			) > 0
	BEGIN
		-- Recreate Constraints
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate Table CONSTRAINTS'''
		PRINT 'GO'

		DECLARE cursor_constraint CURSOR
		FOR
		SELECT def_line
		FROM #constraint_create

		OPEN cursor_constraint

		FETCH NEXT
		FROM cursor_constraint
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_constraint
			INTO @def_line
		END

		CLOSE cursor_constraint

		DEALLOCATE cursor_constraint
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #index_create
			) > 0
	BEGIN
		-- Recreate Indexes
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate Table INDEXES'''
		PRINT 'GO'

		DECLARE cursor_index CURSOR
		FOR
		SELECT def_line
		FROM #index_create

		OPEN cursor_index

		FETCH NEXT
		FROM cursor_index
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_index
			INTO @def_line
		END

		CLOSE cursor_index

		DEALLOCATE cursor_index
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #stats_create
			) > 0
	BEGIN
		-- Recreate Stats
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate Table STATISTICS'''
		PRINT 'GO'

		DECLARE cursor_stats CURSOR
		FOR
		SELECT def_line
		FROM #stats_create

		OPEN cursor_stats

		FETCH NEXT
		FROM cursor_stats
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_stats
			INTO @def_line
		END

		CLOSE cursor_stats

		DEALLOCATE cursor_stats
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #fk_create
			) > 0
	BEGIN
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate ALL FOREIGN KEYS'''
		PRINT 'GO'

		DECLARE cursor_fk CURSOR
		FOR
		SELECT def_line
		FROM #fk_create

		OPEN cursor_fk

		FETCH NEXT
		FROM cursor_fk
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_fk
			INTO @def_line
		END

		CLOSE cursor_fk

		DEALLOCATE cursor_fk
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #view_create
			) > 0
	BEGIN
		-- Recreate Views
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate ALL VIEWS'''
		PRINT 'GO'

		DECLARE cursor_view CURSOR
		FOR
		SELECT def_line
		FROM #view_create

		OPEN cursor_view

		FETCH NEXT
		FROM cursor_view
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_view
			INTO @def_line
		END

		CLOSE cursor_view

		DEALLOCATE cursor_view
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #index_create
			) > 0
	BEGIN
		-- Recreate Indexes
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate ALL View INDEXES'''
		PRINT 'GO'

		DECLARE cursor_view_index CURSOR
		FOR
		SELECT def_line
		FROM #view_index_create

		OPEN cursor_view_index

		FETCH NEXT
		FROM cursor_view_index
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_view_index
			INTO @def_line
		END

		CLOSE cursor_view_index

		DEALLOCATE cursor_view_index
	END

	--------------------------------------------------------------------------
	IF (
			SELECT count(*)
			FROM #func_create
			) > 0
	BEGIN
		PRINT 'PRINT ''------------------------------------------------------------------------------'''
		PRINT 'PRINT ''-- Recreate ALL FUNCTIONS'''
		PRINT 'GO'

		DECLARE cursor_func CURSOR
		FOR
		SELECT def_line
		FROM #func_create

		OPEN cursor_func

		FETCH NEXT
		FROM cursor_func
		INTO @def_line

		WHILE @@FETCH_STATUS = 0
		BEGIN
			PRINT @def_line

			FETCH NEXT
			FROM cursor_func
			INTO @def_line
		END

		CLOSE cursor_func

		DEALLOCATE cursor_func
	END

	--------------------------------------------------------------------------
	PRINT 'PRINT '' '''
	PRINT 'PRINT ''--------------------------------------------'''
	PRINT 'PRINT ''--------------------------------------------'''
	PRINT 'PRINT ''-- ***** Update collation complete  ***** --'''
	PRINT 'PRINT ''--------------------------------------------'''
	PRINT 'PRINT ''--------------------------------------------'''
END