Changer la collation des colonnes d’une base

Comme vous le savez sûrement, changer le classement d’une base de données n’est pas une opération de tout repos : il faut changer le classement de toutes les colonnes caractère et il y a nombre de contraintes. Vous pouvez lire mon article à ce sujet.

Je vous propose ici un script qui va générer les commandes nécessaires à cette opération.

/*-------------------------------------------------------------------
[SCRIPT] Change_columns_collations  
[DESCRIPTION] Generate SQL Commands for changing columns collation
[DISCLAIMER] This code is provided "as is" without warranty of any kind, test at your own risk
[NOTE] Does not take into account the presence of Full Text Index or SQL Replication
[MAJ PAR] DATAFLY - Arian Papillon
[DATEMAJ] 20200309 
-------------------------------------------------------------------*/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET NOCOUNT ON;
GO

-- Base cible et collation cible
USE [AdventureCollation]   -- Renseigner la base cible
DECLARE @toCollation sysname
SET @toCollation = 'French_CI_AS' -- Renseigner la collation cible

-- Script generation
-- Variables and build check
DECLARE @filter NVARCHAR(MAX)
DECLARE @order NVARCHAR(MAX)
DECLARE @sqlcmd NVARCHAR(MAX)
-- SQL Build Check
DECLARE @version DECIMAL(4, 2);
DECLARE @subversion VARCHAR(2);
DECLARE @build DECIMAL(8, 4);
DECLARE @productversion VARCHAR(128);
DECLARE @indexfeatures INT;
SET @productversion = CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'));
SET @version
    = CAST(SUBSTRING(@productversion, 1, CHARINDEX('.', @productversion, CHARINDEX('.', @productversion) + 1) - 1) AS DECIMAL(4, 2));
SET @build
    = CAST(SUBSTRING(@productversion, CHARINDEX('.', @productversion, CHARINDEX('.', @productversion) + 1) + 1, 128) AS DECIMAL(8, 4));
IF EXISTS (SELECT * WHERE (@version < 10))
    SET @indexfeatures = 1; --2005
ELSE
BEGIN
    IF EXISTS (SELECT * WHERE (@version < 12))
        SET @indexfeatures = 2; -- 2012
    ELSE
        SET @indexfeatures = 3; --2014+
END;

-- Drop temporary tables
IF OBJECT_ID('tempdb..#CheckConstraintsCmds') <> 0
    DROP TABLE #CheckConstraintsCmds;
IF OBJECT_ID('tempdb..#FKConstraintsCmds') <> 0
    DROP TABLE #FKConstraintsCmds;
IF OBJECT_ID('tempdb..##IndexCmds') <> 0
    DROP TABLE ##IndexCmds;
IF OBJECT_ID('tempdb..#StatisticsCmds') <> 0
    DROP TABLE #StatisticsCmds;
IF OBJECT_ID('tempdb..#IndexedViewsCmds') <> 0
    DROP TABLE #IndexedViewsCmds;
IF OBJECT_ID('tempdb..#AlterCmds') <> 0
    DROP TABLE #AlterCmds;


-- Generate Check Constraints Commands
SELECT 'ALTER TABLE [' + SCHEMA_NAME(st.schema_id) + '].[' + st.name
        + '] WITH NOCHECK ' + 'ADD CONSTRAINT ' + scc.name + ' CHECK '
        + scc.definition AS 'CreateIt',
		'ALTER TABLE [' + SCHEMA_NAME(st.schema_id) + '].[' + st.name
        + '] DROP CONSTRAINT ' + scc.name AS 'DropIt'
INTO #CheckConstraintsCmds
FROM    sys.tables st
        JOIN sys.check_constraints scc ON st.object_id = scc.parent_object_id
		JOIN sys.columns AS c ON c.object_id = st.object_id AND scc.parent_column_id = c.column_id
		WHERE c.collation_name <> @toCollation
ORDER BY st.name;

-- Generate Foreign Keys Commands
DECLARE @FKName NVARCHAR(128);
DECLARE @FKColumnName NVARCHAR(128);
DECLARE @PKColumnName NVARCHAR(128);
DECLARE @fUpdateRule INT;
DECLARE @fDeleteRule INT;
DECLARE @fTableName NVARCHAR(128);
DECLARE @FieldNames NVARCHAR(500);
IF OBJECT_ID('tempdb..#Temp') <> 0
    DROP TABLE #Temp;
CREATE TABLE #Temp
    (
        PKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , PKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , PKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , PKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , FKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , FKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , FKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , FKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , KEY_SEQ INT
      , UPDATE_RULE INT
      , DELETE_RULE INT
      , FK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , PK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
      , DEFERRABILITY INT
    );
INSERT #Temp
        SELECT
            PKTABLE_QUALIFIER   = convert(sysname,db_name()),
            PKTABLE_OWNER       = convert(sysname,schema_name(o1.schema_id)),
            PKTABLE_NAME        = convert(sysname,o1.name),
            PKCOLUMN_NAME       = convert(sysname,c1.name),
            FKTABLE_QUALIFIER   = convert(sysname,db_name()),
            FKTABLE_OWNER       = convert(sysname,schema_name(o2.schema_id)),
            FKTABLE_NAME        = convert(sysname,o2.name),
            FKCOLUMN_NAME       = convert(sysname,c2.name),
            -- Force the column to be non-nullable (see SQL BU 325751)
            KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), convert(smallint,0)),
            UPDATE_RULE         = convert(smallint,
                                            case f.update_referential_action
                                            when 1 then 0
                                            when 0 then 1
                                            else f.update_referential_action
                                            end),
            DELETE_RULE         = convert(smallint,
                                            case f.delete_referential_action
                                            when 1 then 0
                                            when 0 then 1
                                            else f.delete_referential_action
                                            end),
            FK_NAME             = convert(sysname,object_name(f.object_id)),
            PK_NAME             = convert(sysname,i.name),
            DEFERRABILITY       = convert(smallint, 7)   -- SQL_NOT_DEFERRABLE
        from
            sys.objects o1,
            sys.objects o2,
            sys.columns c1,
            sys.columns c2,
            sys.foreign_keys f inner join
            sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
            sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
        where
            o1.object_id = f.referenced_object_id and
            o2.object_id = f.parent_object_id 
			AND
            c1.object_id = f.referenced_object_id and
            c2.object_id = f.parent_object_id and
            c1.column_id = k.referenced_column_id and
            c2.column_id = k.parent_column_id
			AND (c1.collation_name <> @toCollation AND c2.collation_name <> @toCollation )
        order by 5, 6, 7, 9, 8
