-------------------------------------------------------

Collation conflict script

-------------------------------------------------------

-- declare local variable to be changed to your database name and new collation:
declare @p_DatabaseName	sysname = 'MyDatabase',
	@p_NewCollationName	varchar(200) = 'French_CI_AS'

-- other local variables:
declare @all  tinyint,
	@cmd nvarchar(4000)
select  @all = 1
declare @textptr binary(16)
declare @SQLSegment nvarchar(4000)
declare @C Cursor
set nocount on

-- create temporary table to hold the commands to be executed:
create table #SQL (ID int primary key identity(1,1),SQL ntext)

insert into #SQL (SQL)
select 	'Alter table ['+ object_Name(id) + '] DROP CONSTRAINT [' + object_name(constid) + ']' 
from 	sysconstraints c
where 	objectproperty(constid,'IsForeignKey')=1 
and 	constid in  (

	select 	fk.constid
	from 	sysforeignkeys fk
	join 	syscolumns fc
	on	fc.colid = fk.fkey
	and	fc.id = fk.fkeyid
	join 	syscolumns rc
	on	rc.colid = fk.rkey
	and	rc.id = fk.rkeyid
	where	fc.collationid is not null
	or 	rc.collationid is not null
	or	@all=1 )  --parameter allows all constraints to be dropped

/*script out dropping of check constraints */

	

insert into #SQL (SQL)
select 	'Alter table [' + Object_Name(cs.id) + '] drop constraint ['+object_name(cs.constid)+']'
from 	sysconstraints cs
where 	objectproperty(cs.constid,'IsCheckCnst') = 1


--drop Table Functions

insert into #SQL (SQL)
select 'DROP FUNCTION ['+ name+ ']'
from 	sysobjects 
where 	objectproperty(id,'IsMSShipped')=0 
and 	objectproperty(id,'IsTableFunction')=1 

-- script drop of indexes - we will also populate a temp table that helps recreate the indexes later
create table #spindtab 
(
	objectname			sysname collate database_default NOT NULL,
	index_name			sysname	collate database_default NOT NULL,
	stats				int,
	groupname			sysname collate database_default NOT NULL,
	index_keys			nvarchar(3000)	collate database_default NOT NULL, -- see @IX_keys above for length descr
	OrigFillFactor			tinyint
)


	--generate SQL to do indexes

	declare 	@IX_indid smallint,	-- the index id of an index
			@IX_groupid smallint,  -- the filegroup id of an index
			@IX_indname sysname,
			@IX_groupname sysname,
			@IX_status int,
			@IX_keys nvarchar(3000),	
			@IX_dbname	sysname,
			@IX_ObjID int,
			@IX_ObjName sysname,
			@IX_OrigFillFactor tinyint

	-- Check to see the the table exists and initialize @IX_objid.

	-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
	declare ms_crs_ind cursor local static for
		select id, object_name(id), indid, groupid, name, status, OrigFillFactor from sysindexes
			where /*id = @IX_objid and */indid > 0 and indid < 255 and (status & 64)=0 
			and  objectproperty(id,'ISMSSHIPPED')=0
			order by object_name(id),indid


	open ms_crs_ind
	fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor


	-- Now check out each index, figure out its type and keys and
	--	save the info in a temporary table that we'll print out at the end.
	while @@fetch_status >= 0
	begin
		-- First we'll figure out what the keys are.
		declare @IX_i int, @IX_thiskey nvarchar(133) -- 128+5
		declare @rebuild_index bit

		select @IX_keys = '[' + index_col(@IX_objname, @IX_indid, 1)+']', @IX_i = 2, @rebuild_index=@all  --parameter from application can force all to be rebuilt
		if (indexkey_property(@IX_objid, @IX_indid, 1, 'IsDescending') = 1)
			set @IX_keys = @IX_keys  + ' DESC'

		if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, 1, 'columnid')) is not null
			set @rebuild_index=1


		set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
		if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
			set @IX_thiskey = @IX_thiskey + ' DESC'

		if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
			set @rebuild_index=1

		while (@IX_thiskey is not null )
		begin
			select @IX_keys = @IX_keys + ', ' + @IX_thiskey, @IX_i = @IX_i + 1


			if (select collationid from syscolumns where id=@IX_objid and colid=indexkey_property(@IX_objid, @IX_indid, @IX_i, 'columnid')) is not null
				set @rebuild_index=1

			set @IX_thiskey = '[' + index_col(@IX_objname, @IX_indid, @IX_i) + ']'
			if ((@IX_thiskey is not null) and (indexkey_property(@IX_objid, @IX_indid, @IX_i, 'IsDescending') = 1))
				select @IX_thiskey = @IX_thiskey + ' DESC'
		end

		select @IX_groupname = groupname from sysfilegroups where groupid = @IX_groupid

		-- INSERT ROW FOR INDEX
		if @rebuild_index =1 
			insert into #spindtab values (@IX_ObjName,@IX_indname, @IX_status, @IX_groupname, @IX_keys, @IX_OrigFillFactor)

		-- Next index
		fetch ms_crs_ind into @IX_objid, @IX_ObjName,@IX_indid, @IX_groupid, @IX_indname, @IX_status, @IX_OrigFillFactor
	end
	deallocate ms_crs_ind 

	-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
	declare @IX_empty varchar(1) select @IX_empty = ''
	declare @IX_des1			varchar(35),	-- 35 matches spt_values
			@IX_des2		varchar(35),
			@IX_des4		varchar(35),
			@IX_des32		varchar(35),
			@IX_des64		varchar(35),
			@IX_des2048		varchar(35),
			@IX_des4096		varchar(35),
			@IX_des8388608		varchar(35),
			@IX_des16777216	varchar(35)

	select @IX_des1 = name from master.dbo.spt_values where type = 'I' and number = 1 --ignoor duplicate keys

	select @IX_des2 = name from master.dbo.spt_values where type = 'I' and number = 2 --unique
	select @IX_des4 = name from master.dbo.spt_values where type = 'I' and number = 4 --ignoor duplicate rows
	select @IX_des32 = name from master.dbo.spt_values where type = 'I' and number = 32 --hypothetical
	select @IX_des64 = name from master.dbo.spt_values where type = 'I' and number = 64 --statistics
	select @IX_des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048  --primary key
	select @IX_des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096  --unique key
	select @IX_des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608  --auto create
	select @IX_des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216 --stats no recompute

