-- =============================================
-- AJITH DHARWAR akdharwar@yahoo.com 4/1/2005
-- ---------------------------------------------
-- NOTE THE EXECUTE IS COMMENTED----------------
-- EXECUTE THIS SCRIPT AS IS SEE IF THE ALTERS LOOK SATISFACTORY----------------
-- Script to reset COLLATE to database default
-- =============================================
declare @srvrcollation nvarchar(255) 
select  @srvrcollation = convert(nvarchar(255), serverproperty( N'COLLATION'))
--PRINT @srvrcollation

declare @dbcollation nvarchar(255) 
declare @dbname nvarchar(255)
set @dbname=db_name()
select  @dbcollation = convert(nvarchar(255), databasepropertyex(@dbname, N'COLLATION'))
--PRINT @dbcollation
IF @srvrcollation <> @dbcollation
BEGIN	
--	EXEC('ALTER DATABASE ' + @dbname + ' COLLATE '+ @srvrcollation)
--	UNCOMMENT the above if statements look satisfactory
	PRINT('ALTERING DATABASE ' + @dbname + ' TO COLLATION '+ @srvrcollation + ' FROM ' + @dbcollation)
END
--ALTER DATABASE ErrorLog COLLATE SQL_Latin1_General_CP437_BIN

DECLARE COL_CURSOR CURSOR READ_ONLY FOR 
select table_schema, table_name, column_name, 
column_default, is_Nullable, Data_type, 
character_maximum_length, collation_name
from information_schema.columns 
INNER JOIN 
(SELECT TABLE_NAME TN FROM 
information_schema.tables where TABLE_TYPE='BASE TABLE' ) IT ON (TABLE_NAME=TN)
where (Data_type LIKE  '%char%' OR Data_type LIKE '%text%')
--AND collation_name IS NOT NULL
AND collation_name <> @srvrcollation

DECLARE @table_schema varchar(10), @table_name varchar(100), @column_name varchar(100), 
@column_default varchar(100), @is_Nullable varchar(5), @Data_type varchar(100), 
@character_maximum_length varchar(10), @columncollation varchar(200)

DECLARE @Execstr VARCHAR(2000)
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name, 
@column_default, @is_Nullable, @Data_type, 
@character_maximum_length, @columncollation
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SET @Execstr =	'ALTER TABLE ' + @table_schema + '.' + @table_name
				+ ' ALTER COLUMN [' + @column_name  + '] ' + @Data_type + ' ('+  @character_maximum_length + ') '
				+ CASE WHEN @is_Nullable='no'  THEN  ' NOT NULL' ELSE ' NULL ' END				
		
		--EXEC (@Execstr)
		--UNCOMMENT the above if statements look satisfactory

		PRINT ('Executing -->' + @Execstr )
		PRINT ('--Orig COLLATION WAS -->' + @columncollation )

	END
	FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name, 
	@column_default, @is_Nullable, @Data_type, 
	@character_maximum_length, @columncollation
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
GO