IF OBJECT_ID('tempdb..#Temp1') <> 0
    DROP TABLE #Temp1;
SET @FieldNames = '';
SET @fTableName = '';
SELECT   DISTINCT FK_NAME AS FKName
                , FKTABLE_NAME AS FTName
                , FKTABLE_OWNER AS FSchema
                , @FieldNames AS FTFields
                , PKTABLE_NAME AS STName
                , PKTABLE_OWNER AS STSchema
                , @FieldNames AS STFields
                , @FieldNames AS FKType
INTO     #Temp1
FROM     #Temp
ORDER BY FK_NAME
       , FKTABLE_NAME
       , PKTABLE_NAME;
DECLARE FK_CUSROR CURSOR FOR
    SELECT FKName
    FROM   #Temp1;
OPEN FK_CUSROR;
FETCH FROM FK_CUSROR
INTO @FKName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE FK_FIELDS_CUSROR CURSOR FOR
            SELECT   FKCOLUMN_NAME
                   , PKCOLUMN_NAME
                   , UPDATE_RULE
                   , DELETE_RULE
            FROM     #Temp
            WHERE    FK_NAME = @FKName
            ORDER BY KEY_SEQ;
        OPEN FK_FIELDS_CUSROR;
        FETCH FROM FK_FIELDS_CUSROR
        INTO @FKColumnName
           , @PKColumnName
           , @fUpdateRule
           , @fDeleteRule;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                UPDATE #Temp1
                SET    FTFields = CASE WHEN LEN(FTFields) = 0 THEN
                                           '[' + @FKColumnName + ']'
                                       ELSE
                                           FTFields + ',[' + @FKColumnName + ']'
                                  END
                WHERE  FKName = @FKName;
                UPDATE #Temp1
                SET    STFields = CASE WHEN LEN(STFields) = 0 THEN
                                           '[' + @PKColumnName + ']'
                                       ELSE
                                           STFields + ',[' + @PKColumnName + ']'
                                  END
                WHERE  FKName = @FKName;
                FETCH NEXT FROM FK_FIELDS_CUSROR
                INTO @FKColumnName
                   , @PKColumnName
                   , @fUpdateRule
                   , @fDeleteRule;
            END;
        UPDATE #Temp1
        SET    FKType = CASE WHEN @fUpdateRule = 0 THEN
                                 FKType + ' ON UPDATE CASCADE'
                             ELSE FKType
                        END
        WHERE  FKName = @FKName;
        UPDATE #Temp1
        SET    FKType = CASE WHEN @fDeleteRule = 0 THEN
                                 FKType + ' ON DELETE CASCADE'
                             ELSE FKType
                        END
        WHERE  FKName = @FKName;
        CLOSE FK_FIELDS_CUSROR;
        DEALLOCATE FK_FIELDS_CUSROR;
        FETCH NEXT FROM FK_CUSROR
        INTO @FKName;
    END;
CLOSE FK_CUSROR;
DEALLOCATE FK_CUSROR;
SELECT 
'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['
       + FSchema + '].[' + FKName
       + ']'') AND parent_object_id = OBJECT_ID(N''[' + FSchema + '].['
       + FTName + ']'')) ALTER TABLE [' + FSchema + '].[' + FTName
       + '] WITH NOCHECK ADD CONSTRAINT [' + FKName + '] FOREIGN KEY ('
       + FTFields + ') REFERENCES [' + STSchema + '].[' + STName + '] ('
       + STFields + ') ' + FKType
       + CASE WHEN sys.foreign_keys.is_not_for_replication = 1 THEN
                  ' NOT FOR REPLICATION'
              ELSE ''
         END AS 'CreateIt',
'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['
       + FSchema + '].[' + FKName + ']'')) ALTER TABLE [' + FSchema + '].['
       + FTName + '] DROP CONSTRAINT [' + FKName + '];' AS 'DropIt'
INTO #FKConstraintsCmds
FROM   #Temp1
       JOIN sys.foreign_keys ON #Temp1.FKName = sys.foreign_keys.name;

-- Generate Index Commands
SET @filter = '
AND i.type_desc <> ''HEAP''
-- AND	OBJECTPROPERTY(o.object_id, ''IsView'') = 0
AND SC.collation_name <> ''' +@toCollation + '''  
'
SET @order = '
ORDER BY CreateIt
'
SET @sqlcmd = '
SELECT DISTINCT
'
SET @sqlcmd=@sqlcmd +'
    CASE
        WHEN i.type_desc = ''HEAP'' THEN
            ''''
        ELSE
            CASE
                WHEN i.is_primary_key = 1
                     OR i.is_unique_constraint = 1
                     OR ISNULL(OBJECTPROPERTY(o.object_id, ''TableIsMemoryOptimized''), 0) = 1 THEN
                    -- ALTER TABLE table ADD CONSTRAINT constraint unique_or_pk 
                    ''ALTER TABLE ['' + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] ''
                    + CASE
                          WHEN ISNULL(OBJECTPROPERTY(o.object_id, ''TableIsMemoryOptimized''), 0) = 1 THEN
                              -- ADD INDEX for in_memory
                              ''ADD INDEX ['' + i.name + ''] '' + i.type_desc + '' ''
                          ELSE
                              ''ADD CONSTRAINT ['' + i.name + CASE
                                                                WHEN i.is_primary_key = 1 THEN
                                                                    ''] PRIMARY KEY ''
                                                                ELSE
                                                                    ''] UNIQUE ''
                                                            END + i.type_desc + '' ''
                      END
                ELSE
                    -- CREATE unique indextype INDEX indexname ON table WHERE filter
                    ''CREATE '' + CASE
                                    WHEN i.is_unique = 1 THEN
                                        ''UNIQUE ''
                                    ELSE
                                        ''''
                                END + i.type_desc + '' INDEX ['' + i.name COLLATE DATABASE_DEFAULT + ''] ON [''
                    + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] ''
            END + -- liste colonnes sauf nccsi
    ISNULL(''('' + SUBSTRING(
                 (
                     SELECT '','' + SC.name AS [text()]
                     FROM sys.index_columns IC
                         JOIN sys.columns SC
                             ON IC.object_id = SC.object_id
                                AND IC.column_id = SC.column_id
                     WHERE IC.object_id = o.object_id
                           AND IC.index_id = i.index_id
                           AND IC.is_included_column = 0
                     ORDER BY IC.key_ordinal
                     FOR XML PATH('''')
                 ),
                 2,
                 1000
                          ) + '')'',
           ''''
          ) + -- incluses ou nccsi et sauf ccsi
    CASE
        WHEN i.type_desc = ''CLUSTERED COLUMNSTORE'' THEN
            ''''
        ELSE
            ISNULL(   CASE
                          WHEN i.type_desc <> ''NONCLUSTERED COLUMNSTORE'' THEN
                              '' INCLUDE ''
                          ELSE
                              ''''
                      END + ''('' + SUBSTRING(
                                  (
                                      SELECT '','' + SC.name AS [text()]
                                      FROM sys.index_columns IC
                                          JOIN sys.columns SC
                                              ON IC.object_id = SC.object_id
                                                 AND IC.column_id = SC.column_id
                                      WHERE IC.object_id = o.object_id
                                            AND IC.index_id = i.index_id
                                            AND IC.is_included_column = 1
                                      ORDER BY IC.key_ordinal
                                      FOR XML PATH('''')
                                  ),
                                  2,
                                  1000
                                           ) + '')'',
                      ''''
                  )
    END     + 
'
SET @sqlcmd = @sqlcmd +
    CASE
        WHEN @indexfeatures < 2 THEN
            ''''''
        ELSE
            'ISNULL('' WHERE '' + i.filter_definition, '''')'
    END 
-- options : fillfactor, padindex, data compression
SET @sqlcmd=@sqlcmd + '
    END '
SET @sqlcmd = @sqlcmd + 
'+'' WITH (FILLFACTOR = '' + CAST(CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END as varchar(3)) '
SET @sqlcmd = @sqlcmd +
    CASE
        WHEN @indexfeatures < 2 THEN
            ''''''
        ELSE
            '+'', DATA_COMPRESSION = '' + p.data_compression_desc'
    END 
SET @sqlcmd = @sqlcmd + 
'+'')'''
SET @sqlcmd = @sqlcmd +
'+'' ON [''+sp.name+'']'''
SET @sqlcmd=@sqlcmd + '
	AS CreateIt,
	'
SET @sqlcmd = @sqlcmd + '
-- suppression
    CASE
        WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN
                    ''DROP INDEX ['' + i.name COLLATE DATABASE_DEFAULT + ''] ON [''
                    + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + '']''
		ELSE 
			''ALTER TABLE [''+SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] DROP CONSTRAINT ['' + i.name COLLATE DATABASE_DEFAULT + '']''
        END AS DropIt,
		i.type_desc
	'
SET @sqlcmd=@sqlcmd + '
INTO ##IndexCmds
FROM sys.objects o
    LEFT JOIN sys.indexes i
        ON o.object_id = i.object_id
	JOIN sys.index_columns IC ON o.object_id = IC.object_id and i.index_id = IC.index_id
                      JOIN sys.columns SC
                          ON IC.object_id = SC.object_id
                          AND IC.column_id = SC.column_id
    LEFT JOIN sys.dm_db_index_usage_stats s
        ON s.index_id = i.index_id
           AND s.object_id = i.object_id
           AND s.database_id = DB_ID()
    JOIN sys.partitions p
        ON i.object_id = p.object_id
           AND i.index_id = p.index_id
    JOIN sys.allocation_units a
        ON p.partition_id = a.container_id
    JOIN sys.data_spaces sp
        ON a.data_space_id = sp.data_space_id
    LEFT JOIN sys.key_constraints kc
        ON o.object_id = kc.parent_object_id
           AND kc.type = ''PK''
WHERE o.type IN ( ''U'', ''V'' )
      AND OBJECTPROPERTY(o.object_id, ''IsMSShipped'') = 0
      AND OBJECT_NAME(o.object_id) <> ''sysdiagrams'' 
'
SET @sqlcmd = @sqlcmd + @filter  + @order
EXEC sp_executesql @sqlcmd

-- Generate Statistics Commands
SELECT DISTINCT 
'CREATE STATISTICS ['+s.name+' ] ON ['+ SCHEMA_NAME(o.schema_id)+'].['+
 OBJECT_NAME(s.object_id) + '] ('+
 SUBSTRING((SELECT ','+c.name AS [text()] FROM sys.stats_columns AS sc LEFT JOIN sys.columns AS c ON c.object_id = s.object_id AND c.object_id = sc.object_id AND c.column_id = sc.column_id
			 WHERE s.stats_id = sc.stats_id AND s.object_id = sc.object_id 
			 ORDER BY sc.stats_column_id
			 FOR XML PATH('')),2,1000) 
			 +')' + CASE WHEN s.has_filter = 1 THEN ' WHERE '+s.filter_definition ELSE ''END
AS CreateIt
, 'DROP STATISTICS ['+ SCHEMA_NAME(o.schema_id)+'].['+
 OBJECT_NAME(s.object_id) + '].['+s.name+']'
AS DropIt
INTO #StatisticsCmds
FROM    sys.stats AS s
        JOIN sys.objects o ON s.object_id = o.object_id
		JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id 
		JOIN sys.columns AS c ON c.object_id = s.object_id AND c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
        AND OBJECTPROPERTY(s.object_id, 'IsMSShipped') = 0
        AND OBJECT_NAME(s.object_id) <> 'sysdiagrams' -- seulement tables utilisateur
	    AND s.user_created = 1  
		AND c.collation_name <> @toCollation

-- Generate Indexed Views Commands
SELECT  DISTINCT definition + ' GO ' AS CreateIt, 'DROP VIEW ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(O.object_id)+']' AS DropIt 
INTO #IndexedViewsCmds
FROM sys.sql_modules S
JOIN sys.objects O ON S.object_id = O.object_id
JOIN sys.sql_dependencies D ON S.object_id = D.object_id
inner join sys.objects ref on ref.object_id = d.referenced_major_id
INNER JOIN sys.columns c ON d.referenced_major_id = c.object_id AND d.referenced_minor_id = c.column_id
WHERE is_schema_bound = 1 AND O.type = 'V' AND c.collation_name <> @toCollation

-- Generate Alter Columns Commands
SELECT 'ALTER TABLE [' +  INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
'] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
WHEN DATA_TYPE in ('text','ntext') then ''
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
WHEN 'YES' THEN 'NULL'
WHEN 'No' THEN 'NOT NULL'
END AS AlterIt
INTO #AlterCmds
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
AND COLLATION_NAME <> @toCollation
AND OBJECTPROPERTY(OBJECT_ID('['+INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME+']'), 'IsMSShipped') = 0
AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME <> 'sysdiagrams' -- seulement tables utilisateur
AND COLUMNPROPERTY(OBJECT_ID('[' +  INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +']'),INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,'IsComputed') = 0
-- Computed columns
UNION ALL
SELECT 'ALTER TABLE ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(CC.object_id)+'] DROP COLUMN ['+CC.name+']; ALTER TABLE ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(CC.object_id)+'] ADD ['+CC.name+'] AS '+CC.definition + CASE	WHEN CC.is_persisted=1 THEN ' PERSISTED' ELSE '' END + ' COLLATE French_CI_AS' 
FROM sys.computed_columns AS CC
JOIN sys.objects AS O ON O.object_id = CC.object_id
WHERE collation_name <> @toCollation


-- Generate global script
--------------------------
IF OBJECT_ID('tempdb..#ChangeCollationSQLCmds') <> 0
    DROP TABLE #ChangeCollationSQLCmds;
CREATE TABLE #ChangeCollationSQLCmds (id INT IDENTITY NOT NULL PRIMARY KEY, sqlcmd NVARCHAR(MAX))

INSERT #ChangeCollationSQLCmds SELECT '/*-------------------------------------------------------------------'
INSERT #ChangeCollationSQLCmds SELECT '[SCRIPT] Change Columns Collations  '
INSERT #ChangeCollationSQLCmds SELECT '[DESCRIPTION] SQL Commands for changing columns collation'
INSERT #ChangeCollationSQLCmds SELECT '[DISCLAIMER] This code is provided "as is" without warranty of any kind, test at your own risk'
INSERT #ChangeCollationSQLCmds SELECT '[NOTE] Does not take into account the presence of Full Text Index or SQL Replication'
INSERT #ChangeCollationSQLCmds SELECT '[NOTE] Consider also switching the database to simple recovery model'
INSERT #ChangeCollationSQLCmds SELECT '[GENERATED FROM SCRIPT] http://scripts.mssql.fr - DATAFLY - Arian Papillon'
INSERT #ChangeCollationSQLCmds SELECT '-------------------------------------------------------------------*/'

INSERT #ChangeCollationSQLCmds SELECT '-- Step 0 - Drop System Tables'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop system tables'''
IF OBJECT_ID('sysdiagrams') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE sysdiagrams'
IF OBJECT_ID('dtproperties') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE dtproperties'
IF OBJECT_ID('sysssislog') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE sysssislog'
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

INSERT #ChangeCollationSQLCmds SELECT '-- Step 1 - Drop Check Constraints'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop CHECK Constraints'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #CheckConstraintsCmds 
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 2. Drop FK
INSERT #ChangeCollationSQLCmds SELECT '-- Step 2 - Drop FK'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop FK Constraints'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #FKConstraintsCmds 
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 3. Drop nonclustered index  
INSERT #ChangeCollationSQLCmds SELECT '-- Step 3 - Drop nonclustered index'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop nonclustered index'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM ##IndexCmds WHERE type_desc = 'NONCLUSTERED'
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 4. Drop clustered index 
INSERT #ChangeCollationSQLCmds SELECT '-- Step 4 - Drop clustered index'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop clustered index'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM ##IndexCmds WHERE type_desc = 'CLUSTERED'
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 5. Supprimer toutes les statistiques utilisateur sur des colonnes caractère
INSERT #ChangeCollationSQLCmds SELECT '-- Step 5 - Drop statistics'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop statistics'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #StatisticsCmds
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 6. Supprimer les vues indexées
INSERT #ChangeCollationSQLCmds SELECT '-- Step 6 - Drop indexed views'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop indexed views'''
INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #IndexedViewsCmds
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 7. Changer le classement des colonnes
INSERT #ChangeCollationSQLCmds SELECT '-- Step 7 - Alter Columns'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Alter Columns'''
INSERT #ChangeCollationSQLCmds SELECT AlterIt FROM #AlterCmds
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'
GO

-- Ici on peut déjà changer le classement de la base de données

-- 8. Recréer les vues indexées
INSERT #ChangeCollationSQLCmds SELECT '-- Step 8 - Create indexed views'
INSERT #ChangeCollationSQLCmds SELECT '--IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create indexed views'''
INSERT #ChangeCollationSQLCmds SELECT 'GO'
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #IndexedViewsCmds 
INSERT #ChangeCollationSQLCmds SELECT '--END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 9. Recréer les index clustered 
INSERT #ChangeCollationSQLCmds SELECT '-- Step 9 - Create clustered index'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create clustered index'''
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM ##IndexCmds WHERE type_desc = 'CLUSTERED'
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 10. Recréer les index nonclustered 
INSERT #ChangeCollationSQLCmds SELECT '-- Step 10 - Create nonclustered index'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create nonclustered index'''
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM ##IndexCmds WHERE type_desc = 'NONCLUSTERED'
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

--11. Recréer les statistiques utilisateur
INSERT #ChangeCollationSQLCmds SELECT '-- Step 11 - Create statistics'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create statistics'''
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #StatisticsCmds
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 12. Recréer les FK avec NOCHECK
INSERT #ChangeCollationSQLCmds SELECT '-- Step 12 - Create FK'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create FK Constraints'''
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #FKConstraintsCmds 
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 13. Recréer les contraintes CHECK avec NOCHECK
INSERT #ChangeCollationSQLCmds SELECT '-- Step 13 - Create Check Constraints'
INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'
INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create CHECK Constraints'''
INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #CheckConstraintsCmds 
INSERT #ChangeCollationSQLCmds SELECT 'END'
INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- Comments
INSERT #ChangeCollationSQLCmds SELECT '/*'
INSERT #ChangeCollationSQLCmds SELECT '-- Change database collation commands'
INSERT #ChangeCollationSQLCmds SELECT '  USE master '
INSERT #ChangeCollationSQLCmds SELECT '  GO'
INSERT #ChangeCollationSQLCmds SELECT '	ALTER DATABASE xxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
INSERT #ChangeCollationSQLCmds SELECT '  ALTER DATABASE xxxxxx COLLATE xxxxxx'
INSERT #ChangeCollationSQLCmds SELECT '  GO'
INSERT #ChangeCollationSQLCmds SELECT '*/'
INSERT #ChangeCollationSQLCmds SELECT '---------------------------------------------'
INSERT #ChangeCollationSQLCmds SELECT '-- End of script ----------------------------'


-- View script
SELECT * FROM #ChangeCollationSQLCmds
-- End of script


-- could be necessary ? refreshsqlmodule
/*
DECLARE @cmd NVARCHAR(MAX) = ''
SELECT @cmd=@cmd + 'EXEC sys.sp_refreshsqlmodule ''['+ SCHEMA_NAME(o.schema_id)+'].['+
 OBJECT_NAME(o.object_id) + ']''; ' 
FROM sys.sql_modules m JOIN sys.objects o
ON m.object_id = o.object_id 
WHERE o.type IN ('V','FN','TF','P','TR')
EXEC (@cmd)
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

/*-------------------------------------------------------------------

[SCRIPT] Change_columns_collations

[DESCRIPTION] Generate SQL Commands for changing columns collation

[DISCLAIMER] This code is provided "as is" without warranty of any kind, test at your own risk

[NOTE] Does not take into account the presence of Full Text Index or SQL Replication

[MAJ PAR] DATAFLY - Arian Papillon

[DATEMAJ] 20200309

-------------------------------------------------------------------*/

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET NOCOUNT ON;

-- Base cible et collation cible

USE [AdventureCollation] -- Renseigner la base cible

DECLARE @toCollation sysname

SET @toCollation = 'French_CI_AS' -- Renseigner la collation cible

-- Script generation

-- Variables and build check

DECLARE @filter NVARCHAR(MAX)

DECLARE @order NVARCHAR(MAX)

DECLARE @sqlcmd NVARCHAR(MAX)

-- SQL Build Check

DECLARE @version DECIMAL(4, 2);

DECLARE @subversion VARCHAR(2);

DECLARE @build DECIMAL(8, 4);

DECLARE @productversion VARCHAR(128);

DECLARE @indexfeatures INT;

SET @productversion = CONVERT(VARCHAR(128), SERVERPROPERTY('ProductVersion'));

SET @version

= CAST(SUBSTRING(@productversion, 1, CHARINDEX('.', @productversion, CHARINDEX('.', @productversion) + 1) - 1) AS DECIMAL(4, 2));

SET @build

= CAST(SUBSTRING(@productversion, CHARINDEX('.', @productversion, CHARINDEX('.', @productversion) + 1) + 1, 128) AS DECIMAL(8, 4));

IF EXISTS (SELECT * WHERE (@version < 10))

SET @indexfeatures = 1; --2005

ELSE

BEGIN

IF EXISTS (SELECT * WHERE (@version < 12))

SET @indexfeatures = 2; -- 2012

ELSE

SET @indexfeatures = 3; --2014+

END;

-- Drop temporary tables

IF OBJECT_ID('tempdb..#CheckConstraintsCmds') <> 0

DROP TABLE #CheckConstraintsCmds;

IF OBJECT_ID('tempdb..#FKConstraintsCmds') <> 0

DROP TABLE #FKConstraintsCmds;

IF OBJECT_ID('tempdb..##IndexCmds') <> 0

DROP TABLE ##IndexCmds;

IF OBJECT_ID('tempdb..#StatisticsCmds') <> 0

DROP TABLE #StatisticsCmds;

IF OBJECT_ID('tempdb..#IndexedViewsCmds') <> 0

DROP TABLE #IndexedViewsCmds;

IF OBJECT_ID('tempdb..#AlterCmds') <> 0

DROP TABLE #AlterCmds;

-- Generate Check Constraints Commands

SELECT 'ALTER TABLE [' + SCHEMA_NAME(st.schema_id) + '].[' + st.name

+ '] WITH NOCHECK ' + 'ADD CONSTRAINT ' + scc.name + ' CHECK '

+ scc.definition AS 'CreateIt',

'ALTER TABLE [' + SCHEMA_NAME(st.schema_id) + '].[' + st.name

+ '] DROP CONSTRAINT ' + scc.name AS 'DropIt'

INTO #CheckConstraintsCmds

FROM sys.tables st

JOIN sys.check_constraints scc ON st.object_id = scc.parent_object_id

JOIN sys.columns AS c ON c.object_id = st.object_id AND scc.parent_column_id = c.column_id

WHERE c.collation_name <> @toCollation

ORDER BY st.name;

-- Generate Foreign Keys Commands

DECLARE @FKName NVARCHAR(128);

DECLARE @FKColumnName NVARCHAR(128);

DECLARE @PKColumnName NVARCHAR(128);

DECLARE @fUpdateRule INT;

DECLARE @fDeleteRule INT;

DECLARE @fTableName NVARCHAR(128);

DECLARE @FieldNames NVARCHAR(500);

IF OBJECT_ID('tempdb..#Temp') <> 0

DROP TABLE #Temp;

CREATE TABLE #Temp

(

PKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT

, PKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT

, PKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, PKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, FKTABLE_QUALIFIER NVARCHAR(128) COLLATE DATABASE_DEFAULT

, FKTABLE_OWNER NVARCHAR(128) COLLATE DATABASE_DEFAULT

, FKTABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, FKCOLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, KEY_SEQ INT

, UPDATE_RULE INT

, DELETE_RULE INT

, FK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, PK_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT

, DEFERRABILITY INT

);

INSERT #Temp

SELECT

PKTABLE_QUALIFIER = convert(sysname,db_name()),

PKTABLE_OWNER = convert(sysname,schema_name(o1.schema_id)),

PKTABLE_NAME = convert(sysname,o1.name),

PKCOLUMN_NAME = convert(sysname,c1.name),

FKTABLE_QUALIFIER = convert(sysname,db_name()),

FKTABLE_OWNER = convert(sysname,schema_name(o2.schema_id)),

FKTABLE_NAME = convert(sysname,o2.name),

FKCOLUMN_NAME = convert(sysname,c2.name),

-- Force the column to be non-nullable (see SQL BU 325751)

KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), convert(smallint,0)),

UPDATE_RULE = convert(smallint,

case f.update_referential_action

when 1 then 0

when 0 then 1

else f.update_referential_action

end),

DELETE_RULE = convert(smallint,

case f.delete_referential_action

when 1 then 0

when 0 then 1

else f.delete_referential_action

end),

FK_NAME = convert(sysname,object_name(f.object_id)),

PK_NAME = convert(sysname,i.name),

DEFERRABILITY = convert(smallint, 7) -- SQL_NOT_DEFERRABLE

from

sys.objects o1,

sys.objects o2,

sys.columns c1,

sys.columns c2,

sys.foreign_keys f inner join

sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join

sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)

where

o1.object_id = f.referenced_object_id and

o2.object_id = f.parent_object_id

AND

c1.object_id = f.referenced_object_id and

c2.object_id = f.parent_object_id and

c1.column_id = k.referenced_column_id and

c2.column_id = k.parent_column_id

AND (c1.collation_name <> @toCollation AND c2.collation_name <> @toCollation )

order by 5, 6, 7, 9, 8

IF OBJECT_ID('tempdb..#Temp1') <> 0

DROP TABLE #Temp1;

SET @FieldNames = '';

SET @fTableName = '';

SELECT DISTINCT FK_NAME AS FKName

, FKTABLE_NAME AS FTName

, FKTABLE_OWNER AS FSchema

, @FieldNames AS FTFields

, PKTABLE_NAME AS STName

, PKTABLE_OWNER AS STSchema

, @FieldNames AS STFields

, @FieldNames AS FKType

INTO #Temp1

FROM #Temp

ORDER BY FK_NAME

, FKTABLE_NAME

, PKTABLE_NAME;

DECLARE FK_CUSROR CURSOR FOR

SELECT FKName

FROM #Temp1;

OPEN FK_CUSROR;

FETCH FROM FK_CUSROR

INTO @FKName;

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE FK_FIELDS_CUSROR CURSOR FOR

SELECT FKCOLUMN_NAME

, PKCOLUMN_NAME

, UPDATE_RULE

, DELETE_RULE

FROM #Temp

WHERE FK_NAME = @FKName

ORDER BY KEY_SEQ;

OPEN FK_FIELDS_CUSROR;

FETCH FROM FK_FIELDS_CUSROR

INTO @FKColumnName

, @PKColumnName

, @fUpdateRule

, @fDeleteRule;

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE #Temp1

SET FTFields = CASE WHEN LEN(FTFields) = 0 THEN

'[' + @FKColumnName + ']'

ELSE

FTFields + ',[' + @FKColumnName + ']'

END

WHERE FKName = @FKName;

UPDATE #Temp1

SET STFields = CASE WHEN LEN(STFields) = 0 THEN

'[' + @PKColumnName + ']'

ELSE

STFields + ',[' + @PKColumnName + ']'

END

WHERE FKName = @FKName;

FETCH NEXT FROM FK_FIELDS_CUSROR

INTO @FKColumnName

, @PKColumnName

, @fUpdateRule

, @fDeleteRule;

END;

UPDATE #Temp1

SET FKType = CASE WHEN @fUpdateRule = 0 THEN

FKType + ' ON UPDATE CASCADE'

ELSE FKType

END

WHERE FKName = @FKName;

UPDATE #Temp1

SET FKType = CASE WHEN @fDeleteRule = 0 THEN

FKType + ' ON DELETE CASCADE'

ELSE FKType

END

WHERE FKName = @FKName;

CLOSE FK_FIELDS_CUSROR;

DEALLOCATE FK_FIELDS_CUSROR;

FETCH NEXT FROM FK_CUSROR

INTO @FKName;

END;

CLOSE FK_CUSROR;

DEALLOCATE FK_CUSROR;

SELECT

'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['

+ FSchema + '].[' + FKName

+ ']'') AND parent_object_id = OBJECT_ID(N''[' + FSchema + '].['

+ FTName + ']'')) ALTER TABLE [' + FSchema + '].[' + FTName

+ '] WITH NOCHECK ADD CONSTRAINT [' + FKName + '] FOREIGN KEY ('

+ FTFields + ') REFERENCES [' + STSchema + '].[' + STName + '] ('

+ STFields + ') ' + FKType

+ CASE WHEN sys.foreign_keys.is_not_for_replication = 1 THEN

' NOT FOR REPLICATION'

ELSE ''

END AS 'CreateIt',

'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''['

+ FSchema + '].[' + FKName + ']'')) ALTER TABLE [' + FSchema + '].['

+ FTName + '] DROP CONSTRAINT [' + FKName + '];' AS 'DropIt'

INTO #FKConstraintsCmds

FROM #Temp1

JOIN sys.foreign_keys ON #Temp1.FKName = sys.foreign_keys.name;

-- Generate Index Commands

SET @filter = '

AND i.type_desc <> ''HEAP''

-- AND OBJECTPROPERTY(o.object_id, ''IsView'') = 0

AND SC.collation_name <> ''' +@toCollation + '''

SET @order = '

ORDER BY CreateIt

SET @sqlcmd = '

SELECT DISTINCT

SET @sqlcmd=@sqlcmd +'

CASE

WHEN i.type_desc = ''HEAP'' THEN

''''

ELSE

CASE

WHEN i.is_primary_key = 1

OR i.is_unique_constraint = 1

OR ISNULL(OBJECTPROPERTY(o.object_id, ''TableIsMemoryOptimized''), 0) = 1 THEN

-- ALTER TABLE table ADD CONSTRAINT constraint unique_or_pk

''ALTER TABLE ['' + SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] ''

+ CASE

WHEN ISNULL(OBJECTPROPERTY(o.object_id, ''TableIsMemoryOptimized''), 0) = 1 THEN

-- ADD INDEX for in_memory

''ADD INDEX ['' + i.name + ''] '' + i.type_desc + '' ''

ELSE

''ADD CONSTRAINT ['' + i.name + CASE

WHEN i.is_primary_key = 1 THEN

''] PRIMARY KEY ''

ELSE

''] UNIQUE ''

END + i.type_desc + '' ''

END

ELSE

-- CREATE unique indextype INDEX indexname ON table WHERE filter

''CREATE '' + CASE

WHEN i.is_unique = 1 THEN

''UNIQUE ''

ELSE

''''

END + i.type_desc + '' INDEX ['' + i.name COLLATE DATABASE_DEFAULT + ''] ON [''

+ SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] ''

END + -- liste colonnes sauf nccsi

ISNULL(''('' + SUBSTRING(

(

SELECT '','' + SC.name AS [text()]

FROM sys.index_columns IC

JOIN sys.columns SC

ON IC.object_id = SC.object_id

AND IC.column_id = SC.column_id

WHERE IC.object_id = o.object_id

AND IC.index_id = i.index_id

AND IC.is_included_column = 0

ORDER BY IC.key_ordinal

FOR XML PATH('''')

1000

) + '')'',

''''

) + -- incluses ou nccsi et sauf ccsi

CASE

WHEN i.type_desc = ''CLUSTERED COLUMNSTORE'' THEN

''''

ELSE

ISNULL( CASE

WHEN i.type_desc <> ''NONCLUSTERED COLUMNSTORE'' THEN

'' INCLUDE ''

ELSE

''''

END + ''('' + SUBSTRING(

(

SELECT '','' + SC.name AS [text()]

FROM sys.index_columns IC

JOIN sys.columns SC

ON IC.object_id = SC.object_id

AND IC.column_id = SC.column_id

WHERE IC.object_id = o.object_id

AND IC.index_id = i.index_id

AND IC.is_included_column = 1

ORDER BY IC.key_ordinal

FOR XML PATH('''')

1000

) + '')'',

''''

)

END +

SET @sqlcmd = @sqlcmd +

CASE

WHEN @indexfeatures < 2 THEN

''''''

ELSE

'ISNULL('' WHERE '' + i.filter_definition, '''')'

END

-- options : fillfactor, padindex, data compression

SET @sqlcmd=@sqlcmd + '

END '

SET @sqlcmd = @sqlcmd +

'+'' WITH (FILLFACTOR = '' + CAST(CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END as varchar(3)) '

SET @sqlcmd = @sqlcmd +

CASE

WHEN @indexfeatures < 2 THEN

''''''

ELSE

'+'', DATA_COMPRESSION = '' + p.data_compression_desc'

END

SET @sqlcmd = @sqlcmd +

'+'')'''

SET @sqlcmd = @sqlcmd +

'+'' ON [''+sp.name+'']'''

SET @sqlcmd=@sqlcmd + '

AS CreateIt,

SET @sqlcmd = @sqlcmd + '

-- suppression

CASE

WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN

''DROP INDEX ['' + i.name COLLATE DATABASE_DEFAULT + ''] ON [''

+ SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + '']''

ELSE

''ALTER TABLE [''+SCHEMA_NAME(o.schema_id) + ''].['' + OBJECT_NAME(o.object_id) + ''] DROP CONSTRAINT ['' + i.name COLLATE DATABASE_DEFAULT + '']''

END AS DropIt,

i.type_desc

SET @sqlcmd=@sqlcmd + '

INTO ##IndexCmds

FROM sys.objects o

LEFT JOIN sys.indexes i

ON o.object_id = i.object_id

JOIN sys.index_columns IC ON o.object_id = IC.object_id and i.index_id = IC.index_id

JOIN sys.columns SC

ON IC.object_id = SC.object_id

AND IC.column_id = SC.column_id

LEFT JOIN sys.dm_db_index_usage_stats s

ON s.index_id = i.index_id

AND s.object_id = i.object_id

AND s.database_id = DB_ID()

JOIN sys.partitions p

ON i.object_id = p.object_id

AND i.index_id = p.index_id

JOIN sys.allocation_units a

ON p.partition_id = a.container_id

JOIN sys.data_spaces sp

ON a.data_space_id = sp.data_space_id

LEFT JOIN sys.key_constraints kc

ON o.object_id = kc.parent_object_id

AND kc.type = ''PK''

WHERE o.type IN ( ''U'', ''V'' )

AND OBJECTPROPERTY(o.object_id, ''IsMSShipped'') = 0

AND OBJECT_NAME(o.object_id) <> ''sysdiagrams''

SET @sqlcmd = @sqlcmd + @filter + @order

EXEC sp_executesql @sqlcmd

-- Generate Statistics Commands

SELECT DISTINCT

'CREATE STATISTICS ['+s.name+' ] ON ['+ SCHEMA_NAME(o.schema_id)+'].['+

OBJECT_NAME(s.object_id) + '] ('+

SUBSTRING((SELECT ','+c.name AS [text()] FROM sys.stats_columns AS sc LEFT JOIN sys.columns AS c ON c.object_id = s.object_id AND c.object_id = sc.object_id AND c.column_id = sc.column_id

WHERE s.stats_id = sc.stats_id AND s.object_id = sc.object_id

ORDER BY sc.stats_column_id

FOR XML PATH('')),2,1000)

+')' + CASE WHEN s.has_filter = 1 THEN ' WHERE '+s.filter_definition ELSE ''END

AS CreateIt

, 'DROP STATISTICS ['+ SCHEMA_NAME(o.schema_id)+'].['+

OBJECT_NAME(s.object_id) + '].['+s.name+']'

AS DropIt

INTO #StatisticsCmds

FROM sys.stats AS s

JOIN sys.objects o ON s.object_id = o.object_id

JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id

JOIN sys.columns AS c ON c.object_id = s.object_id AND c.object_id = sc.object_id AND c.column_id = sc.column_id

WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1

AND OBJECTPROPERTY(s.object_id, 'IsMSShipped') = 0

AND OBJECT_NAME(s.object_id) <> 'sysdiagrams' -- seulement tables utilisateur

AND s.user_created = 1

AND c.collation_name <> @toCollation

-- Generate Indexed Views Commands

SELECT DISTINCT definition + ' GO ' AS CreateIt, 'DROP VIEW ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(O.object_id)+']' AS DropIt

INTO #IndexedViewsCmds

FROM sys.sql_modules S

JOIN sys.objects O ON S.object_id = O.object_id

JOIN sys.sql_dependencies D ON S.object_id = D.object_id

inner join sys.objects ref on ref.object_id = d.referenced_major_id

INNER JOIN sys.columns c ON d.referenced_major_id = c.object_id AND d.referenced_minor_id = c.column_id

WHERE is_schema_bound = 1 AND O.type = 'V' AND c.collation_name <> @toCollation

-- Generate Alter Columns Commands

SELECT 'ALTER TABLE [' + INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +

'] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE +

CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'

WHEN DATA_TYPE in ('text','ntext') then ''

WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL

THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )

ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END

+' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE

WHEN 'YES' THEN 'NULL'

WHEN 'No' THEN 'NOT NULL'

END AS AlterIt

INTO #AlterCmds

FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES

ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME

AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA

WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')

AND TABLE_TYPE = 'BASE TABLE'

AND COLLATION_NAME <> @toCollation

AND OBJECTPROPERTY(OBJECT_ID('['+INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME+']'), 'IsMSShipped') = 0

AND INFORMATION_SCHEMA.COLUMNS.TABLE_NAME <> 'sysdiagrams' -- seulement tables utilisateur

AND COLUMNPROPERTY(OBJECT_ID('[' + INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA +'].['+INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +']'),INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME,'IsComputed') = 0

