Skip to content

Instantly share code, notes, and snippets.

@AlexCuse
Created April 26, 2012 14:02
Show Gist options
  • Save AlexCuse/2499829 to your computer and use it in GitHub Desktop.
Save AlexCuse/2499829 to your computer and use it in GitHub Desktop.
Recollate All SQL Server Columns
--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
@AlexCuse
Copy link
Author

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

@gmmastros
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment