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

Stored procedure: sp_ChangeTempTableCollation

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


create procedure sp_ChangeTempTableCollation
@p_TableName	sysname,                               -- The name of the table
@p_DBName	sysname = 'tempdb',            -- The name of the database where 
                                                                         -- the table resides (for future use)
@p_TableOwner	sysname = NULL      -- the owner of the table (for future use)
as
declare 	@Collation  	sysname, 
		@cmd 		varchar(4000),
		@TableID	sysname,
		@FullPath	varchar(200)

-- Full path of the table. I.e: 'tempdb..'
select @FullPath = isnull(@p_DBName,'') + '.' + 
                   isnull(@p_TableOwner,'') + '.'

select 	@Collation = 
 	convert(varchar(100),
	DATABASEPROPERTYEX( db_name(),'collation' )),
	@TableID = object_id(@FullPath+@p_TableName)

-- Check if there is a need to alter the table (if not - exit the SP):
if not exists (select 1
		FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS
		WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
  		  and object_id(@FullPath+TABLE_NAME) = @TableID
  		  and COLLATION_NAME NOT LIKE @Collation)
	return

-- Create a temporary table to hold the commands to later be executed
-- The insertion order counts, this is why an identity column has been added:
create table #t___cmd (id int identity(1,1),txt varchar(4000))

-- Insert the alter commands for all the columns of type 'varchar' ,'char','nvarchar','nchar'
-- With different collation than the DB's:
insert into #t___cmd (txt)
select 	'alter table ' + @p_TableName + ' alter column ' + COLUMN_NAME + ' ' + DATA_TYPE + 
	'(' + convert(varchar(10),CHARACTER_MAXIMUM_LENGTH) + ')' +
	' COLLATE ' + @collation + ' ' +
	CASE IS_NULLABLE
             WHEN 'YES' THEN 'NULL'
             WHEN 'No' THEN 'NOT NULL'
	END
FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
  and object_id(@FullPath+TABLE_NAME) = @TableID
  and COLLATION_NAME NOT LIKE @Collation

-- TEXT and NTEXT columns cannot be altered. Hoping that this would not happen often, for each of
-- these columns:
-- a new temp column is created, updated with the old values, 
-- the other column is dropped and created with the new collation, 
-- the valus are copied and the other one is deleted.
-- The commands are inserted to another temp table, since all the commands can't be executed
-- at once because a column can't be used before it is physically created.
-- I.e: exec ('alter table <table name> add <col>; update <table name> set <col> = <col2>')
-- Will fail during compile time with the error message:
--          Server: Msg 207, Level 16, State 1, Line 1
--          Invalid column name '<col>'.
if exists (select 1 from TEMPDB.INFORMATION_SCHEMA.COLUMNS
		WHERE DATA_TYPE IN ('ntext','text')
  		  and object_id(@FullPath+TABLE_NAME) = @TableID
  		  and COLLATION_NAME NOT LIKE @Collation)
begin
	create table #t___2 (id int identity(1,1), txt varchar(4000))
	insert into #t___2 (txt)
	select    'insert into #t___cmd (txt) ' +
                 'values (''alter table ' + @p_TableName + ' add Temp___Col ' + DATA_TYPE + ''');' +
		'insert into #t___cmd (txt) ' +
                 'values (''update ' + @p_TableName + 
                          ' set ' + 'Temp___Col = ' + COLUMN_NAME + ''');' + 
		'insert into #t___cmd (txt) ' +
                  'values (''alter table ' + @p_TableName + ' drop column ' + 
                          COLUMN_NAME + ''');' +
		'insert into #t___cmd (txt) ' +
                 'values (''alter table ' + @p_TableName + ' add ' + COLUMN_NAME + ' ' + 
                          DATA_TYPE + ' COLLATE ' + @collation + ' ' +
	             CASE IS_NULLABLE
             		WHEN 'YES' THEN 'NULL'
             		WHEN 'No' THEN 'NOT NULL'
		END + ''');' +
		'insert into #t___cmd (txt) ' +
                   'values (''update ' + @p_TableName + ' set ' + COLUMN_NAME + 
                                 ' = Temp___Col ' + 	' COLLATE ' + @collation + ''');' +
		'insert into #t___cmd (txt) ' +
                   'values (''alter table ' + @p_TableName + ' drop column Temp___Col'')'
	FROM TEMPDB.INFORMATION_SCHEMA.COLUMNS
	WHERE DATA_TYPE IN ('ntext','text')
	  and object_id(@FullPath+TABLE_NAME) = @TableID
	  and COLLATION_NAME NOT LIKE @Collation

              -- Execute the commands in the temp table #t___2
              -- (Inserting the ommands into #t___cmd):
	exec master..sp_execresultset 'select txt from #t___2 order by id'
end
-- Execute the commands in the order of insertion:
declare curs_tmp cursor for
select txt from #t___cmd order by id
open curs_tmp
fetch curs_tmp into @cmd
while @@fetch_status = 0
begin
	exec (@cmd)
	fetch curs_tmp into @cmd
end
deallocate curs_tmp
GO

-------------------------------------------------------
 