-- Computed columns

UNION ALL

SELECT 'ALTER TABLE ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(CC.object_id)+'] DROP COLUMN ['+CC.name+']; ALTER TABLE ['+SCHEMA_NAME(O.schema_id)+'].['+OBJECT_NAME(CC.object_id)+'] ADD ['+CC.name+'] AS '+CC.definition + CASE WHEN CC.is_persisted=1 THEN ' PERSISTED' ELSE '' END + ' COLLATE French_CI_AS'

FROM sys.computed_columns AS CC

JOIN sys.objects AS O ON O.object_id = CC.object_id

WHERE collation_name <> @toCollation

-- Generate global script

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

IF OBJECT_ID('tempdb..#ChangeCollationSQLCmds') <> 0

DROP TABLE #ChangeCollationSQLCmds;

CREATE TABLE #ChangeCollationSQLCmds (id INT IDENTITY NOT NULL PRIMARY KEY, sqlcmd NVARCHAR(MAX))

INSERT #ChangeCollationSQLCmds SELECT '/*-------------------------------------------------------------------'

INSERT #ChangeCollationSQLCmds SELECT '[SCRIPT] Change Columns Collations '

INSERT #ChangeCollationSQLCmds SELECT '[DESCRIPTION] SQL Commands for changing columns collation'

