Recollate All SQL Server Columns

DatabaseReCollatorMcJawn.sql

--desired collation for (var)char columns:
DECLARE @collationName VARCHAR(30)
SET @collationName = 'Latin1_General_CS_AI'

--build tables containing drop/create index queries
--http://www.sqlservercentral.com/scripts/Indexing/31652/
SELECT  
  REPLICATE(' ',4000) AS COLNAMES ,
  OBJECT_NAME(I.ID) AS TABLENAME,
  I.ID AS TABLEID,
  I.INDID AS INDEXID,
  I.NAME AS INDEXNAME,
  I.STATUS,
  INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE') AS ISUNIQUE,
  INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
  INDEXPROPERTY (I.ID,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
  INTO #TMP
  FROM sysindexes I
  WHERE I.INDID > 0 
  AND I.INDID < 255 
  AND (I.STATUS & 64)=0
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
  --AND   INDEXPROPERTY (I.ID,I.NAME,'ISUNIQUE')       =0
  AND   INDEXPROPERTY (I.ID,I.NAME,'ISCLUSTERED') =0
  AND (I.[NAME] LIKE 'UX%' OR I.[NAME] LIKE 'IX%')

DECLARE
  @ISQL VARCHAR(4000),
  @TABLEID INT,
  @INDEXID INT,
  @MAXTABLELENGTH INT,
  @MAXINDEXLENGTH INT,
  @DROP_INDEX_SQL NVARCHAR(4000),
  @CREATE_INDEX_SQL NVARCHAR(4000)

  --USED FOR FORMATTING ONLY
    SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
    SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

    DECLARE C1 CURSOR FOR
      SELECT TABLEID,INDEXID FROM #TMP  
    OPEN C1
      FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
        WHILE @@FETCH_STATUS <> -1
          BEGIN
	SET @ISQL = ''
	SELECT @ISQL=@ISQL + ISNULL(syscolumns.NAME,'') + ',' FROM sysindexes I
	INNER JOIN sysindexkeys ON I.ID=sysindexkeys.ID AND I.INDID=sysindexkeys.INDID
	INNER JOIN syscolumns ON sysindexkeys.ID=syscolumns.ID AND sysindexkeys.COLID=syscolumns.COLID
	WHERE I.INDID > 0 
	AND I.INDID < 255 
	AND (I.STATUS & 64)=0
	AND I.ID=@TABLEID AND I.INDID=@INDEXID
	ORDER BY syscolumns.COLID
	UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

	FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
         END
      CLOSE C1
      DEALLOCATE C1
  --AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
  UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

  SELECT  ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
	'CREATE ' 
    + CASE WHEN ISUNIQUE     = 1 THEN ' UNIQUE ' ELSE '        ' END 
    + CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE '           ' END 
    + ' INDEX [' + INDEXNAME + ']' 
    + SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
    +' ON [' + TABLENAME + '] '
    + SPACE(@MAXTABLELENGTH - LEN(TABLENAME)) 
    + '(' + COLNAMES + ')' 
    + CASE WHEN INDEXFILLFACTOR = 0 THEN ''  ELSE  ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR)   END AS SQL
  INTO #create_sql
  FROM #TMP

  SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID,
	'DROP INDEX [' + TABLENAME + '].[' + INDEXNAME + '];' AS SQL
  INTO #drop_sql
  FROM #TMP


--drop indexes

DECLARE @dropid INT, @dropmax INT, @dropsql NVARCHAR(4000)

SELECT @dropid = 1, @dropmax = MAX(ID) 
FROM #drop_sql

WHILE @dropid <= @dropmax BEGIN
	SELECT @dropsql = SQL FROM #drop_sql WHERE ID = @dropid;
	PRINT @dropsql;
	EXEC sp_executesql @dropsql;	
	SET @dropid = @dropid + 1;
END

--recollate

DECLARE @columns TABLE (Id INT IDENTITY(1,1), TableName NVARCHAR(1000), ColumnName NVARCHAR(1000), DataType NVARCHAR(1000), MaxSize INT, Nullable BIT)

INSERT @columns
SELECT * --TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CASE WHEN IS_NULLABLE = 'NO' THEN 0 ELSE 1 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LOWER(DATA_TYPE) LIKE '%CHAR%'
AND COLLATION_NAME <> @collationName
AND TABLE_NAME IN (
	SELECT TABLE_NAME
	FROM INFORMATION_SCHEMA.TABLES
	WHERE Table_Type = 'BASE TABLE'
)

