Change Database Collation

,

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!!
-- 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!!

USE master
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ChangeDatabaseCollation') DROP PROCEDURE sp_ChangeDatabaseCollation
GO

CREATE PROCEDURE sp_ChangeDatabaseCollation (@DBName NVARCHAR(255), @NewCollation NVARCHAR(255), @JustPrintStatements BIT = 1, @DebugPrintSQL BIT = 0) AS
BEGIN
	SET NOCOUNT ON
	DECLARE @CrLf NVARCHAR(10),
			@sql NVARCHAR(MAX),
			@bigsql NVARCHAR(MAX)

	DECLARE @DuplicateUsers TABLE (UserName NVARCHAR(255))

	SET @CrLf = CHAR(13) + CHAR(10)

	IF @NewCollation NOT IN (SELECT name FROM sys.fn_HelpCollations())
	BEGIN
		SET @sql = 'ERROR: cannot proceed with collation change.  The collation name ' + CHAR(39) + @NewCollation + CHAR(39) + ' is invalid.'
		RAISERROR(@sql, 11, 1)
		RETURN 1
	END

	-- If we are changing to a case-insensitive collation, then we need to check for duplicate user names first as any duplicate user names will prevent
	-- the change collation process to a case insensitive collation
	IF EXISTS (SELECT * FROM sys.fn_HelpCollations() WHERE description LIKE '%case-insensitive%' AND name = @NewCollation)
	BEGIN
		SET @sql = 'USE [' + @DBName + ']; SELECT LOWER(name) AS username FROM sys.sysusers GROUP BY LOWER(name) HAVING SUM(1) > 1'
		INSERT INTO @DuplicateUsers
		EXEC sp_executesql @sql

		IF EXISTS (SELECT UserName FROM @DuplicateUsers)
		BEGIN
			SET @sql = 'ERROR: cannot proceed with collation change as there are duplicate users in the Security section of database ' + @DBName + '.  See the Results tab for details.  Please resolve the duplicates before proceeding.'
			RAISERROR(@sql, 11, 1)
			SELECT UserName FROM @DuplicateUsers
			RETURN 1
		END
	END

	-- First drop (if already exists) and create a stored procedure to drop PK's and FK's
	-- Credit for this stored procedure goes to Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx
	SET @sql = REPLACE('IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptDropTableKeys'') DROP PROCEDURE ScriptDropTableKeys', CHAR(39), CHAR(39)+CHAR(39))
	SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + @sql + '''';
	EXEC (@bigsql)
	SET @sql = 
	'CREATE PROCEDURE ScriptDropTableKeys
	@table_name SYSNAME
AS
	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'', ''2012'')
	BEGIN
		RAISERROR(''This script only supports SQL Server 2005, 2008 and 2012'', 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'
	SET @bigsql = 'USE [' + @DBName + ']; EXEC (''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''')'
	EXEC (@bigsql)

	-- Next, drop (if already exists) and create a stored procedure to recreate PK's and FK's
	-- Credit for this stored procedure goes to Adam Machanic - http://sqlblog.com/blogs/adam_machanic/default.aspx
	SET @sql = 'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptCreateTableKeys'') DROP PROCEDURE ScriptCreateTableKeys'
	SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''''
	EXEC (@bigsql)
	SET @sql = CONVERT(NVARCHAR(MAX),
	'CREATE PROCEDURE 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'', ''2012'')
	BEGIN
		RAISERROR(''This script only supports SQL Server 2005, 2008 and 2012'', 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' + @CrLf + 'END')
	SET @bigsql = 'USE [' + @DBName + ']; EXEC (''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''')'
	EXEC (@bigsql)

	-- Now, with the 2 new stored procedures in place (ScriptCreateTableKeys and ScriptDropTablekeys), iterate through all of the user tables in the target database
	-- and generate appropriate CREATE / DROP / ALTER TABLE statements to drop and recreate PK's and FK's.
	DECLARE @MyTableCursor AS CURSOR
	DECLARE @TableName nvarchar(255)
	DECLARE @CreateScripts TABLE (ID INT IDENTITY(1,1), SQLStatement NVARCHAR(MAX))
	DECLARE @DropScripts TABLE (ID INT IDENTITY(1,1), SQLStatement NVARCHAR(MAX))
 
	SET @sql = 'SELECT name FROM sys.tables WHERE [type] = ''U'' and name <> ''sysdiagrams'' ORDER BY name'
	SET @bigsql = 'USE [' + @DBName + ']; SET @cursor = CURSOR FORWARD_ONLY STATIC FOR ' + @sql + ' OPEN @cursor;'
 
	EXEC sys.sp_executesql @bigsql, N'@cursor cursor output', @MyTableCursor output

	FETCH NEXT FROM @MyTableCursor INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = 'USE [' + @DBName + ']; EXEC ScriptCreateTableKeys ''' + @TableName + ''''
		INSERT INTO @CreateScripts (SQLStatement)
		EXEC sp_executesql @sql
		SET @sql = 'USE [' + @DBName + ']; EXEC ScriptDropTableKeys ''' + @TableName + ''''
		INSERT INTO @DropScripts (SQLStatement)
		EXEC sp_executesql @sql
		FETCH NEXT FROM @MyTableCursor INTO @TableName
	END
	CLOSE @MyTableCursor
	DEALLOCATE @MyTableCursor

	-- Now script out statements to recreate check constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE  '' + QuoteName(OBJECT_NAME(so.parent_obj)) + '' ADD CONSTRAINT '' + QuoteName(CONSTRAINT_NAME) + '' CHECK '' + CHECK_CLAUSE
	FROM		INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
	INNER JOIN	sys.sysobjects so ON cc.CONSTRAINT_NAME = so.[name]'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Now script out statements to drop check constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''IF EXISTS (SELECT OBJECT_ID('' + QUOTENAME(CONSTRAINT_NAME, CHAR(39)) + '', '' + CHAR(39) + ''C'' + CHAR(39) + '') FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS) ALTER TABLE  '' + QuoteName(OBJECT_NAME(so.parent_obj)) + '' DROP CONSTRAINT '' + QuoteName(CONSTRAINT_NAME)
	FROM		INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
	INNER JOIN	sys.sysobjects so ON cc.CONSTRAINT_NAME = so.[name]'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Now script out statements to recreate default constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] ADD CONSTRAINT [''+DefCons.name+''] DEFAULT '' + DefCons.definition + '' FOR ['' + Col.name + '']''
	FROM		sys.default_constraints AS DefCons
	INNER JOIN	sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id
	INNER JOIN	sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Now script out statements to drop check constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''
	FROM		sys.default_constraints AS DefCons
	INNER JOIN	sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id
	INNER JOIN	sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	SET @sql = 'USE [' + @DBName + '];
	-- Script out indexes completely, including both PK''s and regular indexes, each clustered or nonclustered.
	-- DOES NOT HANDLE COMPRESSION; that''s ok, since 2008 R2 RTM benchmarking shows it''s faster and results in smaller indexes to insert uncompressed and then compress later
	-- HARDCODES [dbo] schema (i.e. it doesn''t say [JohnDoe].[table], changing that to [dbo].[table]
	-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

	DECLARE
	@idxTableName SYSNAME,
	@idxTableID INT,
	@idxname SYSNAME,
	@idxid INT,
	@colCount INT,
	@colCountWithoutIncludedColumns INT ,
	@IxColumn SYSNAME,
	@IxFirstColumn BIT,
	@ColumnIDInTable INT,
	@ColumnIDInIndex INT,
	@IsIncludedColumn INT,
	@sIncludeCols NVARCHAR(MAX),
	@sIndexCols NVARCHAR(MAX),
	@sSQL NVARCHAR(MAX),
	@sDSQL NVARCHAR(MAX),
	@sParamSQL NVARCHAR(MAX),
	@sFilterSQL NVARCHAR(MAX),
	@location SYSNAME,
	@IndexCount INT,
	@CurrentIndex INT,
	@CurrentCol INT,
	@Name NVARCHAR(128),
	@IsPrimaryKey TINYINT,
	@Fillfactor INT,
	@FilterDefinition NVARCHAR(MAX),
	@IsClustered BIT -- used solely for putting information into the result table


	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexSQL]''))
	DROP TABLE [dbo].[#IndexSQL]

	CREATE TABLE #IndexSQL
	( TableName NVARCHAR(128) NOT NULL
	 ,IndexName NVARCHAR(128) NOT NULL
	 ,IsClustered BIT NOT NULL
	 ,IsPrimaryKey BIT NOT NULL
	 ,IndexCreateSQL NVARCHAR(max) NOT NULL
	 ,IndexDropSQL NVARCHAR(MAX) NOT NULL
	)

	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexListing]''))
	DROP TABLE [dbo].[#IndexListing]

	CREATE TABLE #IndexListing
	(
	[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ObjectID] INT NOT NULL,
	[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IndexID] INT NOT NULL,
	[IsPrimaryKey] TINYINT NOT NULL,
	[FillFactor] INT,
	[FilterDefinition] NVARCHAR(MAX) NULL
	)

	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#ColumnListing]''))
	DROP TABLE [dbo].[#ColumnListing]

	CREATE TABLE #ColumnListing
	(
	[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[ColumnIDInTable] INT NOT NULL,
	[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ColumnIDInIndex] INT NOT NULL,
	[IsIncludedColumn] BIT NULL
	)

	INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
	SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.is_primary_key, si.fill_factor, si.filter_definition
	FROM sys.indexes si
	LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON si.name = tc.CONSTRAINT_NAME AND OBJECT_NAME(si.object_id) = tc.TABLE_NAME
	WHERE OBJECTPROPERTY(si.object_id, ''IsUserTable'') = 1 AND OBJECT_NAME(si.object_id) <> ''sysdiagrams''
	ORDER BY OBJECT_NAME(si.object_id), si.index_id

	SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

	WHILE @CurrentIndex <= @IndexCount
	BEGIN

	SELECT @idxTableName = [TableName],
	@idxTableID = [ObjectID],
	@idxname = [IndexName],
	@idxid = [IndexID],
	@IsPrimaryKey = [IsPrimaryKey],
	@FillFactor = [FILLFACTOR],
	@FilterDefinition = [FilterDefinition]
	FROM #IndexListing
	WHERE [IndexListingID] = @CurrentIndex

	-- So - it is either an index or a constraint
	-- Check if the index is unique
	IF (@IsPrimaryKey = 1)
	BEGIN
	 SET @sSQL = ''ALTER TABLE [dbo].['' + @idxTableName + ''] ADD CONSTRAINT ['' + @idxname + ''] PRIMARY KEY ''
	 SET @sDSQL = ''ALTER TABLE [dbo].['' + @idxTableName + ''] DROP CONSTRAINT ['' + @idxname + '']''
	 -- Check if the index is clustered
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 0)
	 BEGIN
	 SET @sSQL = @sSQL + ''NON''
	 SET @IsClustered = 0
	 END
	 ELSE
	 BEGIN
	 SET @IsClustered = 1
	 END
	 SET @sSQL = @sSQL + ''CLUSTERED (''
	END
	ELSE
	BEGIN
	 SET @sSQL = ''CREATE ''
	 -- Check if the index is unique
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsUnique'') = 1)
	 BEGIN
	 SET @sSQL = @sSQL + ''UNIQUE ''
	 END
	 -- Check if the index is clustered
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 1)
	 BEGIN
	 SET @sSQL = @sSQL + ''CLUSTERED ''
	 SET @IsClustered = 1
	 END
	 ELSE
	 BEGIN
	 SET @IsClustered = 0
	 END

	 SELECT @sDSQL = ''DROP INDEX ['' + @idxname + ''] ON [dbo].['' + @idxTableName + '']''
	 SELECT
	 @sSQL = @sSQL + ''INDEX ['' + @idxname + ''] ON [dbo].['' + @idxTableName + ''] ('',
	 @colCount = 0
	END

	-- Get the number of cols in the index
	SELECT @colCount = COUNT(*)
	FROM sys.index_columns ic
	INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
	WHERE ic.object_id = @idxtableid AND index_id = @idxid --AND ic.is_included_column = 0

	SELECT @colCountWithoutIncludedColumns = COUNT(*)
	FROM sys.index_columns ic
	INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
	WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

	-- Get the file group info
	SELECT @location = f.[name]
	FROM sys.indexes i
	INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
	INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
	WHERE o.object_id = @idxTableID AND i.index_id = @idxid

	-- Get all columns of the index
	INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
	SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
	FROM sys.index_columns ic
	INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
	WHERE ic.object_id = @idxTableID AND index_id = @idxid
	ORDER BY ic.index_column_id

	IF @@ROWCOUNT > 0
	BEGIN

	SELECT @CurrentCol = 1

	SELECT @IxFirstColumn = 1, @sIncludeCols = '''', @sIndexCols = ''''

	WHILE @CurrentCol <= @ColCount
	BEGIN
	SELECT @ColumnIDInTable = ColumnIDInTable,
	@Name = Name,
	@ColumnIDInIndex = ColumnIDInIndex,
	@IsIncludedColumn = IsIncludedColumn
	FROM #ColumnListing
	WHERE [ColumnListingID] = @CurrentCol

	IF @IsIncludedColumn = 0
	BEGIN

	SET @sIndexCols = @sIndexCols + ''['' + @Name + ''] ''

	-- Check the sort order of the index cols ????????
	IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,''IsDescending'')) = 0
	BEGIN
	SET @sIndexCols = @sIndexCols + '' ASC ''
	END
	ELSE
	BEGIN
	SET @sIndexCols = @sIndexCols + '' DESC ''
	END

	IF @CurrentCol < @colCountWithoutIncludedColumns
	BEGIN
	SET @sIndexCols = @sIndexCols + '', ''
	END

	END
	ELSE
	BEGIN
	-- Check for any include columns
	IF LEN(@sIncludeCols) > 0
	BEGIN
	SET @sIncludeCols = @sIncludeCols + '',''
	END

	SET @sIncludeCols = @sIncludeCols + ''['' + @Name + '']''

	END

	SET @CurrentCol = @CurrentCol + 1
	END

	TRUNCATE TABLE #ColumnListing

	--append to the result
	IF LEN(@sIncludeCols) > 0
	 SET @sIndexCols = @sSQL + @sIndexCols + '') '' + '' INCLUDE ( '' + @sIncludeCols + '' ) ''
	ELSE
	 SET @sIndexCols = @sSQL + @sIndexCols + '') ''

	-- Add filtering
	IF @FilterDefinition IS NOT NULL
	 SET @sFilterSQL = '' WHERE '' + @FilterDefinition + '' ''
	ELSE
	 SET @sFilterSQL = ''''

	-- Build the options
	SET @sParamSQL = ''WITH ( PAD_INDEX = ''

	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPadIndex'') = 1
	 SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	 SET @sParamSQL = @sParamSQL + ''OFF,''

	SET @sParamSQL = @sParamSQL + '' ALLOW_PAGE_LOCKS = ''


	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPageLockDisallowed'') = 0
	 SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	 SET @sParamSQL = @sParamSQL + ''OFF,''

	SET @sParamSQL = @sParamSQL + '' ALLOW_ROW_LOCKS = ''

	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsRowLockDisallowed'') = 0
	SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	SET @sParamSQL = @sParamSQL + ''OFF,''


	SET @sParamSQL = @sParamSQL + '' STATISTICS_NORECOMPUTE = ''

	-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
	IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsStatistics'') = 1)
	SET @sParamSQL = @sParamSQL + ''ON''
	ELSE
	SET @sParamSQL = @sParamSQL + ''OFF''

	-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
	IF ISNULL( @FillFactor, 90 ) <> 0 
	 SET @sParamSQL = @sParamSQL + '' ,FILLFACTOR = '' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) -- + '')''

	/*
	IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn''t valid for PK''s
	BEGIN
	 --SET @sParamSQL = @sParamSQL + '' ) ''
	END
	ELSE
	BEGIN
	 SET @sParamSQL = @sParamSQL + '' ,DROP_EXISTING = ON ) ''
	END
	*/

	SET @sSQL = ''IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + @idxTableName + CHAR(39) + '')) ''  + @sIndexCols + @sFilterSQL + @sParamSQL

	-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement
	SET @sSQL = @sSQL + '') ON ['' + @location + '']''

	--PRINT @sIndexCols + CHAR(13)
	INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sDSQL)

	END

	SET @CurrentIndex = @CurrentIndex + 1
	END

	SELECT IndexCreateSQL FROM #IndexSQL

	DROP TABLE #IndexSQL'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	DECLARE @sqltemp NVARCHAR(MAX)
	SET @sqltemp = REPLACE(@sql, 'SELECT IndexCreateSQL FROM #IndexSQL', 'SELECT IndexDropSQL FROM #IndexSQL')

	-- Drop default constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''
	FROM		sys.default_constraints AS DefCons
	INNER JOIN	sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id
	INNER JOIN	sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	SET @sql = 'USE [' + @DBName + '];
	-- Script out indexes completely, including both PK''s and regular indexes, each clustered or nonclustered.
	-- DOES NOT HANDLE COMPRESSION; that''s ok, since 2008 R2 RTM benchmarking shows it''s faster and results in smaller indexes to insert uncompressed and then compress later
	-- HARDCODES [dbo] schema (i.e. it doesn''t say [JohnDoe].[table], changing that to [dbo].[table]
	-- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

	DECLARE
	@idxTableName SYSNAME,
	@idxTableID INT,
	@idxname SYSNAME,
	@idxid INT,
	@colCount INT,
	@IxColumn SYSNAME,
	@IxFirstColumn BIT,
	@ColumnIDInTable INT,
	@ColumnIDInIndex INT,
	@IsIncludedColumn INT,
	@sIncludeCols NVARCHAR(MAX),
	@sIndexCols NVARCHAR(MAX),
	@sSQL NVARCHAR(MAX),
	@sDSQL NVARCHAR(MAX),
	@sParamSQL NVARCHAR(MAX),
	@sFilterSQL NVARCHAR(MAX),
	@location SYSNAME,
	@IndexCount INT,
	@CurrentIndex INT,
	@CurrentCol INT,
	@Name VARCHAR(128),
	@IsPrimaryKey TINYINT,
	@Fillfactor INT,
	@FilterDefinition NVARCHAR(MAX),
	@IsClustered BIT -- used solely for putting information into the result table


	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexSQL]''))
	DROP TABLE [dbo].[#IndexSQL]

	CREATE TABLE #IndexSQL
	( TableName NVARCHAR(128) NOT NULL
	 ,IndexName NVARCHAR(128) NOT NULL
	 ,IsClustered BIT NOT NULL
	 ,IsPrimaryKey BIT NOT NULL
	 ,IndexCreateSQL NVARCHAR(max) NOT NULL
	 ,IndexDropSQL NVARCHAR(MAX) NOT NULL
	)

	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#IndexListing]''))
	DROP TABLE [dbo].[#IndexListing]

	CREATE TABLE #IndexListing
	(
	[IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ObjectID] INT NOT NULL,
	[IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IndexID] INT NOT NULL,
	[IsPrimaryKey] TINYINT NOT NULL,
	[FillFactor] INT,
	[FilterDefinition] NVARCHAR(MAX) NULL
	)

	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N''[tempdb].[dbo].[#ColumnListing]''))
	DROP TABLE [dbo].[#ColumnListing]

	CREATE TABLE #ColumnListing
	(
	[ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	[ColumnIDInTable] INT NOT NULL,
	[Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ColumnIDInIndex] INT NOT NULL,
	[IsIncludedColumn] BIT NULL
	)

	INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
	SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.is_primary_key, si.fill_factor, si.filter_definition
	FROM sys.indexes si
	LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON si.name = tc.CONSTRAINT_NAME AND OBJECT_NAME(si.object_id) = tc.TABLE_NAME
	WHERE OBJECTPROPERTY(si.object_id, ''IsUserTable'') = 1 AND OBJECT_NAME(si.object_id) <> ''sysdiagrams''
	ORDER BY OBJECT_NAME(si.object_id), si.index_id

	SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

	WHILE @CurrentIndex <= @IndexCount
	BEGIN

	SELECT @idxTableName = [TableName],
	@idxTableID = [ObjectID],
	@idxname = [IndexName],
	@idxid = [IndexID],
	@IsPrimaryKey = [IsPrimaryKey],
	@FillFactor = [FILLFACTOR],
	@FilterDefinition = [FilterDefinition]
	FROM #IndexListing
	WHERE [IndexListingID] = @CurrentIndex

	-- So - it is either an index or a constraint
	-- Check if the index is unique
	IF (@IsPrimaryKey = 1)
	BEGIN
	 SET @sSQL = ''ALTER TABLE [dbo].['' + @idxTableName + ''] ADD CONSTRAINT ['' + @idxname + ''] PRIMARY KEY ''
	 SET @sDSQL = ''IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + @idxTableName + CHAR(39) + '')) ALTER TABLE [dbo].['' + @idxTableName + ''] DROP CONSTRAINT ['' + @idxname + '']''
	 -- Check if the index is clustered
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 0)
	 BEGIN
	 SET @sSQL = @sSQL + ''NON''
	 SET @IsClustered = 0
	 END
	 ELSE
	 BEGIN
	 SET @IsClustered = 1
	 END
	 SET @sSQL = @sSQL + ''CLUSTERED (''
	END
	ELSE
	BEGIN
	 SET @sSQL = ''CREATE ''
	 -- Check if the index is unique
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsUnique'') = 1)
	 BEGIN
	 SET @sSQL = @sSQL + ''UNIQUE ''
	 END
	 -- Check if the index is clustered
	 IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsClustered'') = 1)
	 BEGIN
	 SET @sSQL = @sSQL + ''CLUSTERED ''
	 SET @IsClustered = 1
	 END
	 ELSE
	 BEGIN
	 SET @IsClustered = 0
	 END

	 SELECT @sDSQL = ''IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = '' + CHAR(39) + @idxname + CHAR(39) + '' AND object_id = OBJECT_ID('' + CHAR(39) + @idxTableName + CHAR(39) + '')) DROP INDEX ['' + @idxname + ''] ON [dbo].['' + @idxTableName + '']''
	 SELECT
	 @sSQL = @sSQL + ''INDEX ['' + @idxname + ''] ON [dbo].['' + @idxTableName + ''] ('',
	 @colCount = 0
	END

	-- Get the number of cols in the index
	SELECT @colCount = COUNT(*)
	FROM sys.index_columns ic
	INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
	WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

	-- Get the file group info
	SELECT @location = f.[name]
	FROM sys.indexes i
	INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
	INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
	WHERE o.object_id = @idxTableID AND i.index_id = @idxid

	-- Get all columns of the index
	INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
	SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
	FROM sys.index_columns ic
	INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
	WHERE ic.object_id = @idxTableID AND index_id = @idxid
	ORDER BY ic.index_column_id

	IF @@ROWCOUNT > 0
	BEGIN

	SELECT @CurrentCol = 1

	SELECT @IxFirstColumn = 1, @sIncludeCols = '''', @sIndexCols = ''''

	WHILE @CurrentCol <= @ColCount
	BEGIN
	SELECT @ColumnIDInTable = ColumnIDInTable,
	@Name = Name,
	@ColumnIDInIndex = ColumnIDInIndex,
	@IsIncludedColumn = IsIncludedColumn
	FROM #ColumnListing
	WHERE [ColumnListingID] = @CurrentCol

	IF @IsIncludedColumn = 0
	BEGIN

	SET @sIndexCols = @sIndexCols + ''['' + @Name + ''] ''

	-- Check the sort order of the index cols ????????
	IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,''IsDescending'')) = 0
	BEGIN
	SET @sIndexCols = @sIndexCols + '' ASC ''
	END
	ELSE
	BEGIN
	SET @sIndexCols = @sIndexCols + '' DESC ''
	END

	IF @CurrentCol < @colCount
	BEGIN
	SET @sIndexCols = @sIndexCols + '', ''
	END

	END
	ELSE
	BEGIN
	-- Check for any include columns
	IF LEN(@sIncludeCols) > 0
	BEGIN
	SET @sIncludeCols = @sIncludeCols + '',''
	END

	SET @sIncludeCols = @sIncludeCols + ''['' + @IxColumn + '']''

	END

	SET @CurrentCol = @CurrentCol + 1
	END

	TRUNCATE TABLE #ColumnListing

	--append to the result
	IF LEN(@sIncludeCols) > 0
	 SET @sIndexCols = @sSQL + @sIndexCols + '') '' + '' INCLUDE ( '' + @sIncludeCols + '' ) ''
	ELSE
	 SET @sIndexCols = @sSQL + @sIndexCols + '') ''

	-- Add filtering
	IF @FilterDefinition IS NOT NULL
	 SET @sFilterSQL = '' WHERE '' + @FilterDefinition + '' ''
	ELSE
	 SET @sFilterSQL = ''''

	-- Build the options
	SET @sParamSQL = ''WITH ( PAD_INDEX = ''

	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPadIndex'') = 1
	 SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	 SET @sParamSQL = @sParamSQL + ''OFF,''

	SET @sParamSQL = @sParamSQL + '' ALLOW_PAGE_LOCKS = ''


	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsPageLockDisallowed'') = 0
	 SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	 SET @sParamSQL = @sParamSQL + ''OFF,''

	SET @sParamSQL = @sParamSQL + '' ALLOW_ROW_LOCKS = ''

	IF INDEXPROPERTY(@idxTableID, @idxname, ''IsRowLockDisallowed'') = 0
	SET @sParamSQL = @sParamSQL + ''ON,''
	ELSE
	SET @sParamSQL = @sParamSQL + ''OFF,''


	SET @sParamSQL = @sParamSQL + '' STATISTICS_NORECOMPUTE = ''

	-- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
	IF (INDEXPROPERTY(@idxTableID, @idxname, ''IsStatistics'') = 1)
	SET @sParamSQL = @sParamSQL + ''ON''
	ELSE
	SET @sParamSQL = @sParamSQL + ''OFF''

	-- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
	IF ISNULL( @FillFactor, 90 ) <> 0 
	 SET @sParamSQL = @sParamSQL + '' ,FILLFACTOR = '' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) -- + '')''

	/*
	IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn''t valid for PK''s
	BEGIN
	 --SET @sParamSQL = @sParamSQL + '' ) ''
	END
	ELSE
	BEGIN
	 SET @sParamSQL = @sParamSQL + '' ,DROP_EXISTING = ON ) ''
	END
	*/

	SET @sSQL = @sIndexCols + @sFilterSQL + @sParamSQL

	-- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement
	SET @sSQL = @sSQL + '' ON ['' + @location + '']''

	--PRINT @sIndexCols + CHAR(13)
	INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL, IndexDropSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL, @sDSQL)

	END

	SET @CurrentIndex = @CurrentIndex + 1
	END

	SELECT IndexDropSQL FROM #IndexSQL

	DROP TABLE #IndexSQL'

	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Drop check constraints
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''IF EXISTS (SELECT OBJECT_ID('' + QUOTENAME(DefCons.name, CHAR(39)) + '', '' + CHAR(39) + ''C'' + CHAR(39) + '') FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS) ALTER TABLE ['' + schema_name(Tab.schema_id) + ''].['' + OBJECT_NAME(DefCons.parent_object_id) +''] DROP CONSTRAINT [''+DefCons.name+'']''
	FROM		sys.default_constraints AS DefCons
	INNER JOIN	sys.columns AS Col ON DefCons.parent_object_id = Col.object_id AND DefCons.parent_column_id = Col.column_id
	INNER JOIN	sys.tables Tab ON Tab.object_id=Col.OBJECT_ID'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Recreate UDF's
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT m.definition
	FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''FN'', ''IF'', ''TF'')'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Add computed columns
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE ['' + OBJECT_NAME(c.object_id) + ''] ADD ['' + c.name + ''] AS '' + c.definition
	FROM		sys.computed_columns c'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Drop computed columns
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER TABLE ['' + OBJECT_NAME(c.object_id) + ''] DROP COLUMN ['' + c.name + '']''
	FROM		sys.computed_columns c'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Script collation change at column level for every (N)CHAR, (N)VARCHAR and (N)TEXT columns
	SET @sql = 'USE [' + @DBName + ']; 
	DECLARE @TableName NVARCHAR(255),
			@CollationName NVARCHAR(255),
			@ColumnName NVARCHAR(255),
			@DataType NVARCHAR(255),
			@CharacterMaxLen NVARCHAR(255),
			@IsNullable NVARCHAR(255),
			@SQLText NVARCHAR(4000)

	DECLARE @Results TABLE (SQLStatement NVARCHAR(4000))

	DECLARE MyTableCursor Cursor FOR
	SELECT name FROM sys.tables WHERE [type] = ''U'' and name <> ''sysdiagrams'' ORDER BY name 

	OPEN MyTableCursor
	SET @CollationName = ''' + @NewCollation + '''

	FETCH NEXT FROM MyTableCursor INTO @TableName
	WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE MyColumnCursor Cursor
			FOR 
			SELECT c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS c
				WHERE c.TABLE_NAME = @TableName AND (c.DATA_TYPE LIKE ''%char%'' OR c.DATA_TYPE LIKE ''%text%'') AND c.COLLATION_NAME <> @CollationName
				AND NOT EXISTS (SELECT co.column_id FROM sys.computed_columns co WHERE co.object_id = OBJECT_ID(@TableName) AND co.name = c.COLUMN_NAME)
				ORDER BY c.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 @DataType <> ''text'' THEN ''('' + CASE WHEN @CharacterMaxLen = -1 THEN ''MAX'' ELSE @CharacterMaxLen END + '')'' ELSE '''' END + '' COLLATE '' + @CollationName + '' '' + CASE WHEN @IsNullable = ''NO'' THEN ''NOT NULL'' ELSE ''NULL'' END
				INSERT INTO @Results SELECT @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
	
	SELECT * FROM @Results'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Drop UDF's
  	SET @sql = 'USE [' + @DBName + ']; 
	SELECT ''IF EXISTS (SELECT o.name FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''''FN'''', ''''IF'''', ''''TF'''') AND o.name = '' + QUOTENAME(o.NAME, CHAR(39)) + '') DROP FUNCTION ['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.NAME + '']''
	FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''FN'', ''IF'', ''TF'')'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Drop views
  	SET @sql = 'USE [' + @DBName + ']; 
	SELECT ''IF EXISTS (SELECT o.name FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''''V'''') AND o.name = '' + QUOTENAME(o.NAME, CHAR(39)) + '') DROP VIEW ['' + SCHEMA_NAME(o.schema_id) + ''].['' + o.NAME + '']''
	FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''V'')'
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Change collation at database level
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT		''ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; 
ALTER DATABASE [' + @DBName + '] COLLATE ' + @NewCollation + '; 
ALTER DATABASE [' + @DBName + '] SET MULTI_USER'''
	INSERT INTO @DropScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Recreate views
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT m.definition
	FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id] WHERE o.type IN (''V'')'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	-- Recreate permissions
	SET @sql = 'USE [' + @DBName + ']; 
	SELECT ''GRANT '' + dp.permission_name collate ' + @NewCollation + '
    + '' ON '' + s.name + ''.'' + o.name + '' TO ['' + dpr.name + '']''
    FROM sys.database_permissions AS dp
    INNER JOIN sys.objects AS o ON dp.major_id=o.object_id
    INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
    INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id
    WHERE dpr.name NOT IN (''guest'')'
	INSERT INTO @CreateScripts (SQLStatement)
	EXEC sp_executesql @sql

	IF @JustPrintStatements = 1
	BEGIN
		-- Just return recordset of statements that need to be executed to change collation, drop statements first followed by creation statements
		SELECT SQLStatement FROM @DropScripts GROUP BY SQLStatement ORDER BY MIN(ID)
		SELECT SQLStatement FROM @CreateScripts GROUP BY SQLStatement ORDER BY MIN(ID)
	END
	ELSE
	BEGIN
		-- Execute SQL statements in dependency order
		DECLARE SQLStatements CURSOR FOR
		SELECT SQLStatement FROM @DropScripts GROUP BY SQLStatement ORDER BY MIN(ID)
		OPEN SQLStatements
		FETCH NEXT FROM SQLStatements INTO @sql
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @bigsql = 'USE [' + @dbname + ']; EXEC (' + CHAR(39) + REPLACE(@sql, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39) + ')'
			IF @DebugPrintSQL = 1 PRINT @bigsql
			EXEC sp_executesql @bigsql
			FETCH NEXT FROM SQLStatements INTO @sql
		END
		CLOSE SQLStatements
		DEALLOCATE SQLStatements

		DECLARE SQLStatements CURSOR FOR
		SELECT SQLStatement FROM @CreateScripts GROUP BY SQLStatement ORDER BY MIN(ID)
		OPEN SQLStatements
		FETCH NEXT FROM SQLStatements INTO @sql
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @bigsql = 'USE [' + @dbname + ']; EXEC (' + CHAR(39) + REPLACE(@sql, CHAR(39), CHAR(39) + CHAR(39)) + CHAR(39) + ')'
			IF @DebugPrintSQL = 1 PRINT @bigsql
			EXEC (@bigsql)
			FETCH NEXT FROM SQLStatements INTO @sql
		END
		CLOSE SQLStatements
		DEALLOCATE SQLStatements
	END

	-- Tidy up - remove stored procedures created
	SET @sql = REPLACE('IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptDropTableKeys'') DROP PROCEDURE ScriptDropTableKeys', CHAR(39), CHAR(39)+CHAR(39))
	SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + @sql + '''';
	EXEC (@bigsql)
	SET @sql = 'IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''ScriptCreateTableKeys'') DROP PROCEDURE ScriptCreateTableKeys'
	SET @bigsql = 'USE [' + @DBName + ']; EXEC sp_executesql N''' + REPLACE(@sql, CHAR(39), CHAR(39)+CHAR(39)) + ''''
	EXEC (@bigsql)
END

Rate

4.75 (4)

Share

Share

Rate

4.75 (4)