INSERT #ChangeCollationSQLCmds SELECT '[DISCLAIMER] This code is provided "as is" without warranty of any kind, test at your own risk'

INSERT #ChangeCollationSQLCmds SELECT '[NOTE] Does not take into account the presence of Full Text Index or SQL Replication'

INSERT #ChangeCollationSQLCmds SELECT '[NOTE] Consider also switching the database to simple recovery model'

INSERT #ChangeCollationSQLCmds SELECT '[GENERATED FROM SCRIPT] http://scripts.mssql.fr - DATAFLY - Arian Papillon'

INSERT #ChangeCollationSQLCmds SELECT '-------------------------------------------------------------------*/'

INSERT #ChangeCollationSQLCmds SELECT '-- Step 0 - Drop System Tables'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop system tables'''

IF OBJECT_ID('sysdiagrams') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE sysdiagrams'

IF OBJECT_ID('dtproperties') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE dtproperties'

IF OBJECT_ID('sysssislog') <> 0 INSERT #ChangeCollationSQLCmds SELECT 'DROP TABLE sysssislog'

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

INSERT #ChangeCollationSQLCmds SELECT '-- Step 1 - Drop Check Constraints'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop CHECK Constraints'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #CheckConstraintsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 2. Drop FK

INSERT #ChangeCollationSQLCmds SELECT '-- Step 2 - Drop FK'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop FK Constraints'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #FKConstraintsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 3. Drop nonclustered index