DECLARE @id INT, @max INT

SELECT @id = 1, @max = MAX(Id) FROM @columns

DECLARE @Table NVARCHAR(1000), @Column NVARCHAR(1000), @DataType NVARCHAR(1000), @Size NVARCHAR(100), @TempColumn NVARCHAR(1000), @Nullable BIT

WHILE @id <= @max BEGIN

	SELECT @Table = TableName
		 , @Column = ColumnName
		 , @DataType = DataType
		 , @Size = CAST(MaxSize AS NVARCHAR(100))
         , @TempColumn = ColumnName + '_Temp'
         , @Nullable = Nullable
	FROM @columns
    WHERE Id = @id

	DECLARE @sql NVARCHAR(4000)

	SELECT @sql = 
		'ALTER TABLE ' + @Table + ' ALTER COLUMN ' + @Column + ' ' + @DataType + ' (' + 
		(CASE WHEN @Size = -1 THEN 'MAX' ELSE @Size END) + ') COLLATE ' + @collationName + ' ' + 
		(CASE WHEN @Nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END) + ';';
	

	PRINT @sql
	EXEC sp_executesql @sql
	SET @id = @id + 1
END

--create indexes
DECLARE @createid INT, @createmax INT, @createsql NVARCHAR(4000)

SELECT @createid = 1, @createmax = MAX(ID) 
FROM #create_sql

WHILE @createid <= @createmax BEGIN
	SELECT @createsql = SQL FROM #create_sql WHERE ID = @createid;
	PRINT @createsql;
	EXEC sp_executesql @createsql;	
	SET @createid = @createid + 1;
END

DROP TABLE #TMP
DROP TABLE #drop_sql
DROP TABLE #create_sql

This does not work properly for indexes with INCLUDE columns. I originally had an index like this:

CREATE TABLE [dbo].[Colors]([ID] [int] NULL,
[Color] [varchar]%2820%29 NULL) ON [PRIMARY]

GO

Create Index idx_Colors On Colors(id) Include(Color)

Your code dropped the index, but created it like this:

CREATE INDEX [idx_Colors] ON Colors

_POTENTIAL FIX_

here is a start:

select * from sys.index_columns where is_included_column = 1

I have 519 non-clustered indexes in my database. Only 36 of them have a string column, so only 36 need to be dropped and recreated. Your code drops them all and re-creates them.

_POTENTIAL FIX_
If index doesn't contain a string column, don't include it in drop/create statment temp tables

If you have a view with schema binding, you cannot change the collation of the column

Create View vw_Colors With SCHEMABINDING
AS
Select ID, Color From dbo.Colors

_POTENTIAL FIX_
should be able to drop/recreate schemabound views also. Can find schemabound views by using

Select object_definition(object_id), * From sys.views

or equivalent from INFORMATION_SCHEMA.VIEWS

Also...

Select *
From sys.views
where OBJECTPROPERTYEX(object_id, 'IsSchemaBound') = 1

Encrypted view definitions could be a problem though

It may be easier to first check for known limitations and then prevent the rest of the code from running. Then, as each addition limitation is accommodated, remove the check. For example:

Declare @problems Table(Problem VarChar(100))

If Exists(Select 1 From sys.views where OBJECTPROPERTYEX(object_id, 'IsSchemaBound') = 1)
Insert Into @problems(Problem)
Values ('Unable to process databases with schema bound views.')

If Exists( Select 1
From sys.views
where OBJECTPROPERTY(object_id, 'HasAfterTrigger') = 1
OR
OBJECTPROPERTY(object_id, 'HasInsertTrigger') = 1
OR
OBJECTPROPERTY(object_id, 'HasDeleteTrigger') = 1
OR
OBJECTPROPERTY(object_id, 'HasInsteadOfTrigger') = 1
OR
OBJECTPROPERTY(object_id, 'HasUpdateTrigger') = 1
)
Insert Into @problems(Problem)
Values ('Unable to process databases with triggers on views.')

If Exists(Select 1 From sys.views where object_definition(object_id) Is NULL)
Insert Into @problems(Problem)
Values ('Unable to process databases with encrypted views.')

If Exists(Select 1 From @problems)
Begin
Select Problem From @problems
Return
End