insert into #SQL
select case when (stats & 4096)<>0 or (stats & 2048) <> 0 then
	--Constraint		
	'ALTER TABLE ['+objectname+'] DROP CONSTRAINT ['+index_name+'] ' 

	else
	-- index

	'DROP INDEX ['+objectname+'].['+ index_name +'] '
	end
from 	#spindtab 

/*drop calculated columns*/

insert into #SQL (SQL)
select 'ALTER TABLE ['+ object_name(id)+ '] drop column ['+name+']'
from 	syscolumns 
where 	iscomputed=1 
and 	objectproperty(id,'IsMSShipped')=0 
and 	objectproperty(id,'IsTable')=1

-- Set database collation to the new one:

insert into #SQL (SQL) 
SELECT 'Alter database ' + @p_DatabaseName + ' COLLATE ' + @p_NewCollationName

--script out recreation of calculated columns
	
insert into #SQL
select 'ALTER TABLE ['+ object_name(c.id)+ '] ADD ['+name+'] AS '+sc.text 
from 	syscolumns c
join 	syscomments sc
on	c.id = sc.id
and	c.colid = sc.number
where 	c.iscomputed=1 
and 	objectproperty(c.id,'IsMSShipped')=0 
and 	objectproperty(c.id,'IsTable')=1 

-- script out the changing of column level collation

declare @CC_TableName sysname,
	@ColName sysname,
	@CC_Length nvarchar(100),
	@CC_TypeName sysname,
	@CC_OtherText nvarchar(4000),
	@CC_NullText nvarchar(100)

set 	@C = cursor for 

select 		o.name as tablename, 
		c.name as colname, 
		case when t.name like 'n%' then cast(c.length / 2 as nvarchar(100)) else cast(c.length as nvarchar(100)) end as Length, 
		t.name as typename,
		case when c.isnullable=1 then 'NULL' else 'NOT NULL' end as nullable