INSERT #ChangeCollationSQLCmds SELECT '-- Step 3 - Drop nonclustered index'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop nonclustered index'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM ##IndexCmds WHERE type_desc = 'NONCLUSTERED'

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 4. Drop clustered index

INSERT #ChangeCollationSQLCmds SELECT '-- Step 4 - Drop clustered index'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop clustered index'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM ##IndexCmds WHERE type_desc = 'CLUSTERED'

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 5. Supprimer toutes les statistiques utilisateur sur des colonnes caractère

INSERT #ChangeCollationSQLCmds SELECT '-- Step 5 - Drop statistics'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop statistics'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #StatisticsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 6. Supprimer les vues indexées

INSERT #ChangeCollationSQLCmds SELECT '-- Step 6 - Drop indexed views'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Drop indexed views'''

INSERT #ChangeCollationSQLCmds SELECT DropIt FROM #IndexedViewsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 7. Changer le classement des colonnes

INSERT #ChangeCollationSQLCmds SELECT '-- Step 7 - Alter Columns'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Alter Columns'''

INSERT #ChangeCollationSQLCmds SELECT AlterIt FROM #AlterCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- Ici on peut déjà changer le classement de la base de données

-- 8. Recréer les vues indexées

INSERT #ChangeCollationSQLCmds SELECT '-- Step 8 - Create indexed views'