from 		sysobjects o
join 		syscolumns c
on		o.id = c.id
join 		systypes t
on		t.xtype = c.xtype
and		t.xusertype = c.xusertype
where 		o.type ='U'
and		objectproperty(o.id,'IsMSShipped')=0
and             c.iscomputed<>1
and 		c.collationid is not null
--and 		c.collation <> cast(DATABASEPROPERTYEX(DB_NAME(),'collation') as sysname)

open @C


fetch next from @C into @CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
while @@Fetch_Status = 0
begin
	if @CC_TypeName COLLATE DATABASE_DEFAULT in ('ntext','text')
	begin
		-- we can not use the alter table statment to change column level collation on text columns
		--we need to do each of these as a separate transaction dur to the risks of errors
		
		set @SQLSegment = '
-- add a temp column
exec (''Alter table ['+@CC_TableName+'] add [____temp] [' + @CC_TypeName + ']'')

-- copy data to temp column
	exec (''update ['+@CC_TableName+'] set [____temp] =[' + @ColName + ']'')
'
-- if default constraint must add it

if exists (	select 	* 
		from 	sysconstraints  
		where 	id = object_id(@CC_TableName) 
		and 	col_name(id,colid) = @ColName
		and	(status & 5) = 5 )

-- if there are default constraints add a bit to do that
set @SQLSegment = @SQLSegment + 

	(select '

	exec (''Alter table ['+@CC_TableName+'] drop constraint  [' + object_name(c.constid) + ']'')
	exec (''Alter table ['+@CC_TableName+'] drop column [' + @ColName + ']'')
	exec ('' ALTER TABLE ['+ object_name(c.id) + '] ADD ['+o.name+'] [' + @CC_TypeName + '] CONSTRAINT [' + object_name(c.constid) + '] DEFAULT ' + replace(t.text,'''','''''') + '  '')'
	from 	sysconstraints  c
	join 	syscolumns o
	on	c.id = o.id
	and	c.colid = o.colid
	join 	syscomments t
	on	t.id = c.constid
	where 	c.id = object_id(@CC_TableName) 
	and 	(c.status & 5) = 5
	and 	col_name(c.id,c.colid) = @ColName) --default constraint
	
else

	set @SQLSegment = @SQLSegment + '
	exec (''Alter table ['+@CC_TableName+'] drop column [' + @ColName + ']'')
	exec (''Alter table ['+@CC_TableName+'] add [' + @ColName+'] [' + @CC_TypeName + '] '')'

set @SQLSegment =  @SQLSegment  + '
	exec (''update ['+@CC_TableName+'] set [' + @ColName + '] = [____temp] '')
	exec (''alter table ['+@CC_TableName+'] drop column [____temp]'')
'
if @CC_TypeName = 'NOT NULL'

set @SQLSegment =  @SQLSegment  + '
	exec (''Alter table ['+@CC_TableName+'] Alter column [' + @ColName+'] [' + @CC_TypeName + '] '+@CC_NullText+' '')'

	insert into #SQL values (@SQLSegment)
		

	end
	else
	begin
		-- normal columns
		set @SQLSegment = 'Alter table ['+@CC_TableName COLLATE DATABASE_DEFAULT+'] Alter Column ['+@ColName COLLATE DATABASE_DEFAULT+ '] ['+@CC_TypeName COLLATE DATABASE_DEFAULT+']' 
	
		if @CC_TypeName COLLATE DATABASE_DEFAULT in ('nVarchar', 'varchar','char','nchar')
			set @SQLSegment = @SQLSegment COLLATE DATABASE_DEFAULT +' ('+@CC_Length COLLATE DATABASE_DEFAULT  + ')'
	
		set @SQLSegment = @SQLSegment	COLLATE DATABASE_DEFAULT +  /*' COLLATE DATABASE_DEFAULT ' +*/ @CC_NullText COLLATE DATABASE_DEFAULT + '
	' 
		insert into #SQL values (@SQLSegment)
		
	end	
	fetch next from @C into @CC_TableName, @ColName, @CC_Length, @CC_TypeName,@CC_NullText
end

close @C
deallocate @C

-- script out recreation of check constraints
	

insert into #SQL
select 		'Alter table [' + Object_Name(cs.id) + '] WITH NOCHECK ADD CONSTRAINT ['+object_name(cs.constid)+'] CHECK '+sc.text + '
' + case when objectproperty(cs.constid,'CnstIsDisabled') = 1 then 'Alter table [' + Object_Name(cs.id) + '] NOCHECK CONSTRAINT ['+object_name(cs.constid)+']' else '' end
from 		sysconstraints cs
join 		syscomments sc
on		sc.id = cs.constid
where 	objectproperty(cs.constid,'IsCheckCnst') = 1 

-- script out the recreation of indexes


	-- DISPLAY THE RESULTS
	
	
	insert into #SQL
	select case when (stats & 4096)<>0 or (stats & 2048) <> 0 then
		--Constraint		
		'ALTER TABLE ['+objectname+'] ADD CONSTRAINT ['+index_name+'] '
		+ case when (stats & 2048)<>0 then 'PRIMARY KEY ' else 'UNIQUE ' end
		+ case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
		+ ' ('+index_keys+')'
		+ case when OrigFillFactor >0 then ' WITH FILLFACTOR =' + cast(OrigFillFactor as nvarchar(3)) else @IX_empty end
		+ ' ON ['+groupname+']
' collate database_default


		else
		-- index

		'CREATE ' + case when (stats & 2)<>0 then @IX_des2 +' ' else @IX_empty end +case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end +' INDEX'
		+ ' ['+ index_name +'] on ['+objectname+'] ('+index_keys+')'
		+ case when OrigFillFactor >0 or (stats & 1) <> 0 or (stats & 16777216) <> 0 then ' WITH ' else @IX_empty end
		+ case when OrigFillFactor >0 then 'PAD_INDEX, FILLFACTOR = ' +cast(OrigFillFactor as nvarchar(3) ) else @IX_empty end
		+ case when (stats & 1) <> 0 then ', '+ @IX_des1  else @IX_empty end
		+ case when (stats & 16777216) <> 0 then ', '+ @IX_des16777216  else @IX_empty end
		+ ' ON ['+groupname+']
'
		end
	from 	#spindtab 

	drop table #spindtab

-- script recreation of foiegn keys

-- script out foreign keys
declare	@FK_KeyName sysname,
	@FK_TableName sysname,
	@FK_ReferencedTable sysname,
	@ConstID int,
	@Col1 sysname,
	@Col2 sysname,
	@ColList1 nvarchar(2000),
	@ColList2 nvarchar(2000),
	@CnstIsUpdateCascade bit,
	@CnstIsNotRepl bit,
	@CnstIsDeleteCascade bit,
	@CnstIsDisabled bit,
	@C2 cursor


	

set @C = cursor for 

select 	object_Name(id) as TableName,
	object_name(constid) as KeyName,
	(select distinct object_name(rkeyid) from sysforeignkeys fk where fk.constid = c.constid) as ReferencedTable,
	constid,
	objectproperty(constid,'CnstIsUpdateCascade') CnstIsUpdateCascade,
	objectproperty(constid,'CnstIsDeleteCascade') CnstIsDeleteCascade,
	objectproperty(constid,'CnstIsNotRepl') CnstIsNotRepl,
	objectproperty(constid,'CnstIsDisabled') CnstIsDisabled
from 	sysconstraints c
where 	objectproperty(constid,'IsForeignKey')=1 
and 	constid in  (

	select 	fk.constid
	from 	sysforeignkeys fk
	join 	syscolumns fc
	on	fc.colid = fk.fkey
	and	fc.id = fk.fkeyid
	join 	syscolumns rc
	on	rc.colid = fk.rkey
	and	rc.id = fk.rkeyid
	where	fc.collationid is not null
	or 	rc.collationid is not null 
	or	@all=1)  --paramater allows all constraints to be dropped
	
open @C 

fetch next from @C into @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@ConstID, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
while @@fetch_Status =0
begin
	set @ColList1 = ''
	set @ColList2 = ''

	set @C2 = Cursor for
	select  fc.name,
		rc.name

	from 	sysforeignkeys fk
	join 	syscolumns fc
	on	fc.colid = fk.fkey
	and	fc.id = fk.fkeyid
	join 	syscolumns rc
	on	rc.colid = fk.rkey
	and	rc.id = fk.rkeyid
	where 	fk.constid = @ConstID 

	open @C2
	fetch next from @C2 into @Col1, @Col2
	while @@Fetch_status=0
	begin
		if len(@ColList1) > 0 
			set @ColList1 = @ColList1 collate database_default+', '
		if len(@ColList2) > 0 
			set @ColList2 = @ColList2 collate database_default+', '

		set @ColList1 = @ColList1 collate database_default +'[' + @Col1 collate database_default + ']'
		set @ColList2 = @ColList2 collate database_default +'[' + @Col2 collate database_default + ']'

		fetch next from @C2 into @Col1, @Col2
	end
	close @C2
	deallocate @C2

	set @SQLSegment = 'Alter table ['+ @FK_TableName collate database_default + '] WITH NOCHECK ADD CONSTRAINT [' + @FK_KeyName collate database_default + '] FOREIGN KEY ('+@ColList1 collate database_default + ') REFERENCES [' + @FK_ReferencedTable collate database_default+'] ('+ @ColList2 collate database_default +')'
	if @CnstIsUpdateCascade =1
		set @SQLSegment =@SQLSegment + ' ON UPDATE CASCADE'

	if @CnstIsDeleteCascade =1
		set @SQLSegment =@SQLSegment + ' ON DELETE CASCADE'

	if @CnstIsNotRepl =1
		set @SQLSegment =@SQLSegment + ' NOT FOR REPLICATION'
	set @SQLSegment = @SQLSegment +'
'

	insert into #SQL values (@SQLSegment)

	if @CnstIsDisabled=1
	begin
		set @SQLSegment = 'Alter table ['+ @FK_TableName + '] NOCHECK CONSTRAINT [' + @FK_KeyName + ']
'
		insert into #SQL values (@SQLSegment)
	end

	fetch next from @C into @FK_TableName, @FK_KeyName, @FK_ReferencedTable,@ConstID, @CnstIsUpdateCascade, @CnstIsDeleteCascade, @CnstIsNotRepl,@CnstIsDisabled
end

close @C
deallocate @C 

-- Script out the creation of the table functions last!

--first switch to use the correct database
insert into #SQL (SQL) values ('')

--get a text pointer
SELECT @textptr = TEXTPTR(SQL) FROM #SQL where ID = (select max(ID) from #SQL)

set @SQLSegment = '
USE ' + @p_DatabaseName + '
' 

UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment


declare @FunctionName sysname,
	@LastFunctionName sysname

set 	@LastFunctionName =''

set @C = cursor for
select 		name as functionName, 
		sc.text
from 		sysobjects o
join 		syscomments sc
on		o.id = sc.id
where 		objectproperty(o.id,'IsTableFunction') =1
order by	o.name, 
		sc.colid  

open @C
fetch next from @C into @FunctionName, @SQLSegment
while @@Fetch_Status=0
begin
	if @FunctionName<>@LastFunctionName
	begin
		insert into #SQL (SQL) values ('')
		
		--get a text pointer
		SELECT @textptr = TEXTPTR(SQL) FROM #SQL where ID = (select max(ID) from #SQL)

		set @LastFunctionName =@FunctionName

	end
	
	UPDATETEXT #SQL.SQL @textptr NULL 0 @SQLSegment

	fetch next from @C into @FunctionName, @SQLSegment
end
Close @C
deallocate @C

--select SQL from #SQL order by ID

-- First alter database to single user mode:
exec ('alter database ' + @p_DatabaseName + ' set single_user')
if @@error <> 0 return -1

-- Execute all the commands:
-- I tried to execute this with the command:
-- EXEC master..sp_execresultset 'select SQL from #SQL order by ID' 
-- This resulted in errors, plus I can't handle the logical "rollback".
-- This is why I decided to use my own cursor.
-- This part still has to be improved to be able to run commands that are longer than 4000 characters:
declare curs_exec cursor for 
select convert(nvarchar(4000),SQL)
 from #SQL order by ID
open curs_exec
fetch curs_exec into @cmd
while @@FETCH_STATUS = 0
BEGIN
	exec (@cmd)
	if @@error <> 0 
	begin
		print 'ERROR!!!'
		exec ('alter database ' + @p_DatabaseName + ' set multi_user')
		print @cmd
		return -1
	end
	fetch curs_exec into @cmd
END
deallocate curs_exec

-- finally set back to multi user access
exec ('alter database ' + @p_DatabaseName + ' set multi_user')
return


-------------------------------------------------------