INSERT #ChangeCollationSQLCmds SELECT '--IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create indexed views'''

INSERT #ChangeCollationSQLCmds SELECT 'GO'

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #IndexedViewsCmds

INSERT #ChangeCollationSQLCmds SELECT '--END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 9. Recréer les index clustered

INSERT #ChangeCollationSQLCmds SELECT '-- Step 9 - Create clustered index'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create clustered index'''

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM ##IndexCmds WHERE type_desc = 'CLUSTERED'

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 10. Recréer les index nonclustered

INSERT #ChangeCollationSQLCmds SELECT '-- Step 10 - Create nonclustered index'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create nonclustered index'''

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM ##IndexCmds WHERE type_desc = 'NONCLUSTERED'

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

--11. Recréer les statistiques utilisateur

INSERT #ChangeCollationSQLCmds SELECT '-- Step 11 - Create statistics'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create statistics'''

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #StatisticsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 12. Recréer les FK avec NOCHECK

INSERT #ChangeCollationSQLCmds SELECT '-- Step 12 - Create FK'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create FK Constraints'''

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #FKConstraintsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- 13. Recréer les contraintes CHECK avec NOCHECK

INSERT #ChangeCollationSQLCmds SELECT '-- Step 13 - Create Check Constraints'

INSERT #ChangeCollationSQLCmds SELECT 'IF 1=1 BEGIN'

INSERT #ChangeCollationSQLCmds SELECT 'PRINT ''Create CHECK Constraints'''

INSERT #ChangeCollationSQLCmds SELECT CreateIt FROM #CheckConstraintsCmds

INSERT #ChangeCollationSQLCmds SELECT 'END'

INSERT #ChangeCollationSQLCmds SELECT 'GO'

-- Comments

INSERT #ChangeCollationSQLCmds SELECT '/*'

INSERT #ChangeCollationSQLCmds SELECT '-- Change database collation commands'

INSERT #ChangeCollationSQLCmds SELECT ' USE master '

INSERT #ChangeCollationSQLCmds SELECT ' GO'

INSERT #ChangeCollationSQLCmds SELECT ' ALTER DATABASE xxxxxx SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

INSERT #ChangeCollationSQLCmds SELECT ' ALTER DATABASE xxxxxx COLLATE xxxxxx'

INSERT #ChangeCollationSQLCmds SELECT ' GO'

INSERT #ChangeCollationSQLCmds SELECT '*/'

INSERT #ChangeCollationSQLCmds SELECT '---------------------------------------------'

INSERT #ChangeCollationSQLCmds SELECT '-- End of script ----------------------------'

-- View script

SELECT * FROM #ChangeCollationSQLCmds

-- End of script

-- could be necessary ? refreshsqlmodule

DECLARE @cmd NVARCHAR(MAX) = ''

SELECT @cmd=@cmd + 'EXEC sys.sp_refreshsqlmodule ''['+ SCHEMA_NAME(o.schema_id)+'].['+

OBJECT_NAME(o.object_id) + ']''; '

FROM sys.sql_modules m JOIN sys.objects o

ON m.object_id = o.object_id

WHERE o.type IN ('V','FN','TF','P','TR')

EXEC (@cmd)