Collation change script

Collation change script

Normally when I blog, I spend a lot of time making sure that get as close to perfection as I can. This post is an exception. The problem I am talking about is too far out of my main specialization area that I want to spend more time on it than I already have, yet what I have so far appears to be valuable enough already to warrant posting. Even in it’s current, unfinished and not fully tested state.

The problem

One of my customers called me to look at a problem where they were unable to upgrade a third party application due to some mysterious “collation conflict” error messages during the upgrade process. Those error messages originated from the SQL Server database used as the backend for the third party application. And since I am supposed to be the SQL Server expert, they were all looking at me for a solution.

I won’t bother you with the long process we went through for troubleshooting. Let’s just say that with better service providers, we probably would have found the issue earlier. (In fact, with better service providers it probably would never have happened). So let me jump straight to the root cause.

About two years ago, they had moved the database backend for this application from their old physical hardware to hosted hardware (not true cloud, a virtual cloud from a hosting provider). For weird reasons they had not asked me to set up the SQL Server instance, but instead asked the hosting provider. Who apparently felt confident that just clicking next should be good enough. They installed SQL Server, restored a backup of the old database, and called it a day.

Problem is: the new instance was set up with a default collation that was different from the default collation of the old instance. And hence different from the database default collation. And when that resulted in errors, they responded by changing the default collation. Now everything ran and they were happy.

Fast forward two years. The database now has lots of “old” tables that use the original default collation for all string columns. But also lots of “new” tables that have their string columns using the new default collation. And the upgrade failed because somewhere in the new code is a comparison between two character columns that now have incompatible collation.

The fix

I contacted the vendor to find out what collations are recommended and/or supported. I rebuilt the master database to set the default instance collation to the recommended collation, then switched the database default collation to match it. But I had one problem remaining: thousands of columns defined with the wrong collation.

Finding the columns with incorrect collations is easy enough. Scripting the ALTER TABLE statements to change their collation is also not a really big problem. But to change the collation, one has to first drop all indexes that contain the column, and drop all foreign key constraints that reference the column. That’s where it became challenging.

So what does an IT person do when faced with a challenge? Yes: they turn to Google. I found several sites with various tips and scripts. The one that looked most promising was found at StackOverflow. The script posted by Philip C appeared to do everything I needed, and probably even more.

But we all know the golden rule of internet: never trust anything or anyone blindly. So obviously I did not just run this on production. I restored a copy of the database to a test server, so I could experiment with the script. And boy, was I glad I did. There were various issues with the script. I tweaked a bit here, made a small change there … and then realized there was a basic problem that actually required me to do a quite major overhaul. I still was happy to have found the script because it absolutely gave me a head start, but it did end up taking more time than I had hoped for.

The bugs

The first bug I fixed was not actually a real bug. It was a suboptimal choice. The script generates lots of SQL statements in a temporary table, then at the end sends them all to the SSMS window where you can select the output, copy and paste into a new query window, and then execute. When outputting the statements, it sorts them by statement type to ensure that objects are first dropped, then collations are changed, and then objects are recreated in an order that ensure that objects with dependencies are generated after the objects they depend on.

But I disagree with some of the ordering choices. For instance, when recreating objects the script first creates indexes, then unique constraints, and then primary key constraints. That is not wrong. But in my experience, the primary key is most often the clustered index. And when you build multiple indexes on the same table, it is smart to start with the clustered index – if you first build a nonclustered index, then that index has to be rebuilt when you later add a clustered index.

I did not try to add logic to look at which indexes are actually clustered and nonclustered to get the guaranteed best order. But I did switch the order. My version of the script now first generates primary key constraints, then unique constraints, and then the remaining indexes. So in most cases, this will build the clustered index first. Oh, and I did a similar change (using the reverse logic) in the part where constraints and indexes are dropped.

Another change I made quite quickly was to fix a small sloppiness. When recreating the foreign key constraints that originally were enabled and trusted, the code generated would force SQL Server to check the existing data twice. I removed the extra check.

I also did a small usability change. The script as posted returns two columns of output to SSMS. I removed the column with the sort order so that it outputs just the generated T-SQL statements. This makes it very easy to copy those statements and paste them in a new query window where I can review and then execute them.

After making those tweaks, I started testing the script. And that’s when the actual bugs popped up. I didn’t track all of them. One I remember is that wrong code was generated when a multi-column unique constraint overlaps with a multi-column primary key. After fixing that, I got errors where e.g. the same constraint was generated twice if it was a multi-column constraint (not sure if that’s an original bug or introduced by me when I fixed the first bug).

As I noticed more and more small issues, almost always related to multi-column constraints, I realized that the problem is that the script was originally completely column-based. For each column, it would check indexes, constraints, etc. That results either in duplication within the script (often, but not always, removed by using DISTINCT), or complex code to try to prevent the duplication.

So I decided that the best way to fix everything is to keep the outline of the script, but make the changed needed to do everything per table instead of per column, while still making sure not to do unneeded work (e.g. indexes on non-affected columns are never dropped and rebuilt).

After completing that work, I had a script that did what I needed: change the collation of all affected columns, including the dropping and recreating of objects that would prevent this collation change. I ran it on my test database and then used SQL Compare (I think this action alone already earned back the entire license fee) to verify that the two databases were now exactly the same except for the collations.

The script

Below you will find the final version of the script that I ended up using. There are some important points that I want to point out. Please take heed when you consider using the script:

  • The original version of the script had some weird exceptions that I didn’t really understand, and that were not always consistent. Probably due to the various sources that Philip C used when he assembled the script. I removed most of them.
  • The original version of the script also had logic to drop and recreate manually created statistics objects. I didn’t have any of those in the database I was working on, so to save some time I simply commented out that part of the code and didn’t try to figure out if there were any bugs there. Since I based some of the logic in the loop within the script, this part of the code almost certainly doesn’t work now. So if you need to use this script on a database that does have manually created statistics, you will have to uncomment these parts of the script and then make the necessary changes to get them to work.
  • I have tested this scrip on the database that I needed to fix. This database is quite complex and big, but I have no guarantee that all possible situations are covered. Please, do not ever run this script on important production databases without first testing it on a test copy of that database, and without having a good backup available.
  • The script is currently set to automatically find all columns that have a collation that is different from the database default collation. This is quite easy to change if your requirements are different.
  • The script posted below only generates the code to make the changes; it does not automatically execute it. The generated output is sent to SSMS as a single result set. I recommend setting SSMS to use “results to text” (Ctrl-T), and checking the query options to make sure that the maximum number of character per column is suffienctly large – SSMS defaults this to 256 which is insufficient; I always set this to the maximum allowed value which is 8192.
  • As mentioned above, I can not justify investing more time in this script now. The problem is fixed so I can’t improve it on customer time. And I need my own time to prepare my presentation for SQLBits next month.

The code

So without further ado, here is a script you can use, with the aforementioned precautions, to find all columns that do not have the database default collation and change them to that collation; including the dropping and recreating of objects as needed for this change.

-- SSMS results to text - max length 8192 (change from default!!!)

/*******************************************************************************
*
* Created 2017-06-16 By Philip C
*
* This script will check individual columns collations and check it against the
* database default collation, where they are different it will create the scripts
* required to drop all the objects dependant on the column, change the collation
* to the database default and then recreate the dependant objects.
* Some of the code has been reused from stuff found online the majority from 
* Jayakumaur R who created scripts to drop and recreate constraints
*
*
* Modified 2019-01-11 by Hugo Kornelis
*
* Removed double checking of recreated constraints
* Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
* Fixed a bug caused by unique constraints overlapping the primary key constraint
* Fixed a bug in detection of foreign key constraints to drop and recreate
* Fixed bugs with recreating multi-column UNIQUE constraints
*
*********************************************************************************/

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
GO

-- Declare variables
DECLARE @SchemaName             sysname,
        @TableName              sysname,
        @IndexName              sysname,
        @ColumnName             sysname,
        @index_id               int,
        @is_unique              nvarchar(100),
        @IndexTypeDesc          nvarchar(100),
        @FileGroupName          sysname,
        @is_disabled            nvarchar(100),
        @IndexOptions           nvarchar(MAX),
        @IndexColumnId          int,
        @IsDescendingKey        int,
        @IsIncludedColumn       int,
        @TSQLScripCreationIndex nvarchar(MAX),
        @TSQLScripDisableIndex  nvarchar(MAX),
        @object_id              int,
        @has_key_constraint     int,
        @has_index              int,
        @has_foreign_key        int,
        @has_stats              int,
        @stats_id               int,
        @FK_objectid            int,
        @FK_name                sysname,
        @UQ_objectid            int,
        @UQ_name                sysname,
        @UQ_index_id            int,
        @has_unique_constraint  int,
        @DatabaseCollation      sysname,
        @IndexColumns           nvarchar(MAX),
        @IncludedColumns        nvarchar(MAX);

-- Temporary table for generated script
CREATE TABLE #tempscriptstore
    (ScriptType varchar(20),
     script     nvarchar(MAX));

-- Get dataabse default collation
SELECT  @DatabaseCollation = collation_name
FROM    sys.databases
WHERE   database_id = DB_ID();

/************************************************************************************************************************************
*   Iterate over all the tables that have at least one colmun where collation doesn't match the database default.                   *
*   Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics.       *
*   (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero)             *
************************************************************************************************************************************/
DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
SELECT      t.object_id,
            OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
            OBJECT_NAME(t.object_id)        AS tablename,
            COUNT(kc.object_id)             AS has_key_constraint,
            COUNT(ic.index_id)              AS has_index,
            COUNT(fk.constraint_object_id)  AS has_foreign_key,
            COUNT(st.stats_id)              AS has_stats,
            COUNT(uq.object_id)             AS has_unique_constraint
FROM        sys.tables              AS t
INNER JOIN  sys.columns             AS c
   ON       c.object_id         = t.object_id
   AND      c.collation_name    <> @DatabaseCollation -- Table needs to have columns with "wrong" collation
INNER JOIN  sys.types               AS ty
   ON       ty.system_type_id   = c.system_type_id
   AND      ty.name             <> N'sysname' -- Exclusion retained from Philip C's original script
LEFT JOIN   sys.index_columns       AS ic -- Find indexes on any of the affected columns
  ON        ic.object_id        = c.object_id
  AND       ic.column_id        = c.column_id
LEFT JOIN   sys.key_constraints     AS kc -- Find primary key constraints related to an affected index
  ON        kc.parent_object_id = c.object_id
  AND       kc.unique_index_id  = ic.index_id
  AND       kc.type             = 'PK'
LEFT JOIN   sys.key_constraints     AS uq -- Find unique constraints related to an affected index
  ON        uq.parent_object_id = c.object_id
  AND       uq.unique_index_id  = ic.index_id
  AND       uq.type             = 'UQ'
LEFT JOIN   sys.foreign_key_columns AS fk -- Find foreign key constraints on any of the affected columns
  ON        fk.parent_object_id = c.object_id
  AND       fk.parent_column_id = c.column_id
LEFT JOIN   sys.stats_columns       AS st -- Find statistics on any of the affected columns
  ON        st.object_id        = c.object_id
  AND       st.column_id        = c.column_id
  AND       st.stats_column_id  <> 1 -- Retained from Philip C's original script, no idea why this is in the query
WHERE       t.is_ms_shipped = 0 -- Exclude Microsoft-shipped tables
GROUP BY    t.object_id;

OPEN collationfix;
FETCH NEXT FROM collationfix
INTO @object_id,
     @SchemaName,
     @TableName,
     @has_key_constraint,
     @has_index,
     @has_foreign_key,
     @has_stats,
     @has_unique_constraint;

WHILE (@@FETCH_STATUS = 0)
BEGIN;

    /************************************************************************************************************************************
    *   Generates the code to update the colation for all affected columns in the table                                                 *
    ************************************************************************************************************************************/
    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'AlterCollation',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                + QUOTENAME(c.name) + ' '
                + CASE WHEN ty.name = N'ntext'
                           THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                       ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                      THEN N'MAX'
                                                  ELSE CASE WHEN ty.name = N'nvarchar'
                                                                THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                            ELSE CAST(c.max_length AS nvarchar(20))
                                                       END
                                             END + N') COLLATE ' + @DatabaseCollation
                  END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
    FROM        sys.columns AS c
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation;

    /************************************************************************************************************************************
    *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
    ************************************************************************************************************************************/
    IF @has_index > 0
    BEGIN;
        -- Process indexes on affected columns, one by one
        DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
        SELECT  ix.index_id,
                ix.name,
                CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                ix.type_desc,
                CASE WHEN ix.is_padded = 1
                         THEN N'PAD_INDEX = ON, '
                     ELSE N'PAD_INDEX = OFF, '
                END + CASE WHEN ix.allow_page_locks = 1
                               THEN N'ALLOW_PAGE_LOCKS = ON, '
                           ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                      END + CASE WHEN ix.allow_row_locks = 1
                                     THEN N'ALLOW_ROW_LOCKS = ON, '
                                 ELSE N'ALLOW_ROW_LOCKS = OFF, '
                            END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                           THEN N'STATISTICS_NORECOMPUTE = ON, '
                                       ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                  END + CASE WHEN ix.ignore_dup_key = 1
                                                 THEN N'IGNORE_DUP_KEY = ON, '
                                             ELSE N'IGNORE_DUP_KEY = OFF, '
                                        END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                + CASE WHEN ix.fill_factor = 0
                           THEN CAST(100 AS nvarchar(3))
                       ELSE CAST(ix.fill_factor AS nvarchar(3))
                  END                            AS IndexOptions,
                ix.is_disabled,
                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
        FROM    sys.indexes AS ix
        WHERE   ix.object_id        = @object_id
        AND     ix.type                 <> 0 -- Exclude heaps
        AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
        AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.index_id = ix.index_id
                        AND         ic.object_id        = ix.object_id);

        OPEN CursorIndex;
        FETCH NEXT FROM CursorIndex
        INTO @index_id,
             @IndexName,
             @is_unique,
             @IndexTypeDesc,
             @IndexOptions,
             @is_disabled,
             @FileGroupName;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- For each index, use a nested cursor to build lists of indexed and inclued columns
            SET @IndexColumns = N'';
            SET @IncludedColumns = N'';

            DECLARE CursorIndexColumn CURSOR FOR
            SELECT      c.name,
                        ic.is_descending_key,
                        ic.is_included_column
            FROM        sys.index_columns AS ic
            INNER JOIN  sys.columns       AS c
               ON       c.object_id = ic.object_id
               AND      c.column_id = ic.column_id
            WHERE       ic.object_id = @object_id
            AND         ic.index_id          = @index_id
            ORDER BY    ic.index_column_id;

            OPEN CursorIndexColumn;
            FETCH NEXT FROM CursorIndexColumn
            INTO @ColumnName,
                 @IsDescendingKey,
                 @IsIncludedColumn;

            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                IF @IsIncludedColumn = 0
                    SET @IndexColumns += @ColumnName
                                         + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                ELSE
                    SET @IncludedColumns += @ColumnName + N', ';

                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
            END;

            CLOSE CursorIndexColumn;
            DEALLOCATE CursorIndexColumn;

            -- Remove trailing comma
            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
            IF @IncludedColumns <> N''
                SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);

            -- Generate DROP INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropIndex',
                    N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                    + N';';

            -- Generate CREATE INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'CreateIndex',
                    N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                    + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                    + CASE WHEN @IncludedColumns <> N''
                               THEN N'INCLUDE (' + @IncludedColumns + N')'
                           ELSE N''
                      END + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';

            -- Generate script to (re)disable disabled index after creating it
            IF @is_disabled = 1
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DisableIndex',
                        N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                        + QUOTENAME(@TableName) + N' DISABLE;';

            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
        END;

        CLOSE CursorIndex;
        DEALLOCATE CursorIndex;
    END;

    /************************************************************************************************************************************
    *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
    *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_key_constraint > 0
    BEGIN;
        -- Find columns and other metadata for primary key
        SELECT      kc.object_id                                                                          AS constid,
                    kc.name                                                                               AS constraint_name,   -- PK name
                    QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                    ic.key_ordinal,
                    i.name                                                                                AS index_name,
                    i.type_desc                                                                           AS index_type,
                    QUOTENAME(fg.name)                                                                    AS filegroup_name,
                    N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                    + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                    + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                    + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
        INTO        #temp_pk
        FROM        sys.key_constraints AS kc
        INNER JOIN  sys.indexes         AS i
           ON       i.object_id      = kc.parent_object_id
           AND      i.is_primary_key = 1
        INNER JOIN  sys.index_columns   AS ic
           ON       ic.object_id     = i.object_id
           AND      ic.index_id      = i.index_id
        INNER JOIN  sys.columns         AS c
           ON       c.object_id      = ic.object_id
           AND      c.column_id      = ic.column_id
        INNER JOIN  sys.filegroups      AS fg
           ON       fg.data_space_id = i.data_space_id
        WHERE       kc.type     = 'PK'
        AND         kc.parent_object_id = @object_id;

        -- Generate DROP CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                'DropPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                + QUOTENAME(constraint_name) + N';'
        FROM    #temp_pk;

        -- Generate CREATE CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                'AddPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                         FROM       #temp_pk
                         ORDER BY   key_ordinal
                        FOR XML PATH('')),
                        1,
                        2,
                        N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
        FROM    #temp_pk AS p;

        DROP TABLE #temp_pk;
    END;

    /************************************************************************************************************************************
    *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
    *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_foreign_key > 0
    BEGIN;
        -- Process foreign key constraints on affected columns, one by one
        DECLARE foreignkeycursor CURSOR FOR
        SELECT  fk.object_id,
                fk.name
        FROM    sys.foreign_keys AS fk
        WHERE   fk.parent_object_id = @object_id
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.foreign_key_columns AS fkc
                        INNER JOIN  sys.columns             AS c
                           ON       c.object_id       = fkc.parent_object_id
                           AND      c.column_id       = fkc.parent_column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types               AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       fkc.parent_object_id = fk.parent_object_id
                        AND         fkc.constraint_object_id     = fk.object_id);

        OPEN foreignkeycursor;
        FETCH NEXT FROM foreignkeycursor
        INTO @FK_objectid,
             @FK_name;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for foreign key
            SELECT      fk.object_id                                      AS constid,
                        fk.name                                           AS constraint_name,   -- FK name
                        fkc.constraint_column_id                          AS column_ordinal,
                        QUOTENAME(c1.name)                                AS key_col,
                        QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                        + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                        QUOTENAME(c2.name)                                AS ref_col,
                        CASE WHEN fk.delete_referential_action <> 0 -- No action
                                 THEN N' ON DELETE ' + fk.delete_referential_action_desc
                             ELSE N''
                        END                                               AS delete_cascade,
                        CASE WHEN fk.update_referential_action <> 0 -- No action
                                 THEN N' ON UPDATE ' + fk.update_referential_action_desc
                             ELSE N''
                        END                                               AS update_cascade,
                        fk.is_not_trusted,
                        fk.is_disabled
            INTO        #temp_fk
            FROM        sys.foreign_keys        AS fk
            INNER JOIN  sys.foreign_key_columns AS fkc
               ON       fkc.parent_object_id     = fk.parent_object_id
               AND      fkc.constraint_object_id = fk.object_id
            INNER JOIN  sys.columns             AS c1
               ON       c1.object_id             = fkc.parent_object_id
               AND      c1.column_id             = fkc.parent_column_id
            INNER JOIN  sys.columns             AS c2
               ON       c2.object_id             = fkc.referenced_object_id
               AND      c2.column_id             = fkc.referenced_column_id
            WHERE       fk.object_id = @FK_objectid;

            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@FK_name) + N';';

            -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                    + STUFF(
                      (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N') REFERENCES ' + ref_table + N'('
                    + STUFF(
                      (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N')' + delete_cascade + update_cascade + ';'
            FROM    #temp_fk;

            -- If constraint was disabled, add code to disable it again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'DisableForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_disabled = 1;

            -- If constraint was trusted, add code to force re-check so it's trudsted again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'CheckForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_not_trusted = 0;

            DROP TABLE #temp_fk;

            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
        END;
        CLOSE foreignkeycursor;
        DEALLOCATE foreignkeycursor;
    END;

    /*
-- DROP and CREATE statistics for manual statistics has been disabled for now
    /************************************************************************************************************************************
    *   If the column has statistics that aren't part of an index this creates the drop and recreate scripts                            *
    ************************************************************************************************************************************/
    IF  @has_stats > 0
    AND @has_index = 0
    BEGIN
        DECLARE stats_cursor CURSOR FOR
            SELECT  sc.stats_id
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.object_id    = sc.object_id
              AND   s.stats_id     = sc.stats_id
              AND   s.user_created = 1
            WHERE   sc.object_id = @object_id
            AND     sc.column_id     = @Collation_columnid;
        OPEN stats_cursor;
        FETCH NEXT FROM stats_cursor
        INTO @stats_id;
        WHILE (@@FETCH_STATUS = 0)
        BEGIN
            --Create DROP Statistics Statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'DropStatistics',
                    'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.'
                    + QUOTENAME(OBJECT_NAME(s.object_id)) + '.' + QUOTENAME(s.name)
            FROM    sys.stats AS s
            WHERE   s.object_id = @object_id
            AND     s.stats_id      = @stats_id;

            --Building the CREATE statistics statement

            --Obtaining all the information
            SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                    QUOTENAME(s.name)                                                                        AS st_name,
                    QUOTENAME(c.name)                                                                        AS st_column,
                    sc.object_id,
                    sc.stats_id,
                    sc.stats_column_id
            INTO    #temp_stats
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.stats_id  = sc.stats_id
              AND   s.object_id = sc.object_id
            JOIN    sys.columns       AS c
              ON    c.object_id = sc.object_id
              AND   c.column_id = sc.column_id
            WHERE   sc.object_id = @object_id
            AND     sc.stats_id      = @stats_id;
            WITH
            cte AS
                (SELECT DISTINCT ts.st_table,
                                 ts.st_name,
                                 SUBSTRING((SELECT      ',' + st_column
                                            FROM        #temp_stats
                                            WHERE       stats_id = ts.stats_id
                                            ORDER BY    stats_column_id ASC
                                           FOR XML PATH('')),
                                           2,
                                           99999) AS st_col_list
                 FROM   #temp_stats AS ts)
            --Constructing the statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'AddStatistics',
                    'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
            FROM    cte;
            DROP TABLE #temp_stats;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
        END;
        CLOSE stats_cursor;
        DEALLOCATE stats_cursor;
    END;
*/

    /************************************************************************************************************************************
    *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
    ************************************************************************************************************************************/
    IF @has_unique_constraint > 0
    BEGIN;
        -- Process unique constraints on affected columns, one by one
        DECLARE uniquecursor CURSOR FOR
        SELECT  kc.object_id,
                kc.name,
                kc.unique_index_id
        FROM    sys.key_constraints AS kc
        WHERE   kc.parent_object_id = @object_id
        AND     kc.type                 = 'UQ'
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.object_id = kc.parent_object_id
                        AND         ic.index_id          = kc.unique_index_id);

        OPEN uniquecursor;
        FETCH NEXT FROM uniquecursor
        INTO @UQ_objectid,
             @UQ_name,
             @UQ_index_id;

        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for unique constraint
            SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                        ic.key_ordinal,
                        i.name                                                                                    AS index_name,
                        i.type_desc                                                                               AS index_type,
                        QUOTENAME(fg.name)                                                                        AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                        + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                        + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                        + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
            INTO        #temp_uq
            FROM        sys.indexes       AS i
            INNER JOIN  sys.index_columns AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns       AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups    AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       i.object_id    = @object_id
            AND         i.index_id             = @UQ_index_id
            AND         i.is_unique_constraint = 1;

            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N';';

            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_uq
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
            FROM    #temp_uq;

            DROP TABLE #temp_uq;

            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
        END;
        CLOSE uniquecursor;
        DEALLOCATE uniquecursor;
    END;

    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint;
END;
CLOSE collationfix;
DEALLOCATE collationfix;

/************************************************************************************************************************************
*   Returns all the created scripts in the correct order for running                                                                *
*   Also add GO in between some sections so the entire script can execute at once                                                   *
************************************************************************************************************************************/
WITH ScriptInput
  AS (SELECT    script,
                CASE WHEN ScriptType = 'DropForeignKey'
                         THEN 1
                     WHEN ScriptType = 'DropIndex'
                         THEN 2
                     WHEN ScriptType = 'DropUniqueKey'
                         THEN 3
                     WHEN ScriptType = 'DropPrimaryKey'
                         THEN 4
                     WHEN ScriptType = 'DropStatistics'
                         THEN 5
                     WHEN ScriptType = 'AlterCollation'
                         THEN 7
                     WHEN ScriptType = 'AddPrimaryKey'
                         THEN 9
                     WHEN ScriptType = 'AddUniqueKey'
                         THEN 10
                     WHEN ScriptType = 'CreateIndex'
                         THEN 11
                     WHEN ScriptType = 'AddStatistics'
                         THEN 12
                     WHEN ScriptType = 'AddForeignKey'
                         THEN 13
                     WHEN ScriptType = 'DisableForeignKey'
                         THEN 15
                     WHEN ScriptType = 'CheckForeignKey'
                         THEN 16
                     WHEN ScriptType = 'DisableIndex'
                         THEN 17
                     ELSE 99
                END AS SortOrder
      FROM      #tempscriptstore
      UNION ALL
      SELECT    'GO',
                n.num
      FROM      (VALUES (6),    -- After dropping objects, before changing collations
                        (8),    -- After chaning collations, before re-creating objects
                        (14),   -- After creating objects, before checking and disabling foreign keys and indexes
                        (18)    -- End of script
      ) AS n (num) )
SELECT      ScriptInput.script
FROM        ScriptInput
ORDER BY    ScriptInput.SortOrder;

DROP TABLE #tempscriptstore;
Off Topic: Charity and snooker
SQL injection

Related Posts

No results found.

27 Comments. Leave new

  • Stefan Visagie
    July 3, 2019 15:04

    This is a great piece of work! Saved us trying to migrate databases into Azure SQL. Thank you very much.

    Reply
  • Great script! I’m adding it to the repertoire 🙂 I’m going to look at the statistics stuff to see if any changes needed there for my purposes.

    Reply
  • Walter Charrière
    August 12, 2019 18:26

    Hi

    I like to share a minor update, added drop and create column defaults alos required when changing collation (also put more GO separators in cases of large scripts and comment lines of what comes next after each GO.

    — SSMS results to text – max length 8192 (change from default!!!)

    /*******************************************************************************
    *
    * Created 2017-06-16 By Philip C
    *
    * This script will check individual columns collations and check it against the
    * database default collation, where they are different it will create the scripts
    * required to drop all the objects dependant on the column, change the collation
    * to the database default and then recreate the dependant objects.
    * Some of the code has been reused from stuff found online the majority from
    * Jayakumaur R who created scripts to drop and recreate constraints
    *
    *
    * Modified 2019-01-11 by Hugo Kornelis
    *
    * Removed double checking of recreated constraints
    * Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
    * Fixed a bug caused by unique constraints overlapping the primary key constraint
    * Fixed a bug in detection of foreign key constraints to drop and recreate
    * Fixed bugs with recreating multi-column UNIQUE constraints
    *
    * Modified 2019-08-12 by Walter Charrière
    *
    * Add drop and create default constraints
    *
    *********************************************************************************/

    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    GO

    — Declare variables
    DECLARE @SchemaName sysname,
    @TableName sysname,
    @IndexName sysname,
    @ColumnName sysname,
    @index_id int,
    @is_unique nvarchar(100),
    @IndexTypeDesc nvarchar(100),
    @FileGroupName sysname,
    @is_disabled nvarchar(100),
    @IndexOptions nvarchar(MAX),
    @IndexColumnId int,
    @IsDescendingKey int,
    @IsIncludedColumn int,
    @TSQLScripCreationIndex nvarchar(MAX),
    @TSQLScripDisableIndex nvarchar(MAX),
    @object_id int,
    @has_key_constraint int,
    @has_index int,
    @has_foreign_key int,
    @has_stats int,
    @stats_id int,
    @FK_objectid int,
    @FK_name sysname,
    @UQ_objectid int,
    @UQ_name sysname,
    @UQ_index_id int,
    @has_unique_constraint int,
    @has_default_constraint int,
    @DatabaseCollation sysname,
    @IndexColumns nvarchar(MAX),
    @IncludedColumns nvarchar(MAX);

    — Temporary table for generated script
    CREATE TABLE #tempscriptstore
    (ScriptType varchar(20),
    script nvarchar(MAX));

    — Get dataabse default collation
    SELECT @DatabaseCollation = collation_name
    FROM sys.databases
    WHERE database_id = DB_ID();

    /************************************************************************************************************************************
    * Iterate over all the tables that have at least one colmun where collation doesn’t match the database default. *
    * Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics. *
    * (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero) *
    ************************************************************************************************************************************/
    DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
    SELECT t.object_id,
    OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
    OBJECT_NAME(t.object_id) AS tablename,
    COUNT(kc.object_id) AS has_key_constraint,
    COUNT(ic.index_id) AS has_index,
    COUNT(fk.constraint_object_id) AS has_foreign_key,
    COUNT(st.stats_id) AS has_stats,
    COUNT(uq.object_id) AS has_unique_constraint,
    COUNT(dc.object_id) AS has_default_constraint
    FROM sys.tables AS t
    INNER JOIN sys.columns AS c
    ON c.object_id = t.object_id
    AND c.collation_name <> @DatabaseCollation — Table needs to have columns with “wrong” collation
    INNER JOIN sys.types AS ty
    ON ty.system_type_id = c.system_type_id
    AND ty.name <> N’sysname’ — Exclusion retained from Philip C’s original script
    LEFT JOIN sys.index_columns AS ic — Find indexes on any of the affected columns
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
    LEFT JOIN sys.key_constraints AS kc — Find primary key constraints related to an affected index
    ON kc.parent_object_id = c.object_id
    AND kc.unique_index_id = ic.index_id
    AND kc.type = ‘PK’
    LEFT JOIN sys.key_constraints AS uq — Find unique constraints related to an affected index
    ON uq.parent_object_id = c.object_id
    AND uq.unique_index_id = ic.index_id
    AND uq.type = ‘UQ’
    LEFT JOIN sys.foreign_key_columns AS fk — Find foreign key constraints on any of the affected columns
    ON fk.parent_object_id = c.object_id
    AND fk.parent_column_id = c.column_id
    LEFT JOIN sys.stats_columns AS st — Find statistics on any of the affected columns
    ON st.object_id = c.object_id
    AND st.column_id = c.column_id
    AND st.stats_column_id <> 1 — Retained from Philip C’s original script, no idea why this is in the query
    LEFT JOIN sys.default_constraints AS dc — Find default constraints on any of the affected columns
    ON dc.object_id = c.default_object_id
    AND dc.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0 — Exclude Microsoft-shipped tables
    GROUP BY t.object_id;

    OPEN collationfix;
    FETCH NEXT FROM collationfix
    INTO @object_id,
    @SchemaName,
    @TableName,
    @has_key_constraint,
    @has_index,
    @has_foreign_key,
    @has_stats,
    @has_unique_constraint,
    @has_default_constraint;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN;

    /************************************************************************************************************************************
    *   Generates the code to update the colation for all affected columns in the table                                                 *
    ************************************************************************************************************************************/
    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'AlterCollation',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                + QUOTENAME(c.name) + ' '
                + CASE WHEN ty.name = N'ntext'
                           THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                       ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                      THEN N'MAX'
                                                  ELSE CASE WHEN ty.name = N'nvarchar'
                                                                THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                            ELSE CAST(c.max_length AS nvarchar(20))
                                                       END
                                             END + N') COLLATE ' + @DatabaseCollation
                  END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
    FROM        sys.columns AS c
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation;
    
    /************************************************************************************************************************************
    *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
    ************************************************************************************************************************************/
    IF @has_index > 0
    BEGIN;
        -- Process indexes on affected columns, one by one
        DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
        SELECT  ix.index_id,
                ix.name,
                CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                ix.type_desc,
                CASE WHEN ix.is_padded = 1
                         THEN N'PAD_INDEX = ON, '
                     ELSE N'PAD_INDEX = OFF, '
                END + CASE WHEN ix.allow_page_locks = 1
                               THEN N'ALLOW_PAGE_LOCKS = ON, '
                           ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                      END + CASE WHEN ix.allow_row_locks = 1
                                     THEN N'ALLOW_ROW_LOCKS = ON, '
                                 ELSE N'ALLOW_ROW_LOCKS = OFF, '
                            END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                           THEN N'STATISTICS_NORECOMPUTE = ON, '
                                       ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                  END + CASE WHEN ix.ignore_dup_key = 1
                                                 THEN N'IGNORE_DUP_KEY = ON, '
                                             ELSE N'IGNORE_DUP_KEY = OFF, '
                                        END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                + CASE WHEN ix.fill_factor = 0
                           THEN CAST(100 AS nvarchar(3))
                       ELSE CAST(ix.fill_factor AS nvarchar(3))
                  END                            AS IndexOptions,
                ix.is_disabled,
                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
        FROM    sys.indexes AS ix
        WHERE   ix.object_id        = @object_id
        AND     ix.type                 <> 0 -- Exclude heaps
        AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
        AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.index_id = ix.index_id
                        AND         ic.object_id        = ix.object_id);
    
        OPEN CursorIndex;
        FETCH NEXT FROM CursorIndex
        INTO @index_id,
             @IndexName,
             @is_unique,
             @IndexTypeDesc,
             @IndexOptions,
             @is_disabled,
             @FileGroupName;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- For each index, use a nested cursor to build lists of indexed and inclued columns
            SET @IndexColumns = N'';
            SET @IncludedColumns = N'';
    
            DECLARE CursorIndexColumn CURSOR FOR
            SELECT      c.name,
                        ic.is_descending_key,
                        ic.is_included_column
            FROM        sys.index_columns AS ic
            INNER JOIN  sys.columns       AS c
               ON       c.object_id = ic.object_id
               AND      c.column_id = ic.column_id
            WHERE       ic.object_id = @object_id
            AND         ic.index_id          = @index_id
            ORDER BY    ic.index_column_id;
    
            OPEN CursorIndexColumn;
            FETCH NEXT FROM CursorIndexColumn
            INTO @ColumnName,
                 @IsDescendingKey,
                 @IsIncludedColumn;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                IF @IsIncludedColumn = 0
                    SET @IndexColumns += @ColumnName
                                         + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                ELSE
                    SET @IncludedColumns += @ColumnName + N', ';
    
                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
            END;
    
            CLOSE CursorIndexColumn;
            DEALLOCATE CursorIndexColumn;
    
            -- Remove trailing comma
            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
            IF @IncludedColumns <> N''
                SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
    
            -- Generate DROP INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropIndex',
                    N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                    + N';';
    
            -- Generate CREATE INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'CreateIndex',
                    N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                    + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                    + CASE WHEN @IncludedColumns <> N''
                               THEN N'INCLUDE (' + @IncludedColumns + N')'
                           ELSE N''
                      END + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
    
            -- Generate script to (re)disable disabled index after creating it
            IF @is_disabled = 1
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DisableIndex',
                        N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                        + QUOTENAME(@TableName) + N' DISABLE;';
    
            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
        END;
    
        CLOSE CursorIndex;
        DEALLOCATE CursorIndex;
    END;
    
    /************************************************************************************************************************************
    *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
    *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_key_constraint > 0
    BEGIN;
        -- Find columns and other metadata for primary key
        SELECT      kc.object_id                                                                          AS constid,
                    kc.name                                                                               AS constraint_name,   -- PK name
                    QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                    ic.key_ordinal,
                    i.name                                                                                AS index_name,
                    i.type_desc                                                                           AS index_type,
                    QUOTENAME(fg.name)                                                                    AS filegroup_name,
                    N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                    + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                    + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                    + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
        INTO        #temp_pk
        FROM        sys.key_constraints AS kc
        INNER JOIN  sys.indexes         AS i
           ON       i.object_id      = kc.parent_object_id
           AND      i.is_primary_key = 1
        INNER JOIN  sys.index_columns   AS ic
           ON       ic.object_id     = i.object_id
           AND      ic.index_id      = i.index_id
        INNER JOIN  sys.columns         AS c
           ON       c.object_id      = ic.object_id
           AND      c.column_id      = ic.column_id
        INNER JOIN  sys.filegroups      AS fg
           ON       fg.data_space_id = i.data_space_id
        WHERE       kc.type     = 'PK'
        AND         kc.parent_object_id = @object_id;
    
        -- Generate DROP CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                'DropPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                + QUOTENAME(constraint_name) + N';'
        FROM    #temp_pk;
    
        -- Generate CREATE CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                'AddPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                         FROM       #temp_pk
                         ORDER BY   key_ordinal
                        FOR XML PATH('')),
                        1,
                        2,
                        N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
        FROM    #temp_pk AS p;
    
        DROP TABLE #temp_pk;
    END;
    
    /************************************************************************************************************************************
    *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
    *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_foreign_key > 0
    BEGIN;
        -- Process foreign key constraints on affected columns, one by one
        DECLARE foreignkeycursor CURSOR FOR
        SELECT  fk.object_id,
                fk.name
        FROM    sys.foreign_keys AS fk
        WHERE   fk.parent_object_id = @object_id
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.foreign_key_columns AS fkc
                        INNER JOIN  sys.columns             AS c
                           ON       c.object_id       = fkc.parent_object_id
                           AND      c.column_id       = fkc.parent_column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types               AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       fkc.parent_object_id = fk.parent_object_id
                        AND         fkc.constraint_object_id     = fk.object_id);
    
        OPEN foreignkeycursor;
        FETCH NEXT FROM foreignkeycursor
        INTO @FK_objectid,
             @FK_name;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for foreign key
            SELECT      fk.object_id                                      AS constid,
                        fk.name                                           AS constraint_name,   -- FK name
                        fkc.constraint_column_id                          AS column_ordinal,
                        QUOTENAME(c1.name)                                AS key_col,
                        QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                        + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                        QUOTENAME(c2.name)                                AS ref_col,
                        CASE WHEN fk.delete_referential_action <> 0 -- No action
                                 THEN N' ON DELETE ' + fk.delete_referential_action_desc
                             ELSE N''
                        END                                               AS delete_cascade,
                        CASE WHEN fk.update_referential_action <> 0 -- No action
                                 THEN N' ON UPDATE ' + fk.update_referential_action_desc
                             ELSE N''
                        END                                               AS update_cascade,
                        fk.is_not_trusted,
                        fk.is_disabled
            INTO        #temp_fk
            FROM        sys.foreign_keys        AS fk
            INNER JOIN  sys.foreign_key_columns AS fkc
               ON       fkc.parent_object_id     = fk.parent_object_id
               AND      fkc.constraint_object_id = fk.object_id
            INNER JOIN  sys.columns             AS c1
               ON       c1.object_id             = fkc.parent_object_id
               AND      c1.column_id             = fkc.parent_column_id
            INNER JOIN  sys.columns             AS c2
               ON       c2.object_id             = fkc.referenced_object_id
               AND      c2.column_id             = fkc.referenced_column_id
            WHERE       fk.object_id = @FK_objectid;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@FK_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                    + STUFF(
                      (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N') REFERENCES ' + ref_table + N'('
                    + STUFF(
                      (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N')' + delete_cascade + update_cascade + ';'
            FROM    #temp_fk;
    
            -- If constraint was disabled, add code to disable it again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'DisableForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_disabled = 1;
    
            -- If constraint was trusted, add code to force re-check so it's trudsted again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'CheckForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_not_trusted = 0;
    
            DROP TABLE #temp_fk;
    
            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
        END;
        CLOSE foreignkeycursor;
        DEALLOCATE foreignkeycursor;
    END;
    
    /*
    

    — DROP and CREATE statistics for manual statistics has been disabled for now
    /************************************************************************************************************************************
    * If the column has statistics that aren’t part of an index this creates the drop and recreate scripts *
    ************************************************************************************************************************************/
    IF @has_stats > 0
    AND @has_index = 0
    BEGIN
    DECLARE stats_cursor CURSOR FOR
    SELECT sc.stats_id
    FROM sys.stats_columns AS sc
    JOIN sys.stats AS s
    ON s.object_id = sc.object_id
    AND s.stats_id = sc.stats_id
    AND s.user_created = 1
    WHERE sc.object_id = @object_id
    AND sc.column_id = @Collation_columnid;
    OPEN stats_cursor;
    FETCH NEXT FROM stats_cursor
    INTO @stats_id;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    –Create DROP Statistics Statement
    INSERT INTO #tempscriptstore
    (ScriptType,
    script)
    SELECT ‘DropStatistics’,
    ‘DROP STATISTICS ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + ‘.’
    + QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ + QUOTENAME(s.name)
    FROM sys.stats AS s
    WHERE s.object_id = @object_id
    AND s.stats_id = @stats_id;

            --Building the CREATE statistics statement
    
            --Obtaining all the information
            SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                    QUOTENAME(s.name)                                                                        AS st_name,
                    QUOTENAME(c.name)                                                                        AS st_column,
                    sc.object_id,
                    sc.stats_id,
                    sc.stats_column_id
            INTO    #temp_stats
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.stats_id  = sc.stats_id
              AND   s.object_id = sc.object_id
            JOIN    sys.columns       AS c
              ON    c.object_id = sc.object_id
              AND   c.column_id = sc.column_id
            WHERE   sc.object_id = @object_id
            AND     sc.stats_id      = @stats_id;
            WITH
            cte AS
                (SELECT DISTINCT ts.st_table,
                                 ts.st_name,
                                 SUBSTRING((SELECT      ',' + st_column
                                            FROM        #temp_stats
                                            WHERE       stats_id = ts.stats_id
                                            ORDER BY    stats_column_id ASC
                                           FOR XML PATH('')),
                                           2,
                                           99999) AS st_col_list
                 FROM   #temp_stats AS ts)
            --Constructing the statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'AddStatistics',
                    'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
            FROM    cte;
            DROP TABLE #temp_stats;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
        END;
        CLOSE stats_cursor;
        DEALLOCATE stats_cursor;
    END;
    

    */

    /************************************************************************************************************************************
    *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
    ************************************************************************************************************************************/
    IF @has_unique_constraint > 0
    BEGIN;
        -- Process unique constraints on affected columns, one by one
        DECLARE uniquecursor CURSOR FOR
        SELECT  kc.object_id,
                kc.name,
                kc.unique_index_id
        FROM    sys.key_constraints AS kc
        WHERE   kc.parent_object_id = @object_id
        AND     kc.type                 = 'UQ'
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.object_id = kc.parent_object_id
                        AND         ic.index_id          = kc.unique_index_id);
    
        OPEN uniquecursor;
        FETCH NEXT FROM uniquecursor
        INTO @UQ_objectid,
             @UQ_name,
             @UQ_index_id;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for unique constraint
            SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                        ic.key_ordinal,
                        i.name                                                                                    AS index_name,
                        i.type_desc                                                                               AS index_type,
                        QUOTENAME(fg.name)                                                                        AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                        + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                        + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                        + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
            INTO        #temp_uq
            FROM        sys.indexes       AS i
            INNER JOIN  sys.index_columns AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns       AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups    AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       i.object_id    = @object_id
            AND         i.index_id             = @UQ_index_id
            AND         i.is_unique_constraint = 1;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_uq
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
            FROM    #temp_uq;
    
            DROP TABLE #temp_uq;
    
            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
        END;
        CLOSE uniquecursor;
        DEALLOCATE uniquecursor;
    END;
    
    
    /************************************************************************************************************************************
    *  If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table                *
    ************************************************************************************************************************************/
    IF @has_default_constraint > 0
    BEGIN
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'DropDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(dc.name) + ' '
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc 
           ON       dc.object_id = c.default_object_id 
           AND      dc.parent_column_id = c.column_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    
    
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'CreateDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
                    + QUOTENAME(dc.name) + ' ''' + dc.definition + ''''
                    + ' FOR ' + QUOTENAME(c.name) 
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc on dc.object_id = c.default_object_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    End
    
    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint,
         @has_default_constraint;
    

    END;
    CLOSE collationfix;
    DEALLOCATE collationfix;

    /************************************************************************************************************************************
    * Returns all the created scripts in the correct order for running *
    * Also add GO in between some sections so the entire script can execute at once *
    ************************************************************************************************************************************/
    DECLARE @oddnum INT = 1;
    WITH ScriptInput
    AS (SELECT script,
    CASE WHEN ScriptType = ‘DropDefault’
    THEN 1
    WHEN ScriptType = ‘DropForeignKey’
    THEN 3
    WHEN ScriptType = ‘DropIndex’
    THEN 5
    WHEN ScriptType = ‘DropUniqueKey’
    THEN 7
    WHEN ScriptType = ‘DropPrimaryKey’
    THEN 9
    WHEN ScriptType = ‘DropStatistics’
    THEN 11
    WHEN ScriptType = ‘AlterCollation’
    THEN 13
    WHEN ScriptType = ‘AddPrimaryKey’
    THEN 15
    WHEN ScriptType = ‘AddUniqueKey’
    THEN 17
    WHEN ScriptType = ‘CreateIndex’
    THEN 19
    WHEN ScriptType = ‘AddStatistics’
    THEN 21
    WHEN ScriptType = ‘AddForeignKey’
    THEN 23
    WHEN ScriptType = ‘DisableForeignKey’
    THEN 25
    WHEN ScriptType = ‘CheckForeignKey’
    THEN 27
    WHEN ScriptType = ‘DisableIndex’
    THEN 29
    WHEN ScriptType = ‘CreateDefault’
    THEN 31
    ELSE 99
    END AS SortOrder
    FROM #tempscriptstore
    UNION ALL
    SELECT case when n.num > 0 then ‘GO — ‘ + descrip else descrip end,
    n.num
    FROM (VALUES (0, ‘Drop Default’),
    (2, ‘Drop Foreign Key’),
    (4, ‘Drop Index’),
    (6, ‘Drop Unique Key’),
    (8, ‘Drop Primary Key’),
    –(10, ‘Drop Statistics’),
    (12, ‘Alter Collation ‘),
    (14, ‘Add Primary Key ‘),
    (16, ‘Add Unique Key ‘),
    (18, ‘Create Index ‘),
    –(20, ‘Add Statistics ‘),
    (22, ‘Add Foreign Key ‘),
    (24, ‘Disable Foreign Key ‘),
    (26, ‘Check Foreign Key ‘),
    (28, ‘Disable Index ‘),
    (30, ‘Create Default ‘),
    (32, ‘End of script’)
    ) AS n (num, descrip) )
    SELECT ScriptInput.script
    FROM ScriptInput
    ORDER BY ScriptInput.SortOrder;

    DROP TABLE #tempscriptstore;

    Reply
  • Walter Charrière
    August 12, 2019 18:31

    Ups!! Sorry, hope this get the code correctly:
    Begin
    — SSMS results to text – max length 8192 (change from default!!!)

    /*******************************************************************************
    *
    * Created 2017-06-16 By Philip C
    *
    * This script will check individual columns collations and check it against the
    * database default collation, where they are different it will create the scripts
    * required to drop all the objects dependant on the column, change the collation
    * to the database default and then recreate the dependant objects.
    * Some of the code has been reused from stuff found online the majority from
    * Jayakumaur R who created scripts to drop and recreate constraints
    *
    *
    * Modified 2019-01-11 by Hugo Kornelis
    *
    * Removed double checking of recreated constraints
    * Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
    * Fixed a bug caused by unique constraints overlapping the primary key constraint
    * Fixed a bug in detection of foreign key constraints to drop and recreate
    * Fixed bugs with recreating multi-column UNIQUE constraints
    *
    * Modified 2019-08-12 by Walter Charrière
    *
    * Add drop and create default constraints
    *
    *********************************************************************************/

    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    GO

    — Declare variables
    DECLARE @SchemaName sysname,
    @TableName sysname,
    @IndexName sysname,
    @ColumnName sysname,
    @index_id int,
    @is_unique nvarchar(100),
    @IndexTypeDesc nvarchar(100),
    @FileGroupName sysname,
    @is_disabled nvarchar(100),
    @IndexOptions nvarchar(MAX),
    @IndexColumnId int,
    @IsDescendingKey int,
    @IsIncludedColumn int,
    @TSQLScripCreationIndex nvarchar(MAX),
    @TSQLScripDisableIndex nvarchar(MAX),
    @object_id int,
    @has_key_constraint int,
    @has_index int,
    @has_foreign_key int,
    @has_stats int,
    @stats_id int,
    @FK_objectid int,
    @FK_name sysname,
    @UQ_objectid int,
    @UQ_name sysname,
    @UQ_index_id int,
    @has_unique_constraint int,
    @has_default_constraint int,
    @DatabaseCollation sysname,
    @IndexColumns nvarchar(MAX),
    @IncludedColumns nvarchar(MAX);

    — Temporary table for generated script
    CREATE TABLE #tempscriptstore
    (ScriptType varchar(20),
    script nvarchar(MAX));

    — Get dataabse default collation
    SELECT @DatabaseCollation = collation_name
    FROM sys.databases
    WHERE database_id = DB_ID();

    /************************************************************************************************************************************
    * Iterate over all the tables that have at least one colmun where collation doesn’t match the database default. *
    * Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics. *
    * (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero) *
    ************************************************************************************************************************************/
    DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
    SELECT t.object_id,
    OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
    OBJECT_NAME(t.object_id) AS tablename,
    COUNT(kc.object_id) AS has_key_constraint,
    COUNT(ic.index_id) AS has_index,
    COUNT(fk.constraint_object_id) AS has_foreign_key,
    COUNT(st.stats_id) AS has_stats,
    COUNT(uq.object_id) AS has_unique_constraint,
    COUNT(dc.object_id) AS has_default_constraint
    FROM sys.tables AS t
    INNER JOIN sys.columns AS c
    ON c.object_id = t.object_id
    AND c.collation_name <> @DatabaseCollation — Table needs to have columns with “wrong” collation
    INNER JOIN sys.types AS ty
    ON ty.system_type_id = c.system_type_id
    AND ty.name <> N’sysname’ — Exclusion retained from Philip C’s original script
    LEFT JOIN sys.index_columns AS ic — Find indexes on any of the affected columns
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
    LEFT JOIN sys.key_constraints AS kc — Find primary key constraints related to an affected index
    ON kc.parent_object_id = c.object_id
    AND kc.unique_index_id = ic.index_id
    AND kc.type = ‘PK’
    LEFT JOIN sys.key_constraints AS uq — Find unique constraints related to an affected index
    ON uq.parent_object_id = c.object_id
    AND uq.unique_index_id = ic.index_id
    AND uq.type = ‘UQ’
    LEFT JOIN sys.foreign_key_columns AS fk — Find foreign key constraints on any of the affected columns
    ON fk.parent_object_id = c.object_id
    AND fk.parent_column_id = c.column_id
    LEFT JOIN sys.stats_columns AS st — Find statistics on any of the affected columns
    ON st.object_id = c.object_id
    AND st.column_id = c.column_id
    AND st.stats_column_id <> 1 — Retained from Philip C’s original script, no idea why this is in the query
    LEFT JOIN sys.default_constraints AS dc — Find default constraints on any of the affected columns
    ON dc.object_id = c.default_object_id
    AND dc.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0 — Exclude Microsoft-shipped tables
    GROUP BY t.object_id;

    OPEN collationfix;
    FETCH NEXT FROM collationfix
    INTO @object_id,
    @SchemaName,
    @TableName,
    @has_key_constraint,
    @has_index,
    @has_foreign_key,
    @has_stats,
    @has_unique_constraint,
    @has_default_constraint;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN;

    /************************************************************************************************************************************
    *   Generates the code to update the colation for all affected columns in the table                                                 *
    ************************************************************************************************************************************/
    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'AlterCollation',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                + QUOTENAME(c.name) + ' '
                + CASE WHEN ty.name = N'ntext'
                           THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                       ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                      THEN N'MAX'
                                                  ELSE CASE WHEN ty.name = N'nvarchar'
                                                                THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                            ELSE CAST(c.max_length AS nvarchar(20))
                                                       END
                                             END + N') COLLATE ' + @DatabaseCollation
                  END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
    FROM        sys.columns AS c
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation;
    
    /************************************************************************************************************************************
    *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
    ************************************************************************************************************************************/
    IF @has_index > 0
    BEGIN;
        -- Process indexes on affected columns, one by one
        DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
        SELECT  ix.index_id,
                ix.name,
                CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                ix.type_desc,
                CASE WHEN ix.is_padded = 1
                         THEN N'PAD_INDEX = ON, '
                     ELSE N'PAD_INDEX = OFF, '
                END + CASE WHEN ix.allow_page_locks = 1
                               THEN N'ALLOW_PAGE_LOCKS = ON, '
                           ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                      END + CASE WHEN ix.allow_row_locks = 1
                                     THEN N'ALLOW_ROW_LOCKS = ON, '
                                 ELSE N'ALLOW_ROW_LOCKS = OFF, '
                            END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                           THEN N'STATISTICS_NORECOMPUTE = ON, '
                                       ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                  END + CASE WHEN ix.ignore_dup_key = 1
                                                 THEN N'IGNORE_DUP_KEY = ON, '
                                             ELSE N'IGNORE_DUP_KEY = OFF, '
                                        END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                + CASE WHEN ix.fill_factor = 0
                           THEN CAST(100 AS nvarchar(3))
                       ELSE CAST(ix.fill_factor AS nvarchar(3))
                  END                            AS IndexOptions,
                ix.is_disabled,
                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
        FROM    sys.indexes AS ix
        WHERE   ix.object_id        = @object_id
        AND     ix.type                 <> 0 -- Exclude heaps
        AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
        AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.index_id = ix.index_id
                        AND         ic.object_id        = ix.object_id);
    
        OPEN CursorIndex;
        FETCH NEXT FROM CursorIndex
        INTO @index_id,
             @IndexName,
             @is_unique,
             @IndexTypeDesc,
             @IndexOptions,
             @is_disabled,
             @FileGroupName;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- For each index, use a nested cursor to build lists of indexed and inclued columns
            SET @IndexColumns = N'';
            SET @IncludedColumns = N'';
    
            DECLARE CursorIndexColumn CURSOR FOR
            SELECT      c.name,
                        ic.is_descending_key,
                        ic.is_included_column
            FROM        sys.index_columns AS ic
            INNER JOIN  sys.columns       AS c
               ON       c.object_id = ic.object_id
               AND      c.column_id = ic.column_id
            WHERE       ic.object_id = @object_id
            AND         ic.index_id          = @index_id
            ORDER BY    ic.index_column_id;
    
            OPEN CursorIndexColumn;
            FETCH NEXT FROM CursorIndexColumn
            INTO @ColumnName,
                 @IsDescendingKey,
                 @IsIncludedColumn;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                IF @IsIncludedColumn = 0
                    SET @IndexColumns += @ColumnName
                                         + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                ELSE
                    SET @IncludedColumns += @ColumnName + N', ';
    
                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
            END;
    
            CLOSE CursorIndexColumn;
            DEALLOCATE CursorIndexColumn;
    
            -- Remove trailing comma
            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
            IF @IncludedColumns <> N''
                SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
    
            -- Generate DROP INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropIndex',
                    N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                    + N';';
    
            -- Generate CREATE INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'CreateIndex',
                    N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                    + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                    + CASE WHEN @IncludedColumns <> N''
                               THEN N'INCLUDE (' + @IncludedColumns + N')'
                           ELSE N''
                      END + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
    
            -- Generate script to (re)disable disabled index after creating it
            IF @is_disabled = 1
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DisableIndex',
                        N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                        + QUOTENAME(@TableName) + N' DISABLE;';
    
            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
        END;
    
        CLOSE CursorIndex;
        DEALLOCATE CursorIndex;
    END;
    
    /************************************************************************************************************************************
    *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
    *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_key_constraint > 0
    BEGIN;
        -- Find columns and other metadata for primary key
        SELECT      kc.object_id                                                                          AS constid,
                    kc.name                                                                               AS constraint_name,   -- PK name
                    QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                    ic.key_ordinal,
                    i.name                                                                                AS index_name,
                    i.type_desc                                                                           AS index_type,
                    QUOTENAME(fg.name)                                                                    AS filegroup_name,
                    N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                    + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                    + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                    + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
        INTO        #temp_pk
        FROM        sys.key_constraints AS kc
        INNER JOIN  sys.indexes         AS i
           ON       i.object_id      = kc.parent_object_id
           AND      i.is_primary_key = 1
        INNER JOIN  sys.index_columns   AS ic
           ON       ic.object_id     = i.object_id
           AND      ic.index_id      = i.index_id
        INNER JOIN  sys.columns         AS c
           ON       c.object_id      = ic.object_id
           AND      c.column_id      = ic.column_id
        INNER JOIN  sys.filegroups      AS fg
           ON       fg.data_space_id = i.data_space_id
        WHERE       kc.type     = 'PK'
        AND         kc.parent_object_id = @object_id;
    
        -- Generate DROP CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                'DropPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                + QUOTENAME(constraint_name) + N';'
        FROM    #temp_pk;
    
        -- Generate CREATE CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                'AddPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                         FROM       #temp_pk
                         ORDER BY   key_ordinal
                        FOR XML PATH('')),
                        1,
                        2,
                        N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
        FROM    #temp_pk AS p;
    
        DROP TABLE #temp_pk;
    END;
    
    /************************************************************************************************************************************
    *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
    *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_foreign_key > 0
    BEGIN;
        -- Process foreign key constraints on affected columns, one by one
        DECLARE foreignkeycursor CURSOR FOR
        SELECT  fk.object_id,
                fk.name
        FROM    sys.foreign_keys AS fk
        WHERE   fk.parent_object_id = @object_id
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.foreign_key_columns AS fkc
                        INNER JOIN  sys.columns             AS c
                           ON       c.object_id       = fkc.parent_object_id
                           AND      c.column_id       = fkc.parent_column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types               AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       fkc.parent_object_id = fk.parent_object_id
                        AND         fkc.constraint_object_id     = fk.object_id);
    
        OPEN foreignkeycursor;
        FETCH NEXT FROM foreignkeycursor
        INTO @FK_objectid,
             @FK_name;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for foreign key
            SELECT      fk.object_id                                      AS constid,
                        fk.name                                           AS constraint_name,   -- FK name
                        fkc.constraint_column_id                          AS column_ordinal,
                        QUOTENAME(c1.name)                                AS key_col,
                        QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                        + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                        QUOTENAME(c2.name)                                AS ref_col,
                        CASE WHEN fk.delete_referential_action <> 0 -- No action
                                 THEN N' ON DELETE ' + fk.delete_referential_action_desc
                             ELSE N''
                        END                                               AS delete_cascade,
                        CASE WHEN fk.update_referential_action <> 0 -- No action
                                 THEN N' ON UPDATE ' + fk.update_referential_action_desc
                             ELSE N''
                        END                                               AS update_cascade,
                        fk.is_not_trusted,
                        fk.is_disabled
            INTO        #temp_fk
            FROM        sys.foreign_keys        AS fk
            INNER JOIN  sys.foreign_key_columns AS fkc
               ON       fkc.parent_object_id     = fk.parent_object_id
               AND      fkc.constraint_object_id = fk.object_id
            INNER JOIN  sys.columns             AS c1
               ON       c1.object_id             = fkc.parent_object_id
               AND      c1.column_id             = fkc.parent_column_id
            INNER JOIN  sys.columns             AS c2
               ON       c2.object_id             = fkc.referenced_object_id
               AND      c2.column_id             = fkc.referenced_column_id
            WHERE       fk.object_id = @FK_objectid;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@FK_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                    + STUFF(
                      (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N') REFERENCES ' + ref_table + N'('
                    + STUFF(
                      (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N')' + delete_cascade + update_cascade + ';'
            FROM    #temp_fk;
    
            -- If constraint was disabled, add code to disable it again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'DisableForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_disabled = 1;
    
            -- If constraint was trusted, add code to force re-check so it's trudsted again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'CheckForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_not_trusted = 0;
    
            DROP TABLE #temp_fk;
    
            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
        END;
        CLOSE foreignkeycursor;
        DEALLOCATE foreignkeycursor;
    END;
    
    /*
    

    — DROP and CREATE statistics for manual statistics has been disabled for now
    /************************************************************************************************************************************
    * If the column has statistics that aren’t part of an index this creates the drop and recreate scripts *
    ************************************************************************************************************************************/
    IF @has_stats > 0
    AND @has_index = 0
    BEGIN
    DECLARE stats_cursor CURSOR FOR
    SELECT sc.stats_id
    FROM sys.stats_columns AS sc
    JOIN sys.stats AS s
    ON s.object_id = sc.object_id
    AND s.stats_id = sc.stats_id
    AND s.user_created = 1
    WHERE sc.object_id = @object_id
    AND sc.column_id = @Collation_columnid;
    OPEN stats_cursor;
    FETCH NEXT FROM stats_cursor
    INTO @stats_id;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    –Create DROP Statistics Statement
    INSERT INTO #tempscriptstore
    (ScriptType,
    script)
    SELECT ‘DropStatistics’,
    ‘DROP STATISTICS ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + ‘.’
    + QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ + QUOTENAME(s.name)
    FROM sys.stats AS s
    WHERE s.object_id = @object_id
    AND s.stats_id = @stats_id;

            --Building the CREATE statistics statement
    
            --Obtaining all the information
            SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                    QUOTENAME(s.name)                                                                        AS st_name,
                    QUOTENAME(c.name)                                                                        AS st_column,
                    sc.object_id,
                    sc.stats_id,
                    sc.stats_column_id
            INTO    #temp_stats
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.stats_id  = sc.stats_id
              AND   s.object_id = sc.object_id
            JOIN    sys.columns       AS c
              ON    c.object_id = sc.object_id
              AND   c.column_id = sc.column_id
            WHERE   sc.object_id = @object_id
            AND     sc.stats_id      = @stats_id;
            WITH
            cte AS
                (SELECT DISTINCT ts.st_table,
                                 ts.st_name,
                                 SUBSTRING((SELECT      ',' + st_column
                                            FROM        #temp_stats
                                            WHERE       stats_id = ts.stats_id
                                            ORDER BY    stats_column_id ASC
                                           FOR XML PATH('')),
                                           2,
                                           99999) AS st_col_list
                 FROM   #temp_stats AS ts)
            --Constructing the statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'AddStatistics',
                    'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
            FROM    cte;
            DROP TABLE #temp_stats;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
        END;
        CLOSE stats_cursor;
        DEALLOCATE stats_cursor;
    END;
    

    */

    /************************************************************************************************************************************
    *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
    ************************************************************************************************************************************/
    IF @has_unique_constraint > 0
    BEGIN;
        -- Process unique constraints on affected columns, one by one
        DECLARE uniquecursor CURSOR FOR
        SELECT  kc.object_id,
                kc.name,
                kc.unique_index_id
        FROM    sys.key_constraints AS kc
        WHERE   kc.parent_object_id = @object_id
        AND     kc.type                 = 'UQ'
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.object_id = kc.parent_object_id
                        AND         ic.index_id          = kc.unique_index_id);
    
        OPEN uniquecursor;
        FETCH NEXT FROM uniquecursor
        INTO @UQ_objectid,
             @UQ_name,
             @UQ_index_id;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for unique constraint
            SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                        ic.key_ordinal,
                        i.name                                                                                    AS index_name,
                        i.type_desc                                                                               AS index_type,
                        QUOTENAME(fg.name)                                                                        AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                        + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                        + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                        + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
            INTO        #temp_uq
            FROM        sys.indexes       AS i
            INNER JOIN  sys.index_columns AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns       AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups    AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       i.object_id    = @object_id
            AND         i.index_id             = @UQ_index_id
            AND         i.is_unique_constraint = 1;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_uq
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
            FROM    #temp_uq;
    
            DROP TABLE #temp_uq;
    
            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
        END;
        CLOSE uniquecursor;
        DEALLOCATE uniquecursor;
    END;
    
    
    /************************************************************************************************************************************
    *  If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table                *
    ************************************************************************************************************************************/
    IF @has_default_constraint > 0
    BEGIN
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'DropDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(dc.name) + ' '
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc 
           ON       dc.object_id = c.default_object_id 
           AND      dc.parent_column_id = c.column_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    
    
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'CreateDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
                    + QUOTENAME(dc.name) + ' ''' + dc.definition + ''''
                    + ' FOR ' + QUOTENAME(c.name) 
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc on dc.object_id = c.default_object_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    End
    
    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint,
         @has_default_constraint;
    

    END;
    CLOSE collationfix;
    DEALLOCATE collationfix;

    /************************************************************************************************************************************
    * Returns all the created scripts in the correct order for running *
    * Also add GO in between some sections so the entire script can execute at once *
    ************************************************************************************************************************************/
    DECLARE @oddnum INT = 1;
    WITH ScriptInput
    AS (SELECT script,
    CASE WHEN ScriptType = ‘DropDefault’
    THEN 1
    WHEN ScriptType = ‘DropForeignKey’
    THEN 3
    WHEN ScriptType = ‘DropIndex’
    THEN 5
    WHEN ScriptType = ‘DropUniqueKey’
    THEN 7
    WHEN ScriptType = ‘DropPrimaryKey’
    THEN 9
    WHEN ScriptType = ‘DropStatistics’
    THEN 11
    WHEN ScriptType = ‘AlterCollation’
    THEN 13
    WHEN ScriptType = ‘AddPrimaryKey’
    THEN 15
    WHEN ScriptType = ‘AddUniqueKey’
    THEN 17
    WHEN ScriptType = ‘CreateIndex’
    THEN 19
    WHEN ScriptType = ‘AddStatistics’
    THEN 21
    WHEN ScriptType = ‘AddForeignKey’
    THEN 23
    WHEN ScriptType = ‘DisableForeignKey’
    THEN 25
    WHEN ScriptType = ‘CheckForeignKey’
    THEN 27
    WHEN ScriptType = ‘DisableIndex’
    THEN 29
    WHEN ScriptType = ‘CreateDefault’
    THEN 31
    ELSE 99
    END AS SortOrder
    FROM #tempscriptstore
    UNION ALL
    SELECT CASE WHEN n.num > 0
    THEN ‘GO’ + CHAR(13)+CHAR(10) + descrip
    ELSE descrip end,
    n.num
    FROM (VALUES (0, ‘—- Drop Default’),
    (2, ‘—- Drop Foreign Key’),
    (4, ‘—- Drop Index’),
    (6, ‘—- Drop Unique Key’),
    (8, ‘—- Drop Primary Key’),
    –(10, ‘—- Drop Statistics’),
    (12, ‘—- Alter Collation ‘),
    (14, ‘—- Add Primary Key ‘),
    (16, ‘—- Add Unique Key ‘),
    (18, ‘—- Create Index ‘),
    –(20, ‘—- Add Statistics ‘),
    (22, ‘—- Add Foreign Key ‘),
    (24, ‘—- Disable Foreign Key ‘),
    (26, ‘—- Check Foreign Key ‘),
    (28, ‘—- Disable Index ‘),
    (30, ‘—- Create Default ‘),
    (32, ‘—- End of script’)
    ) AS n (num, descrip) )
    SELECT ScriptInput.script
    FROM ScriptInput
    ORDER BY ScriptInput.SortOrder;

    DROP TABLE #tempscriptstore;
    End;

    Reply
  • Walter Charrière
    August 12, 2019 20:02

    Hi again, hope this time works, corrected some minor errors, add consideration of iltered indexes.

    Begin
    — SSMS results to text – max length 8192 (change from default!!!)

    /*******************************************************************************
    *
    * Created 2017-06-16 By Philip C
    *
    * This script will check individual columns collations and check it against the
    * database default collation, where they are different it will create the scripts
    * required to drop all the objects dependant on the column, change the collation
    * to the database default and then recreate the dependant objects.
    * Some of the code has been reused from stuff found online the majority from 
    * Jayakumaur R who created scripts to drop and recreate constraints
    *
    *
    * Modified 2019-01-11 by Hugo Kornelis
    *
    * Removed double checking of recreated constraints
    * Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
    * Fixed a bug caused by unique constraints overlapping the primary key constraint
    * Fixed a bug in detection of foreign key constraints to drop and recreate
    * Fixed bugs with recreating multi-column UNIQUE constraints
    *
    * Modified 2019-08-12 by Walter Charrière 
    *
    * Add drop and create default constraints
    * Fixed bug with text columns adding length
    * Add Where calusule for filtered indexes
    *
    *********************************************************************************/
    
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    GO
    
    -- Declare variables
    DECLARE @SchemaName             sysname,
            @TableName              sysname,
            @IndexName              sysname,
            @ColumnName             sysname,
            @index_id               int,
            @is_unique              nvarchar(100),
            @IndexTypeDesc          nvarchar(100),
            @FileGroupName          sysname,
            @is_disabled            nvarchar(100),
            @IndexOptions           nvarchar(MAX),
            @IndexColumnId          int,
            @IsDescendingKey        int,
            @IsIncludedColumn       int,
            @TSQLScripCreationIndex nvarchar(MAX),
            @TSQLScripDisableIndex  nvarchar(MAX),
            @object_id              int,
            @has_key_constraint     int,
            @has_index              int,
            @has_foreign_key        int,
            @has_stats              int,
            @stats_id               int,
            @FK_objectid            int,
            @FK_name                sysname,
            @UQ_objectid            int,
            @UQ_name                sysname,
            @UQ_index_id            int,
            @has_unique_constraint  int,
            @has_default_constraint int,
            @DatabaseCollation      sysname,
            @IndexColumns           nvarchar(MAX),
            @IncludedColumns        nvarchar(MAX),
            @filter_definition      nvarchar(MAX);
    
    -- Temporary table for generated script
    CREATE TABLE #tempscriptstore
        (ScriptType varchar(20),
         script     nvarchar(MAX));
    
    -- Get dataabse default collation
    SELECT  @DatabaseCollation = collation_name
    FROM    sys.databases
    WHERE   database_id = DB_ID();
    
    /************************************************************************************************************************************
    *   Iterate over all the tables that have at least one colmun where collation doesn't match the database default.                   *
    *   Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics.       *
    *   (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero)             *
    ************************************************************************************************************************************/
    DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
    SELECT      t.object_id,
                OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
                OBJECT_NAME(t.object_id)        AS tablename,
                COUNT(kc.object_id)             AS has_key_constraint,
                COUNT(ic.index_id)              AS has_index,
                COUNT(fk.constraint_object_id)  AS has_foreign_key,
                COUNT(st.stats_id)              AS has_stats,
                COUNT(uq.object_id)             AS has_unique_constraint,
                COUNT(dc.object_id)             AS has_default_constraint
    FROM        sys.tables              AS t
    INNER JOIN  sys.columns             AS c
       ON       c.object_id         = t.object_id
       AND      c.collation_name    <> @DatabaseCollation -- Table needs to have columns with "wrong" collation
    INNER JOIN  sys.types               AS ty
       ON       ty.system_type_id   = c.system_type_id
       AND      ty.name             <> N'sysname' -- Exclusion retained from Philip C's original script
    LEFT JOIN   sys.index_columns       AS ic -- Find indexes on any of the affected columns
      ON        ic.object_id        = c.object_id
      AND       ic.column_id        = c.column_id
    LEFT JOIN   sys.key_constraints     AS kc -- Find primary key constraints related to an affected index
      ON        kc.parent_object_id = c.object_id
      AND       kc.unique_index_id  = ic.index_id
      AND       kc.type             = 'PK'
    LEFT JOIN   sys.key_constraints     AS uq -- Find unique constraints related to an affected index
      ON        uq.parent_object_id = c.object_id
      AND       uq.unique_index_id  = ic.index_id
      AND       uq.type             = 'UQ'
    LEFT JOIN   sys.foreign_key_columns AS fk -- Find foreign key constraints on any of the affected columns
      ON        fk.parent_object_id = c.object_id
      AND       fk.parent_column_id = c.column_id
    LEFT JOIN   sys.stats_columns       AS st -- Find statistics on any of the affected columns
      ON        st.object_id        = c.object_id
      AND       st.column_id        = c.column_id
      AND       st.stats_column_id  <> 1 -- Retained from Philip C's original script, no idea why this is in the query
    LEFT JOIN   sys.default_constraints AS dc -- Find default constraints on any of the affected columns
      ON        dc.object_id = c.default_object_id 
      AND       dc.parent_column_id = c.column_id
    WHERE       t.is_ms_shipped = 0 -- Exclude Microsoft-shipped tables
    GROUP BY    t.object_id;
    
    OPEN collationfix;
    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint,
         @has_default_constraint;
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN;
    
        /************************************************************************************************************************************
        *   Generates the code to update the colation for all affected columns in the table                                                 *
        ************************************************************************************************************************************/
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'AlterCollation',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                    + QUOTENAME(c.name) + ' '
                    + CASE WHEN ty.name = N'ntext' or ty.name = N'text'
                               THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                           ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                          THEN N'MAX'
                                                      ELSE CASE WHEN ty.name = N'nvarchar'
                                                                    THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                                ELSE CAST(c.max_length AS nvarchar(20))
                                                           END
                                                 END + N') COLLATE ' + @DatabaseCollation
                      END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
        FROM        sys.columns AS c
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    
        /************************************************************************************************************************************
        *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
        ************************************************************************************************************************************/
        IF @has_index > 0
        BEGIN;
            -- Process indexes on affected columns, one by one
            DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
            SELECT  ix.index_id,
                    ix.name,
                    CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                    ix.type_desc,
                    ix.filter_definition,
                    CASE WHEN ix.is_padded = 1
                             THEN N'PAD_INDEX = ON, '
                         ELSE N'PAD_INDEX = OFF, '
                    END + CASE WHEN ix.allow_page_locks = 1
                                   THEN N'ALLOW_PAGE_LOCKS = ON, '
                               ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                          END + CASE WHEN ix.allow_row_locks = 1
                                         THEN N'ALLOW_ROW_LOCKS = ON, '
                                     ELSE N'ALLOW_ROW_LOCKS = OFF, '
                                END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                               THEN N'STATISTICS_NORECOMPUTE = ON, '
                                           ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                      END + CASE WHEN ix.ignore_dup_key = 1
                                                     THEN N'IGNORE_DUP_KEY = ON, '
                                                 ELSE N'IGNORE_DUP_KEY = OFF, '
                                            END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                    + CASE WHEN ix.fill_factor = 0
                               THEN CAST(100 AS nvarchar(3))
                           ELSE CAST(ix.fill_factor AS nvarchar(3))
                      END                            AS IndexOptions,
                    ix.is_disabled,
                    FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
            FROM    sys.indexes AS ix
            WHERE   ix.object_id        = @object_id
            AND     ix.type                 <> 0 -- Exclude heaps
            AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
            AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
            AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                            FROM        sys.index_columns AS ic
                            INNER JOIN  sys.columns       AS c
                               ON       c.object_id       = ic.object_id
                               AND      c.column_id       = ic.column_id
                               AND      c.collation_name  <> @DatabaseCollation
                            INNER JOIN  sys.types         AS ty
                               ON       ty.system_type_id = c.system_type_id
                               AND      ty.name           <> N'sysname'
                            WHERE       ic.index_id = ix.index_id
                            AND         ic.object_id        = ix.object_id);
    
            OPEN CursorIndex;
            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @filter_definition,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                -- For each index, use a nested cursor to build lists of indexed and inclued columns
                SET @IndexColumns = N'';
                SET @IncludedColumns = N'';
    
                DECLARE CursorIndexColumn CURSOR FOR
                SELECT      c.name,
                            ic.is_descending_key,
                            ic.is_included_column
                FROM        sys.index_columns AS ic
                INNER JOIN  sys.columns       AS c
                   ON       c.object_id = ic.object_id
                   AND      c.column_id = ic.column_id
                WHERE       ic.object_id = @object_id
                AND         ic.index_id          = @index_id
                ORDER BY    ic.index_column_id;
    
                OPEN CursorIndexColumn;
                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
    
                WHILE (@@FETCH_STATUS = 0)
                BEGIN;
                    IF @IsIncludedColumn = 0
                        SET @IndexColumns += @ColumnName
                                             + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                    ELSE
                        SET @IncludedColumns += @ColumnName + N', ';
    
                    FETCH NEXT FROM CursorIndexColumn
                    INTO @ColumnName,
                         @IsDescendingKey,
                         @IsIncludedColumn;
                END;
    
                CLOSE CursorIndexColumn;
                DEALLOCATE CursorIndexColumn;
    
                -- Remove trailing comma
                SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
                IF @IncludedColumns <> N''
                    SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
    
                -- Generate DROP INDEX statement
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DropIndex',
                        N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                        + N';';
    
                -- Generate CREATE INDEX statement
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'CreateIndex',
                        N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                        + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                        + CASE WHEN @IncludedColumns <> N''
                                   THEN N'INCLUDE (' + @IncludedColumns + N')'
                               ELSE N''
                          END 
                        + CASE WHEN @filter_definition <> '' 
                                    THEN N' WHERE ' + @filter_definition ELSE '' 
                          END 
                        + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
                -- Generate script to (re)disable disabled index after creating it
                IF @is_disabled = 1
                    INSERT INTO #tempscriptstore (ScriptType,
                                                  script)
                    SELECT  'DisableIndex',
                            N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                            + QUOTENAME(@TableName) + N' DISABLE;';
    
                FETCH NEXT FROM CursorIndex
                INTO @index_id,
                     @IndexName,
                     @is_unique,
                     @IndexTypeDesc,
                     @filter_definition,
                     @IndexOptions,
                     @is_disabled,
                     @FileGroupName;
            END;
    
            CLOSE CursorIndex;
            DEALLOCATE CursorIndex;
        END;
    
        /************************************************************************************************************************************
        *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
        *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
        ************************************************************************************************************************************/
        IF @has_key_constraint > 0
        BEGIN;
            -- Find columns and other metadata for primary key
            SELECT      kc.object_id                                                                          AS constid,
                        kc.name                                                                               AS constraint_name,   -- PK name
                        QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                        ic.key_ordinal,
                        i.name                                                                                AS index_name,
                        i.type_desc                                                                           AS index_type,
                        QUOTENAME(fg.name)                                                                    AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                        + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                        + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                        + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
            INTO        #temp_pk
            FROM        sys.key_constraints AS kc
            INNER JOIN  sys.indexes         AS i
               ON       i.object_id      = kc.parent_object_id
               AND      i.is_primary_key = 1
            INNER JOIN  sys.index_columns   AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns         AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups      AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       kc.type     = 'PK'
            AND         kc.parent_object_id = @object_id;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                    'DropPrimaryKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_pk;
    
            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddPrimaryKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_pk
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
            FROM    #temp_pk AS p;
    
            DROP TABLE #temp_pk;
        END;
    
        /************************************************************************************************************************************
        *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
        *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
        ************************************************************************************************************************************/
        IF @has_foreign_key > 0
        BEGIN;
            -- Process foreign key constraints on affected columns, one by one
            DECLARE foreignkeycursor CURSOR FOR
            SELECT  fk.object_id,
                    fk.name
            FROM    sys.foreign_keys AS fk
            WHERE   fk.parent_object_id = @object_id
            AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                            FROM        sys.foreign_key_columns AS fkc
                            INNER JOIN  sys.columns             AS c
                               ON       c.object_id       = fkc.parent_object_id
                               AND      c.column_id       = fkc.parent_column_id
                               AND      c.collation_name  <> @DatabaseCollation
                            INNER JOIN  sys.types               AS ty
                               ON       ty.system_type_id = c.system_type_id
                               AND      ty.name           <> N'sysname'
                            WHERE       fkc.parent_object_id = fk.parent_object_id
                            AND         fkc.constraint_object_id     = fk.object_id);
    
            OPEN foreignkeycursor;
            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                -- Find columns and other metadata for foreign key
                SELECT      fk.object_id                                      AS constid,
                            fk.name                                           AS constraint_name,   -- FK name
                            fkc.constraint_column_id                          AS column_ordinal,
                            QUOTENAME(c1.name)                                AS key_col,
                            QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                            + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                            QUOTENAME(c2.name)                                AS ref_col,
                            CASE WHEN fk.delete_referential_action <> 0 -- No action
                                     THEN N' ON DELETE ' + fk.delete_referential_action_desc
                                 ELSE N''
                            END                                               AS delete_cascade,
                            CASE WHEN fk.update_referential_action <> 0 -- No action
                                     THEN N' ON UPDATE ' + fk.update_referential_action_desc
                                 ELSE N''
                            END                                               AS update_cascade,
                            fk.is_not_trusted,
                            fk.is_disabled
                INTO        #temp_fk
                FROM        sys.foreign_keys        AS fk
                INNER JOIN  sys.foreign_key_columns AS fkc
                   ON       fkc.parent_object_id     = fk.parent_object_id
                   AND      fkc.constraint_object_id = fk.object_id
                INNER JOIN  sys.columns             AS c1
                   ON       c1.object_id             = fkc.parent_object_id
                   AND      c1.column_id             = fkc.parent_column_id
                INNER JOIN  sys.columns             AS c2
                   ON       c2.object_id             = fkc.referenced_object_id
                   AND      c2.column_id             = fkc.referenced_column_id
                WHERE       fk.object_id = @FK_objectid;
    
                -- Generate DROP CONSTRAINT statement
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DropForeignKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                        + QUOTENAME(@FK_name) + N';';
    
                -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                        'AddForeignKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                        + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                        + STUFF(
                          (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                           FROM         #temp_fk
                           ORDER BY     column_ordinal
                          FOR XML PATH('')),
                          1,
                          2,
                          N'') + N') REFERENCES ' + ref_table + N'('
                        + STUFF(
                          (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                           FROM         #temp_fk
                           ORDER BY     column_ordinal
                          FOR XML PATH('')),
                          1,
                          2,
                          N'') + N')' + delete_cascade + update_cascade + ';'
                FROM    #temp_fk;
    
                -- If constraint was disabled, add code to disable it again
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                        'DisableForeignKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                        + QUOTENAME(constraint_name) + N';'
                FROM    #temp_fk
                WHERE   is_disabled = 1;
    
                -- If constraint was trusted, add code to force re-check so it's trudsted again
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                        'CheckForeignKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                        + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
                FROM    #temp_fk
                WHERE   is_not_trusted = 0;
    
                DROP TABLE #temp_fk;
    
                FETCH NEXT FROM foreignkeycursor
                INTO @FK_objectid,
                     @FK_name;
            END;
            CLOSE foreignkeycursor;
            DEALLOCATE foreignkeycursor;
        END;
    
        /*
    -- DROP and CREATE statistics for manual statistics has been disabled for now
        /************************************************************************************************************************************
        *   If the column has statistics that aren't part of an index this creates the drop and recreate scripts                            *
        ************************************************************************************************************************************/
        IF  @has_stats > 0
        AND @has_index = 0
        BEGIN
            DECLARE stats_cursor CURSOR FOR
                SELECT  sc.stats_id
                FROM    sys.stats_columns AS sc
                JOIN    sys.stats         AS s
                  ON    s.object_id    = sc.object_id
                  AND   s.stats_id     = sc.stats_id
                  AND   s.user_created = 1
                WHERE   sc.object_id = @object_id
                AND     sc.column_id     = @Collation_columnid;
            OPEN stats_cursor;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
            WHILE (@@FETCH_STATUS = 0)
            BEGIN
                --Create DROP Statistics Statement
                INSERT INTO #tempscriptstore
                    (ScriptType,
                     script)
                SELECT  'DropStatistics',
                        'DROP STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.'
                        + QUOTENAME(OBJECT_NAME(s.object_id)) + '.' + QUOTENAME(s.name)
                FROM    sys.stats AS s
                WHERE   s.object_id = @object_id
                AND     s.stats_id      = @stats_id;
    
                --Building the CREATE statistics statement
    
                --Obtaining all the information
                SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                        QUOTENAME(s.name)                                                                        AS st_name,
                        QUOTENAME(c.name)                                                                        AS st_column,
                        sc.object_id,
                        sc.stats_id,
                        sc.stats_column_id
                INTO    #temp_stats
                FROM    sys.stats_columns AS sc
                JOIN    sys.stats         AS s
                  ON    s.stats_id  = sc.stats_id
                  AND   s.object_id = sc.object_id
                JOIN    sys.columns       AS c
                  ON    c.object_id = sc.object_id
                  AND   c.column_id = sc.column_id
                WHERE   sc.object_id = @object_id
                AND     sc.stats_id      = @stats_id;
                WITH
                cte AS
                    (SELECT DISTINCT ts.st_table,
                                     ts.st_name,
                                     SUBSTRING((SELECT      ',' + st_column
                                                FROM        #temp_stats
                                                WHERE       stats_id = ts.stats_id
                                                ORDER BY    stats_column_id ASC
                                               FOR XML PATH('')),
                                               2,
                                               99999) AS st_col_list
                     FROM   #temp_stats AS ts)
                --Constructing the statement
                INSERT INTO #tempscriptstore
                    (ScriptType,
                     script)
                SELECT  'AddStatistics',
                        'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
                FROM    cte;
                DROP TABLE #temp_stats;
                FETCH NEXT FROM stats_cursor
                INTO @stats_id;
            END;
            CLOSE stats_cursor;
            DEALLOCATE stats_cursor;
        END;
    */
    
        /************************************************************************************************************************************
        *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
        ************************************************************************************************************************************/
        IF @has_unique_constraint > 0
        BEGIN;
            -- Process unique constraints on affected columns, one by one
            DECLARE uniquecursor CURSOR FOR
            SELECT  kc.object_id,
                    kc.name,
                    kc.unique_index_id
            FROM    sys.key_constraints AS kc
            WHERE   kc.parent_object_id = @object_id
            AND     kc.type                 = 'UQ'
            AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                            FROM        sys.index_columns AS ic
                            INNER JOIN  sys.columns       AS c
                               ON       c.object_id       = ic.object_id
                               AND      c.column_id       = ic.column_id
                               AND      c.collation_name  <> @DatabaseCollation
                            INNER JOIN  sys.types         AS ty
                               ON       ty.system_type_id = c.system_type_id
                               AND      ty.name           <> N'sysname'
                            WHERE       ic.object_id = kc.parent_object_id
                            AND         ic.index_id          = kc.unique_index_id);
    
            OPEN uniquecursor;
            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                -- Find columns and other metadata for unique constraint
                SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                            ic.key_ordinal,
                            i.name                                                                                    AS index_name,
                            i.type_desc                                                                               AS index_type,
                            QUOTENAME(fg.name)                                                                        AS filegroup_name,
                            N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                            + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                            + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                            + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
                INTO        #temp_uq
                FROM        sys.indexes       AS i
                INNER JOIN  sys.index_columns AS ic
                   ON       ic.object_id     = i.object_id
                   AND      ic.index_id      = i.index_id
                INNER JOIN  sys.columns       AS c
                   ON       c.object_id      = ic.object_id
                   AND      c.column_id      = ic.column_id
                INNER JOIN  sys.filegroups    AS fg
                   ON       fg.data_space_id = i.data_space_id
                WHERE       i.object_id    = @object_id
                AND         i.index_id             = @UQ_index_id
                AND         i.is_unique_constraint = 1;
    
                -- Generate DROP CONSTRAINT statement
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DropUniqueKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                        + QUOTENAME(@UQ_name) + N';';
    
                -- Generate CREATE CONSTRAINT statement
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                        'AddUniqueKey',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                        + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                        + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                                 FROM       #temp_uq
                                 ORDER BY   key_ordinal
                                FOR XML PATH('')),
                                1,
                                2,
                                N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
                FROM    #temp_uq;
    
                DROP TABLE #temp_uq;
    
                FETCH NEXT FROM uniquecursor
                INTO @UQ_objectid,
                     @UQ_name,
                     @UQ_index_id;
            END;
            CLOSE uniquecursor;
            DEALLOCATE uniquecursor;
        END;
    
    
        /************************************************************************************************************************************
        *  If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table                *
        ************************************************************************************************************************************/
        IF @has_default_constraint > 0
        BEGIN
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT      'DropDefault',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                        + QUOTENAME(dc.name) + ' '
            FROM        sys.columns AS c 
            INNER JOIN  sys.default_constraints dc 
               ON       dc.object_id = c.default_object_id 
               AND      dc.parent_column_id = c.column_id 
            INNER JOIN  sys.types   AS ty
               ON       ty.system_type_id = c.system_type_id
               AND      ty.name           <> N'sysname'
            WHERE       c.object_id = @object_id
            AND         c.collation_name    <> @DatabaseCollation;
    
    
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT      'CreateDefault',
                        N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
                        + dc.definition + ' FOR ' + QUOTENAME(c.name) 
            FROM        sys.columns AS c 
            INNER JOIN  sys.default_constraints dc on dc.object_id = c.default_object_id 
            INNER JOIN  sys.types   AS ty
               ON       ty.system_type_id = c.system_type_id
               AND      ty.name           <> N'sysname'
            WHERE       c.object_id = @object_id
            AND         c.collation_name    <> @DatabaseCollation;
        End
    
        FETCH NEXT FROM collationfix
        INTO @object_id,
             @SchemaName,
             @TableName,
             @has_key_constraint,
             @has_index,
             @has_foreign_key,
             @has_stats,
             @has_unique_constraint,
             @has_default_constraint;
    END;
    CLOSE collationfix;
    DEALLOCATE collationfix;
    
    /************************************************************************************************************************************
    *   Returns all the created scripts in the correct order for running                                                                *
    *   Also add GO in between some sections so the entire script can execute at once                                                   *
    ************************************************************************************************************************************/
    DECLARE @oddnum INT = 1;
    WITH ScriptInput
      AS (SELECT    script,
                    CASE WHEN ScriptType = 'DropDefault'
                             THEN 1
                         WHEN ScriptType = 'DropForeignKey'
                             THEN 3
                         WHEN ScriptType = 'DropIndex'
                             THEN 5
                         WHEN ScriptType = 'DropUniqueKey'
                             THEN 7
                         WHEN ScriptType = 'DropPrimaryKey'
                             THEN 9
                         WHEN ScriptType = 'DropStatistics'
                             THEN 11
                         WHEN ScriptType = 'AlterCollation'
                             THEN 13
                         WHEN ScriptType = 'AddPrimaryKey'
                             THEN 15
                         WHEN ScriptType = 'AddUniqueKey'
                             THEN 17
                         WHEN ScriptType = 'CreateIndex'
                             THEN 19
                         WHEN ScriptType = 'AddStatistics'
                             THEN 21
                         WHEN ScriptType = 'AddForeignKey'
                             THEN 23
                         WHEN ScriptType = 'DisableForeignKey'
                             THEN 25
                         WHEN ScriptType = 'CheckForeignKey'
                             THEN 27
                         WHEN ScriptType = 'DisableIndex'
                             THEN 29
                         WHEN ScriptType = 'CreateDefault'
                             THEN 31
                         ELSE 99
                    END AS SortOrder
          FROM      #tempscriptstore
          UNION ALL
          SELECT CASE WHEN n.num > 0 
                      THEN 'GO' + CHAR(13)+CHAR(10) + descrip 
                      ELSE descrip end,         
                 n.num
          FROM (VALUES (0,  '---- Drop Default'),   
                       (2,  '---- Drop Foreign Key'),
                       (4,  '---- Drop Index'),
                       (6,  '---- Drop Unique Key'),
                       (8,  '---- Drop Primary Key'),
                       --(10, '---- Drop Statistics'),
                       (12, '---- Alter Collation '),
                       (14, '---- Add Primary Key '),
                       (16, '---- Add Unique Key '),
                       (18, '---- Create Index '),
                       --(20, '---- Add Statistics '),
                       (22, '---- Add Foreign Key '),
                       (24, '---- Disable Foreign Key '),
                       (26, '---- Check Foreign Key '),
                       (28, '---- Disable Index '),
                       (30, '---- Create Default '),
                       (32, '---- End of script')
          ) AS n (num, descrip) )
    SELECT      ScriptInput.script
    FROM        ScriptInput
    ORDER BY    ScriptInput.SortOrder;
    
    DROP TABLE #tempscriptstore;
    

    End

    Reply
  • A poster on the MSDN forums experienced a little glitch with this useful script. He had a a number of nchar columns and the length of these doubled. This line:

    ELSE CASE WHEN ty.name = N’nvarchar’

    which is line 139 in Hugo’s original script and line 150 in Walters updated version should read:

    ELSE CASE WHEN ty.name IN (N’nvarchar’, N’nchar’)

    Reply
  • I ran into an issue using this with column names that are reserved words, specifically in the CREATE INDEX (lines 252 and 255). I fixed this by adding QUOTENAME where it builds the column. I also included Erland’s suggestion to fix the NCHAR length doubling.

    Not sure how to do the nice formatting, but here it is below.

    /*******************************************************************************
    *
    * Created 2017-06-16 By Philip C
    *
    * This script will check individual columns collations and check it against the
    * database default collation, where they are different it will create the scripts
    * required to drop all the objects dependant on the column, change the collation
    * to the database default and then recreate the dependant objects.
    * Some of the code has been reused from stuff found online the majority from
    * Jayakumaur R who created scripts to drop and recreate constraints
    *
    *
    * Modified 2019-01-11 by Hugo Kornelis
    *
    * Removed double checking of recreated constraints
    * Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
    * Fixed a bug caused by unique constraints overlapping the primary key constraint
    * Fixed a bug in detection of foreign key constraints to drop and recreate
    * Fixed bugs with recreating multi-column UNIQUE constraints
    *
    * Modified 2019-08-12 by Walter Charrière
    *
    * Add drop and create default constraints
    * Fixed bug with text columns adding length
    * Add Where calusule for filtered indexes
    *
    *********************************************************************************/

    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    GO

    — Declare variables
    DECLARE @SchemaName sysname,
    @TableName sysname,
    @IndexName sysname,
    @ColumnName sysname,
    @index_id int,
    @is_unique nvarchar(100),
    @IndexTypeDesc nvarchar(100),
    @FileGroupName sysname,
    @is_disabled nvarchar(100),
    @IndexOptions nvarchar(MAX),
    @IndexColumnId int,
    @IsDescendingKey int,
    @IsIncludedColumn int,
    @TSQLScripCreationIndex nvarchar(MAX),
    @TSQLScripDisableIndex nvarchar(MAX),
    @object_id int,
    @has_key_constraint int,
    @has_index int,
    @has_foreign_key int,
    @has_stats int,
    @stats_id int,
    @FK_objectid int,
    @FK_name sysname,
    @UQ_objectid int,
    @UQ_name sysname,
    @UQ_index_id int,
    @has_unique_constraint int,
    @has_default_constraint int,
    @DatabaseCollation sysname,
    @IndexColumns nvarchar(MAX),
    @IncludedColumns nvarchar(MAX),
    @filter_definition nvarchar(MAX);

    — Temporary table for generated script
    CREATE TABLE #tempscriptstore
    (ScriptType varchar(20),
    script nvarchar(MAX));

    — Get dataabse default collation
    SELECT @DatabaseCollation = collation_name
    FROM sys.databases
    WHERE database_id = DB_ID();

    /************************************************************************************************************************************
    * Iterate over all the tables that have at least one colmun where collation doesn’t match the database default. *
    * Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics. *
    * (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero) *
    ************************************************************************************************************************************/
    DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
    SELECT t.object_id,
    OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
    OBJECT_NAME(t.object_id) AS tablename,
    COUNT(kc.object_id) AS has_key_constraint,
    COUNT(ic.index_id) AS has_index,
    COUNT(fk.constraint_object_id) AS has_foreign_key,
    COUNT(st.stats_id) AS has_stats,
    COUNT(uq.object_id) AS has_unique_constraint,
    COUNT(dc.object_id) AS has_default_constraint
    FROM sys.tables AS t
    INNER JOIN sys.columns AS c
    ON c.object_id = t.object_id
    AND c.collation_name <> @DatabaseCollation — Table needs to have columns with “wrong” collation
    INNER JOIN sys.types AS ty
    ON ty.system_type_id = c.system_type_id
    AND ty.name <> N’sysname’ — Exclusion retained from Philip C’s original script
    LEFT JOIN sys.index_columns AS ic — Find indexes on any of the affected columns
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
    LEFT JOIN sys.key_constraints AS kc — Find primary key constraints related to an affected index
    ON kc.parent_object_id = c.object_id
    AND kc.unique_index_id = ic.index_id
    AND kc.type = ‘PK’
    LEFT JOIN sys.key_constraints AS uq — Find unique constraints related to an affected index
    ON uq.parent_object_id = c.object_id
    AND uq.unique_index_id = ic.index_id
    AND uq.type = ‘UQ’
    LEFT JOIN sys.foreign_key_columns AS fk — Find foreign key constraints on any of the affected columns
    ON fk.parent_object_id = c.object_id
    AND fk.parent_column_id = c.column_id
    LEFT JOIN sys.stats_columns AS st — Find statistics on any of the affected columns
    ON st.object_id = c.object_id
    AND st.column_id = c.column_id
    AND st.stats_column_id <> 1 — Retained from Philip C’s original script, no idea why this is in the query
    LEFT JOIN sys.default_constraints AS dc — Find default constraints on any of the affected columns
    ON dc.object_id = c.default_object_id
    AND dc.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0 — Exclude Microsoft-shipped tables
    GROUP BY t.object_id;

    OPEN collationfix;
    FETCH NEXT FROM collationfix
    INTO @object_id,
    @SchemaName,
    @TableName,
    @has_key_constraint,
    @has_index,
    @has_foreign_key,
    @has_stats,
    @has_unique_constraint,
    @has_default_constraint;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN;

    /************************************************************************************************************************************
    *   Generates the code to update the colation for all affected columns in the table                                                 *
    ************************************************************************************************************************************/
    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'AlterCollation',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                + QUOTENAME(c.name) + ' '
                + CASE WHEN ty.name = N'ntext' or ty.name = N'text'
                           THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                       ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                      THEN N'MAX'
                                                  ELSE CASE WHEN ty.name IN (N'nvarchar', N'nchar')
                                                                THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                            ELSE CAST(c.max_length AS nvarchar(20))
                                                       END
                                             END + N') COLLATE ' + @DatabaseCollation
                  END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
    FROM        sys.columns AS c
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation;
    
    /************************************************************************************************************************************
    *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
    ************************************************************************************************************************************/
    IF @has_index > 0
    BEGIN;
        -- Process indexes on affected columns, one by one
        DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
        SELECT  ix.index_id,
                ix.name,
                CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                ix.type_desc,
                ix.filter_definition,
                CASE WHEN ix.is_padded = 1
                         THEN N'PAD_INDEX = ON, '
                     ELSE N'PAD_INDEX = OFF, '
                END + CASE WHEN ix.allow_page_locks = 1
                               THEN N'ALLOW_PAGE_LOCKS = ON, '
                           ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                      END + CASE WHEN ix.allow_row_locks = 1
                                     THEN N'ALLOW_ROW_LOCKS = ON, '
                                 ELSE N'ALLOW_ROW_LOCKS = OFF, '
                            END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                           THEN N'STATISTICS_NORECOMPUTE = ON, '
                                       ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                  END + CASE WHEN ix.ignore_dup_key = 1
                                                 THEN N'IGNORE_DUP_KEY = ON, '
                                             ELSE N'IGNORE_DUP_KEY = OFF, '
                                        END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                + CASE WHEN ix.fill_factor = 0
                           THEN CAST(100 AS nvarchar(3))
                       ELSE CAST(ix.fill_factor AS nvarchar(3))
                  END                            AS IndexOptions,
                ix.is_disabled,
                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
        FROM    sys.indexes AS ix
        WHERE   ix.object_id        = @object_id
        AND     ix.type                 <> 0 -- Exclude heaps
        AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
        AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.index_id = ix.index_id
                        AND         ic.object_id        = ix.object_id);
    
        OPEN CursorIndex;
        FETCH NEXT FROM CursorIndex
        INTO @index_id,
             @IndexName,
             @is_unique,
             @IndexTypeDesc,
             @filter_definition,
             @IndexOptions,
             @is_disabled,
             @FileGroupName;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- For each index, use a nested cursor to build lists of indexed and inclued columns
            SET @IndexColumns = N'';
            SET @IncludedColumns = N'';
    
            DECLARE CursorIndexColumn CURSOR FOR
            SELECT      c.name,
                        ic.is_descending_key,
                        ic.is_included_column
            FROM        sys.index_columns AS ic
            INNER JOIN  sys.columns       AS c
               ON       c.object_id = ic.object_id
               AND      c.column_id = ic.column_id
            WHERE       ic.object_id = @object_id
            AND         ic.index_id          = @index_id
            ORDER BY    ic.index_column_id;
    
            OPEN CursorIndexColumn;
            FETCH NEXT FROM CursorIndexColumn
            INTO @ColumnName,
                 @IsDescendingKey,
                 @IsIncludedColumn;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                IF @IsIncludedColumn = 0
                    SET @IndexColumns += QUOTENAME(@ColumnName)
                                         + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                ELSE
                    SET @IncludedColumns += QUOTENAME(@ColumnName) + N', ';
    
                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
            END;
    
            CLOSE CursorIndexColumn;
            DEALLOCATE CursorIndexColumn;
    
            -- Remove trailing comma
            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
            IF @IncludedColumns <> N''
                SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
    
            -- Generate DROP INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropIndex',
                    N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                    + N';';
    
            -- Generate CREATE INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'CreateIndex',
                    N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                    + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                    + CASE WHEN @IncludedColumns <> N''
                               THEN N'INCLUDE (' + @IncludedColumns + N')'
                           ELSE N''
                      END 
                    + CASE WHEN @filter_definition <> '' 
                                THEN N' WHERE ' + @filter_definition ELSE '' 
                      END 
                    + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
            -- Generate script to (re)disable disabled index after creating it
            IF @is_disabled = 1
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DisableIndex',
                        N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                        + QUOTENAME(@TableName) + N' DISABLE;';
    
            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @filter_definition,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
        END;
    
        CLOSE CursorIndex;
        DEALLOCATE CursorIndex;
    END;
    
    /************************************************************************************************************************************
    *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
    *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_key_constraint > 0
    BEGIN;
        -- Find columns and other metadata for primary key
        SELECT      kc.object_id                                                                          AS constid,
                    kc.name                                                                               AS constraint_name,   -- PK name
                    QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                    ic.key_ordinal,
                    i.name                                                                                AS index_name,
                    i.type_desc                                                                           AS index_type,
                    QUOTENAME(fg.name)                                                                    AS filegroup_name,
                    N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                    + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                    + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                    + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
        INTO        #temp_pk
        FROM        sys.key_constraints AS kc
        INNER JOIN  sys.indexes         AS i
           ON       i.object_id      = kc.parent_object_id
           AND      i.is_primary_key = 1
        INNER JOIN  sys.index_columns   AS ic
           ON       ic.object_id     = i.object_id
           AND      ic.index_id      = i.index_id
        INNER JOIN  sys.columns         AS c
           ON       c.object_id      = ic.object_id
           AND      c.column_id      = ic.column_id
        INNER JOIN  sys.filegroups      AS fg
           ON       fg.data_space_id = i.data_space_id
        WHERE       kc.type     = 'PK'
        AND         kc.parent_object_id = @object_id;
    
        -- Generate DROP CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                'DropPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                + QUOTENAME(constraint_name) + N';'
        FROM    #temp_pk;
    
        -- Generate CREATE CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                'AddPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                         FROM       #temp_pk
                         ORDER BY   key_ordinal
                        FOR XML PATH('')),
                        1,
                        2,
                        N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
        FROM    #temp_pk AS p;
    
        DROP TABLE #temp_pk;
    END;
    
    /************************************************************************************************************************************
    *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
    *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_foreign_key > 0
    BEGIN;
        -- Process foreign key constraints on affected columns, one by one
        DECLARE foreignkeycursor CURSOR FOR
        SELECT  fk.object_id,
                fk.name
        FROM    sys.foreign_keys AS fk
        WHERE   fk.parent_object_id = @object_id
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.foreign_key_columns AS fkc
                        INNER JOIN  sys.columns             AS c
                           ON       c.object_id       = fkc.parent_object_id
                           AND      c.column_id       = fkc.parent_column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types               AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       fkc.parent_object_id = fk.parent_object_id
                        AND         fkc.constraint_object_id     = fk.object_id);
    
        OPEN foreignkeycursor;
        FETCH NEXT FROM foreignkeycursor
        INTO @FK_objectid,
             @FK_name;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for foreign key
            SELECT      fk.object_id                                      AS constid,
                        fk.name                                           AS constraint_name,   -- FK name
                        fkc.constraint_column_id                          AS column_ordinal,
                        QUOTENAME(c1.name)                                AS key_col,
                        QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                        + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                        QUOTENAME(c2.name)                                AS ref_col,
                        CASE WHEN fk.delete_referential_action <> 0 -- No action
                                 THEN N' ON DELETE ' + fk.delete_referential_action_desc
                             ELSE N''
                        END                                               AS delete_cascade,
                        CASE WHEN fk.update_referential_action <> 0 -- No action
                                 THEN N' ON UPDATE ' + fk.update_referential_action_desc
                             ELSE N''
                        END                                               AS update_cascade,
                        fk.is_not_trusted,
                        fk.is_disabled
            INTO        #temp_fk
            FROM        sys.foreign_keys        AS fk
            INNER JOIN  sys.foreign_key_columns AS fkc
               ON       fkc.parent_object_id     = fk.parent_object_id
               AND      fkc.constraint_object_id = fk.object_id
            INNER JOIN  sys.columns             AS c1
               ON       c1.object_id             = fkc.parent_object_id
               AND      c1.column_id             = fkc.parent_column_id
            INNER JOIN  sys.columns             AS c2
               ON       c2.object_id             = fkc.referenced_object_id
               AND      c2.column_id             = fkc.referenced_column_id
            WHERE       fk.object_id = @FK_objectid;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@FK_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                    + STUFF(
                      (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N') REFERENCES ' + ref_table + N'('
                    + STUFF(
                      (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N')' + delete_cascade + update_cascade + ';'
            FROM    #temp_fk;
    
            -- If constraint was disabled, add code to disable it again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'DisableForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_disabled = 1;
    
            -- If constraint was trusted, add code to force re-check so it's trudsted again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'CheckForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_not_trusted = 0;
    
            DROP TABLE #temp_fk;
    
            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
        END;
        CLOSE foreignkeycursor;
        DEALLOCATE foreignkeycursor;
    END;
    
    /*
    

    — DROP and CREATE statistics for manual statistics has been disabled for now
    /************************************************************************************************************************************
    * If the column has statistics that aren’t part of an index this creates the drop and recreate scripts *
    ************************************************************************************************************************************/
    IF @has_stats > 0
    AND @has_index = 0
    BEGIN
    DECLARE stats_cursor CURSOR FOR
    SELECT sc.stats_id
    FROM sys.stats_columns AS sc
    JOIN sys.stats AS s
    ON s.object_id = sc.object_id
    AND s.stats_id = sc.stats_id
    AND s.user_created = 1
    WHERE sc.object_id = @object_id
    AND sc.column_id = @Collation_columnid;
    OPEN stats_cursor;
    FETCH NEXT FROM stats_cursor
    INTO @stats_id;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    –Create DROP Statistics Statement
    INSERT INTO #tempscriptstore
    (ScriptType,
    script)
    SELECT ‘DropStatistics’,
    ‘DROP STATISTICS ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + ‘.’
    + QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ + QUOTENAME(s.name)
    FROM sys.stats AS s
    WHERE s.object_id = @object_id
    AND s.stats_id = @stats_id;

            --Building the CREATE statistics statement
    
            --Obtaining all the information
            SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                    QUOTENAME(s.name)                                                                        AS st_name,
                    QUOTENAME(c.name)                                                                        AS st_column,
                    sc.object_id,
                    sc.stats_id,
                    sc.stats_column_id
            INTO    #temp_stats
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.stats_id  = sc.stats_id
              AND   s.object_id = sc.object_id
            JOIN    sys.columns       AS c
              ON    c.object_id = sc.object_id
              AND   c.column_id = sc.column_id
            WHERE   sc.object_id = @object_id
            AND     sc.stats_id      = @stats_id;
            WITH
            cte AS
                (SELECT DISTINCT ts.st_table,
                                 ts.st_name,
                                 SUBSTRING((SELECT      ',' + st_column
                                            FROM        #temp_stats
                                            WHERE       stats_id = ts.stats_id
                                            ORDER BY    stats_column_id ASC
                                           FOR XML PATH('')),
                                           2,
                                           99999) AS st_col_list
                 FROM   #temp_stats AS ts)
            --Constructing the statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'AddStatistics',
                    'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
            FROM    cte;
            DROP TABLE #temp_stats;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
        END;
        CLOSE stats_cursor;
        DEALLOCATE stats_cursor;
    END;
    

    */

    /************************************************************************************************************************************
    *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
    ************************************************************************************************************************************/
    IF @has_unique_constraint > 0
    BEGIN;
        -- Process unique constraints on affected columns, one by one
        DECLARE uniquecursor CURSOR FOR
        SELECT  kc.object_id,
                kc.name,
                kc.unique_index_id
        FROM    sys.key_constraints AS kc
        WHERE   kc.parent_object_id = @object_id
        AND     kc.type                 = 'UQ'
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.object_id = kc.parent_object_id
                        AND         ic.index_id          = kc.unique_index_id);
    
        OPEN uniquecursor;
        FETCH NEXT FROM uniquecursor
        INTO @UQ_objectid,
             @UQ_name,
             @UQ_index_id;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for unique constraint
            SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                        ic.key_ordinal,
                        i.name                                                                                    AS index_name,
                        i.type_desc                                                                               AS index_type,
                        QUOTENAME(fg.name)                                                                        AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                        + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                        + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                        + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
            INTO        #temp_uq
            FROM        sys.indexes       AS i
            INNER JOIN  sys.index_columns AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns       AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups    AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       i.object_id    = @object_id
            AND         i.index_id             = @UQ_index_id
            AND         i.is_unique_constraint = 1;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_uq
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
            FROM    #temp_uq;
    
            DROP TABLE #temp_uq;
    
            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
        END;
        CLOSE uniquecursor;
        DEALLOCATE uniquecursor;
    END;
    
    
    /************************************************************************************************************************************
    *  If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table                *
    ************************************************************************************************************************************/
    IF @has_default_constraint > 0
    BEGIN
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'DropDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(dc.name) + ' '
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc 
           ON       dc.object_id = c.default_object_id 
           AND      dc.parent_column_id = c.column_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    
    
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'CreateDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
                    + dc.definition + ' FOR ' + QUOTENAME(c.name) 
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc on dc.object_id = c.default_object_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    End
    
    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint,
         @has_default_constraint;
    

    END;
    CLOSE collationfix;
    DEALLOCATE collationfix;

    /************************************************************************************************************************************
    * Returns all the created scripts in the correct order for running *
    * Also add GO in between some sections so the entire script can execute at once *
    ************************************************************************************************************************************/
    DECLARE @oddnum INT = 1;
    WITH ScriptInput
    AS (SELECT script,
    CASE WHEN ScriptType = ‘DropDefault’
    THEN 1
    WHEN ScriptType = ‘DropForeignKey’
    THEN 3
    WHEN ScriptType = ‘DropIndex’
    THEN 5
    WHEN ScriptType = ‘DropUniqueKey’
    THEN 7
    WHEN ScriptType = ‘DropPrimaryKey’
    THEN 9
    WHEN ScriptType = ‘DropStatistics’
    THEN 11
    WHEN ScriptType = ‘AlterCollation’
    THEN 13
    WHEN ScriptType = ‘AddPrimaryKey’
    THEN 15
    WHEN ScriptType = ‘AddUniqueKey’
    THEN 17
    WHEN ScriptType = ‘CreateIndex’
    THEN 19
    WHEN ScriptType = ‘AddStatistics’
    THEN 21
    WHEN ScriptType = ‘AddForeignKey’
    THEN 23
    WHEN ScriptType = ‘DisableForeignKey’
    THEN 25
    WHEN ScriptType = ‘CheckForeignKey’
    THEN 27
    WHEN ScriptType = ‘DisableIndex’
    THEN 29
    WHEN ScriptType = ‘CreateDefault’
    THEN 31
    ELSE 99
    END AS SortOrder
    FROM #tempscriptstore
    UNION ALL
    SELECT CASE WHEN n.num > 0
    THEN ‘GO’ + CHAR(13)+CHAR(10) + descrip
    ELSE descrip end,
    n.num
    FROM (VALUES (0, ‘—- Drop Default’),
    (2, ‘—- Drop Foreign Key’),
    (4, ‘—- Drop Index’),
    (6, ‘—- Drop Unique Key’),
    (8, ‘—- Drop Primary Key’),
    –(10, ‘—- Drop Statistics’),
    (12, ‘—- Alter Collation ‘),
    (14, ‘—- Add Primary Key ‘),
    (16, ‘—- Add Unique Key ‘),
    (18, ‘—- Create Index ‘),
    –(20, ‘—- Add Statistics ‘),
    (22, ‘—- Add Foreign Key ‘),
    (24, ‘—- Disable Foreign Key ‘),
    (26, ‘—- Check Foreign Key ‘),
    (28, ‘—- Disable Index ‘),
    (30, ‘—- Create Default ‘),
    (32, ‘—- End of script’)
    ) AS n (num, descrip) )
    SELECT ScriptInput.script
    FROM ScriptInput
    ORDER BY ScriptInput.SortOrder;

    DROP TABLE #tempscriptstore;

    Reply
  • One more try to get formatting to work. Also updated revision history…

    BEGIN
    /*******************************************************************************
    *
    * Created 2017-06-16 By Philip C
    *
    * This script will check individual columns collations and check it against the
    * database default collation, where they are different it will create the scripts
    * required to drop all the objects dependant on the column, change the collation
    * to the database default and then recreate the dependant objects.
    * Some of the code has been reused from stuff found online the majority from
    * Jayakumaur R who created scripts to drop and recreate constraints
    *
    *
    * Modified 2019-01-11 by Hugo Kornelis
    *
    * Removed double checking of recreated constraints
    * Modified the order of the output to minimize number of index rebuilds (assuming primary key is most often clsutered index)
    * Fixed a bug caused by unique constraints overlapping the primary key constraint
    * Fixed a bug in detection of foreign key constraints to drop and recreate
    * Fixed bugs with recreating multi-column UNIQUE constraints
    *
    * Modified 2019-08-12 by Walter Charrière
    *
    * Add drop and create default constraints
    * Fixed bug with text columns adding length
    * Add Where calusule for filtered indexes
    *
    * Modified 2019-12-19 by Damon Clark
    *
    * Added fix from Erland for doubling length of nchar columns
    * Added quoting of column names in create index statements (for reserved words)
    *********************************************************************************/

    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;
    GO

    — Declare variables
    DECLARE @SchemaName sysname,
    @TableName sysname,
    @IndexName sysname,
    @ColumnName sysname,
    @index_id int,
    @is_unique nvarchar(100),
    @IndexTypeDesc nvarchar(100),
    @FileGroupName sysname,
    @is_disabled nvarchar(100),
    @IndexOptions nvarchar(MAX),
    @IndexColumnId int,
    @IsDescendingKey int,
    @IsIncludedColumn int,
    @TSQLScripCreationIndex nvarchar(MAX),
    @TSQLScripDisableIndex nvarchar(MAX),
    @object_id int,
    @has_key_constraint int,
    @has_index int,
    @has_foreign_key int,
    @has_stats int,
    @stats_id int,
    @FK_objectid int,
    @FK_name sysname,
    @UQ_objectid int,
    @UQ_name sysname,
    @UQ_index_id int,
    @has_unique_constraint int,
    @has_default_constraint int,
    @DatabaseCollation sysname,
    @IndexColumns nvarchar(MAX),
    @IncludedColumns nvarchar(MAX),
    @filter_definition nvarchar(MAX);

    — Temporary table for generated script
    CREATE TABLE #tempscriptstore
    (ScriptType varchar(20),
    script nvarchar(MAX));

    — Get dataabse default collation
    SELECT @DatabaseCollation = collation_name
    FROM sys.databases
    WHERE database_id = DB_ID();

    /************************************************************************************************************************************
    * Iterate over all the tables that have at least one colmun where collation doesn’t match the database default. *
    * Also checks for PRIMARY KEY, UNIQUE, and (referencing) FOREIGN KEY constraints, indexes, and manually created statistics. *
    * (Note that the counts are not accurate counts due to duplication, these should only be tested for zero or non-zero) *
    ************************************************************************************************************************************/
    DECLARE collationfix CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
    SELECT t.object_id,
    OBJECT_SCHEMA_NAME(t.object_id) AS schemaname,
    OBJECT_NAME(t.object_id) AS tablename,
    COUNT(kc.object_id) AS has_key_constraint,
    COUNT(ic.index_id) AS has_index,
    COUNT(fk.constraint_object_id) AS has_foreign_key,
    COUNT(st.stats_id) AS has_stats,
    COUNT(uq.object_id) AS has_unique_constraint,
    COUNT(dc.object_id) AS has_default_constraint
    FROM sys.tables AS t
    INNER JOIN sys.columns AS c
    ON c.object_id = t.object_id
    AND c.collation_name <> @DatabaseCollation — Table needs to have columns with “wrong” collation
    INNER JOIN sys.types AS ty
    ON ty.system_type_id = c.system_type_id
    AND ty.name <> N’sysname’ — Exclusion retained from Philip C’s original script
    LEFT JOIN sys.index_columns AS ic — Find indexes on any of the affected columns
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
    LEFT JOIN sys.key_constraints AS kc — Find primary key constraints related to an affected index
    ON kc.parent_object_id = c.object_id
    AND kc.unique_index_id = ic.index_id
    AND kc.type = ‘PK’
    LEFT JOIN sys.key_constraints AS uq — Find unique constraints related to an affected index
    ON uq.parent_object_id = c.object_id
    AND uq.unique_index_id = ic.index_id
    AND uq.type = ‘UQ’
    LEFT JOIN sys.foreign_key_columns AS fk — Find foreign key constraints on any of the affected columns
    ON fk.parent_object_id = c.object_id
    AND fk.parent_column_id = c.column_id
    LEFT JOIN sys.stats_columns AS st — Find statistics on any of the affected columns
    ON st.object_id = c.object_id
    AND st.column_id = c.column_id
    AND st.stats_column_id <> 1 — Retained from Philip C’s original script, no idea why this is in the query
    LEFT JOIN sys.default_constraints AS dc — Find default constraints on any of the affected columns
    ON dc.object_id = c.default_object_id
    AND dc.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0 — Exclude Microsoft-shipped tables
    GROUP BY t.object_id;

    OPEN collationfix;
    FETCH NEXT FROM collationfix
    INTO @object_id,
    @SchemaName,
    @TableName,
    @has_key_constraint,
    @has_index,
    @has_foreign_key,
    @has_stats,
    @has_unique_constraint,
    @has_default_constraint;

    WHILE (@@FETCH_STATUS = 0)
    BEGIN;

    /************************************************************************************************************************************
    *   Generates the code to update the colation for all affected columns in the table                                                 *
    ************************************************************************************************************************************/
    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'AlterCollation',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ALTER COLUMN '
                + QUOTENAME(c.name) + ' '
                + CASE WHEN ty.name = N'ntext' or ty.name = N'text'
                           THEN ty.name + N' COLLATE ' + @DatabaseCollation + ' '
                       ELSE ty.name + N'(' + CASE WHEN c.max_length = -1
                                                      THEN N'MAX'
                                                  ELSE CASE WHEN ty.name IN (N'nvarchar', N'nchar')
                                                                THEN CAST(c.max_length / 2 AS nvarchar(20))
                                                            ELSE CAST(c.max_length AS nvarchar(20))
                                                       END
                                             END + N') COLLATE ' + @DatabaseCollation
                  END + CASE WHEN c.is_nullable = 1 THEN N' NULL;' ELSE N' NOT NULL;' END
    FROM        sys.columns AS c
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation;
    
    /************************************************************************************************************************************
    *   If the table has affected indexes, this creates the drop and recreate index scripts                                             *
    ************************************************************************************************************************************/
    IF @has_index > 0
    BEGIN;
        -- Process indexes on affected columns, one by one
        DECLARE CursorIndex CURSOR LOCAL FORWARD_ONLY READ_ONLY STATIC FOR
        SELECT  ix.index_id,
                ix.name,
                CASE WHEN ix.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END,
                ix.type_desc,
                ix.filter_definition,
                CASE WHEN ix.is_padded = 1
                         THEN N'PAD_INDEX = ON, '
                     ELSE N'PAD_INDEX = OFF, '
                END + CASE WHEN ix.allow_page_locks = 1
                               THEN N'ALLOW_PAGE_LOCKS = ON, '
                           ELSE N'ALLOW_PAGE_LOCKS = OFF, '
                      END + CASE WHEN ix.allow_row_locks = 1
                                     THEN N'ALLOW_ROW_LOCKS = ON, '
                                 ELSE N'ALLOW_ROW_LOCKS = OFF, '
                            END + CASE WHEN INDEXPROPERTY(ix.object_id, ix.name, 'IsStatistics') = 1
                                           THEN N'STATISTICS_NORECOMPUTE = ON, '
                                       ELSE N'STATISTICS_NORECOMPUTE = OFF, '
                                  END + CASE WHEN ix.ignore_dup_key = 1
                                                 THEN N'IGNORE_DUP_KEY = ON, '
                                             ELSE N'IGNORE_DUP_KEY = OFF, '
                                        END + N'SORT_IN_TEMPDB = OFF, FILLFACTOR ='
                + CASE WHEN ix.fill_factor = 0
                           THEN CAST(100 AS nvarchar(3))
                       ELSE CAST(ix.fill_factor AS nvarchar(3))
                  END                            AS IndexOptions,
                ix.is_disabled,
                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
        FROM    sys.indexes AS ix
        WHERE   ix.object_id        = @object_id
        AND     ix.type                 <> 0 -- Exclude heaps
        AND     ix.is_primary_key       = 0 -- Exclude primary key constraints (handled separately)
        AND     ix.is_unique_constraint = 0 -- Exclude unique constraints (handled separately)
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.index_id = ix.index_id
                        AND         ic.object_id        = ix.object_id);
    
        OPEN CursorIndex;
        FETCH NEXT FROM CursorIndex
        INTO @index_id,
             @IndexName,
             @is_unique,
             @IndexTypeDesc,
             @filter_definition,
             @IndexOptions,
             @is_disabled,
             @FileGroupName;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- For each index, use a nested cursor to build lists of indexed and inclued columns
            SET @IndexColumns = N'';
            SET @IncludedColumns = N'';
    
            DECLARE CursorIndexColumn CURSOR FOR
            SELECT      c.name,
                        ic.is_descending_key,
                        ic.is_included_column
            FROM        sys.index_columns AS ic
            INNER JOIN  sys.columns       AS c
               ON       c.object_id = ic.object_id
               AND      c.column_id = ic.column_id
            WHERE       ic.object_id = @object_id
            AND         ic.index_id          = @index_id
            ORDER BY    ic.index_column_id;
    
            OPEN CursorIndexColumn;
            FETCH NEXT FROM CursorIndexColumn
            INTO @ColumnName,
                 @IsDescendingKey,
                 @IsIncludedColumn;
    
            WHILE (@@FETCH_STATUS = 0)
            BEGIN;
                IF @IsIncludedColumn = 0
                    SET @IndexColumns += QUOTENAME(@ColumnName)
                                         + CASE WHEN @IsDescendingKey = 1 THEN N' DESC, ' ELSE N' ASC, ' END;
                ELSE
                    SET @IncludedColumns += QUOTENAME(@ColumnName) + N', ';
    
                FETCH NEXT FROM CursorIndexColumn
                INTO @ColumnName,
                     @IsDescendingKey,
                     @IsIncludedColumn;
            END;
    
            CLOSE CursorIndexColumn;
            DEALLOCATE CursorIndexColumn;
    
            -- Remove trailing comma
            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
            IF @IncludedColumns <> N''
                SET @IncludedColumns = SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns) - 1);
    
            -- Generate DROP INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropIndex',
                    N'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)
                    + N';';
    
            -- Generate CREATE INDEX statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'CreateIndex',
                    N'CREATE ' + @is_unique + @IndexTypeDesc + N' INDEX ' + QUOTENAME(@IndexName) + N' ON '
                    + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @IndexColumns + N') '
                    + CASE WHEN @IncludedColumns <> N''
                               THEN N'INCLUDE (' + @IncludedColumns + N')'
                           ELSE N''
                      END 
                    + CASE WHEN @filter_definition <> '' 
                                THEN N' WHERE ' + @filter_definition ELSE '' 
                      END 
                    + N' WITH (' + @IndexOptions + N') ON ' + QUOTENAME(@FileGroupName) + N';';
            -- Generate script to (re)disable disabled index after creating it
            IF @is_disabled = 1
                INSERT INTO #tempscriptstore (ScriptType,
                                              script)
                SELECT  'DisableIndex',
                        N'ALTER INDEX ' + QUOTENAME(@IndexName) + N' ON ' + QUOTENAME(@SchemaName) + N'.'
                        + QUOTENAME(@TableName) + N' DISABLE;';
    
            FETCH NEXT FROM CursorIndex
            INTO @index_id,
                 @IndexName,
                 @is_unique,
                 @IndexTypeDesc,
                 @filter_definition,
                 @IndexOptions,
                 @is_disabled,
                 @FileGroupName;
        END;
    
        CLOSE CursorIndex;
        DEALLOCATE CursorIndex;
    END;
    
    /************************************************************************************************************************************
    *   If the table has an affected primary key constraint, this creates the drop and recreate constraint script                       *
    *   this has been taken and adapted from a script found online created by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_key_constraint > 0
    BEGIN;
        -- Find columns and other metadata for primary key
        SELECT      kc.object_id                                                                          AS constid,
                    kc.name                                                                               AS constraint_name,   -- PK name
                    QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END AS pk_col,
                    ic.key_ordinal,
                    i.name                                                                                AS index_name,
                    i.type_desc                                                                           AS index_type,
                    QUOTENAME(fg.name)                                                                    AS filegroup_name,
                    N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END + N', IGNORE_DUP_KEY = '
                    + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END + N', ALLOW_ROW_LOCKS = '
                    + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END + ', ALLOW_PAGE_LOCKS = '
                    + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END                       AS index_property
        INTO        #temp_pk
        FROM        sys.key_constraints AS kc
        INNER JOIN  sys.indexes         AS i
           ON       i.object_id      = kc.parent_object_id
           AND      i.is_primary_key = 1
        INNER JOIN  sys.index_columns   AS ic
           ON       ic.object_id     = i.object_id
           AND      ic.index_id      = i.index_id
        INNER JOIN  sys.columns         AS c
           ON       c.object_id      = ic.object_id
           AND      c.column_id      = ic.column_id
        INNER JOIN  sys.filegroups      AS fg
           ON       fg.data_space_id = i.data_space_id
        WHERE       kc.type     = 'PK'
        AND         kc.parent_object_id = @object_id;
    
        -- Generate DROP CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One DROP statement, even if there are multiple columns
                'DropPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                + QUOTENAME(constraint_name) + N';'
        FROM    #temp_pk;
    
        -- Generate CREATE CONSTRAINT statement
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                'AddPrimaryKey',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                + QUOTENAME(p.constraint_name) + N' PRIMARY KEY ' + CAST(p.index_type AS nvarchar(100)) + N' ('
                + STUFF((SELECT     N', ' + pk_col  -- This generates a comma-separated list of the columns, in order
                         FROM       #temp_pk
                         ORDER BY   key_ordinal
                        FOR XML PATH('')),
                        1,
                        2,
                        N'') + N')' + p.index_property + N' ON ' + p.filegroup_name + N';'
        FROM    #temp_pk AS p;
    
        DROP TABLE #temp_pk;
    END;
    
    /************************************************************************************************************************************
    *   If the table has a foreign key constraint on an affected column, this creates the drop and recreate constraint script           *
    *   this has been taken and adapted from a script found online cretaed by Jayakumaur R                                              *
    ************************************************************************************************************************************/
    IF @has_foreign_key > 0
    BEGIN;
        -- Process foreign key constraints on affected columns, one by one
        DECLARE foreignkeycursor CURSOR FOR
        SELECT  fk.object_id,
                fk.name
        FROM    sys.foreign_keys AS fk
        WHERE   fk.parent_object_id = @object_id
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.foreign_key_columns AS fkc
                        INNER JOIN  sys.columns             AS c
                           ON       c.object_id       = fkc.parent_object_id
                           AND      c.column_id       = fkc.parent_column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types               AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       fkc.parent_object_id = fk.parent_object_id
                        AND         fkc.constraint_object_id     = fk.object_id);
    
        OPEN foreignkeycursor;
        FETCH NEXT FROM foreignkeycursor
        INTO @FK_objectid,
             @FK_name;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for foreign key
            SELECT      fk.object_id                                      AS constid,
                        fk.name                                           AS constraint_name,   -- FK name
                        fkc.constraint_column_id                          AS column_ordinal,
                        QUOTENAME(c1.name)                                AS key_col,
                        QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + N'.'
                        + QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) AS ref_table,
                        QUOTENAME(c2.name)                                AS ref_col,
                        CASE WHEN fk.delete_referential_action <> 0 -- No action
                                 THEN N' ON DELETE ' + fk.delete_referential_action_desc
                             ELSE N''
                        END                                               AS delete_cascade,
                        CASE WHEN fk.update_referential_action <> 0 -- No action
                                 THEN N' ON UPDATE ' + fk.update_referential_action_desc
                             ELSE N''
                        END                                               AS update_cascade,
                        fk.is_not_trusted,
                        fk.is_disabled
            INTO        #temp_fk
            FROM        sys.foreign_keys        AS fk
            INNER JOIN  sys.foreign_key_columns AS fkc
               ON       fkc.parent_object_id     = fk.parent_object_id
               AND      fkc.constraint_object_id = fk.object_id
            INNER JOIN  sys.columns             AS c1
               ON       c1.object_id             = fkc.parent_object_id
               AND      c1.column_id             = fkc.parent_column_id
            INNER JOIN  sys.columns             AS c2
               ON       c2.object_id             = fkc.referenced_object_id
               AND      c2.column_id             = fkc.referenced_column_id
            WHERE       fk.object_id = @FK_objectid;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@FK_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement (always as ënabled, "not trusted"; status is changed later in script)
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(constraint_name) + N' FOREIGN KEY ('
                    + STUFF(
                      (SELECT       N', ' + key_col -- This generates a comma-separated list of the constrained columns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N') REFERENCES ' + ref_table + N'('
                    + STUFF(
                      (SELECT       N', ' + ref_col -- This generates a comma-separated list of the referencedcolumns, in order
                       FROM         #temp_fk
                       ORDER BY     column_ordinal
                      FOR XML PATH('')),
                      1,
                      2,
                      N'') + N')' + delete_cascade + update_cascade + ';'
            FROM    #temp_fk;
    
            -- If constraint was disabled, add code to disable it again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'DisableForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' NOCHECK CONSTRAINT '
                    + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_disabled = 1;
    
            -- If constraint was trusted, add code to force re-check so it's trudsted again
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'CheckForeignKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName)
                    + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(constraint_name) + N';'
            FROM    #temp_fk
            WHERE   is_not_trusted = 0;
    
            DROP TABLE #temp_fk;
    
            FETCH NEXT FROM foreignkeycursor
            INTO @FK_objectid,
                 @FK_name;
        END;
        CLOSE foreignkeycursor;
        DEALLOCATE foreignkeycursor;
    END;
    
    /*
    

    — DROP and CREATE statistics for manual statistics has been disabled for now
    /************************************************************************************************************************************
    * If the column has statistics that aren’t part of an index this creates the drop and recreate scripts *
    ************************************************************************************************************************************/
    IF @has_stats > 0
    AND @has_index = 0
    BEGIN
    DECLARE stats_cursor CURSOR FOR
    SELECT sc.stats_id
    FROM sys.stats_columns AS sc
    JOIN sys.stats AS s
    ON s.object_id = sc.object_id
    AND s.stats_id = sc.stats_id
    AND s.user_created = 1
    WHERE sc.object_id = @object_id
    AND sc.column_id = @Collation_columnid;
    OPEN stats_cursor;
    FETCH NEXT FROM stats_cursor
    INTO @stats_id;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    –Create DROP Statistics Statement
    INSERT INTO #tempscriptstore
    (ScriptType,
    script)
    SELECT ‘DropStatistics’,
    ‘DROP STATISTICS ‘ + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + ‘.’
    + QUOTENAME(OBJECT_NAME(s.object_id)) + ‘.’ + QUOTENAME(s.name)
    FROM sys.stats AS s
    WHERE s.object_id = @object_id
    AND s.stats_id = @stats_id;

            --Building the CREATE statistics statement
    
            --Obtaining all the information
            SELECT  QUOTENAME(OBJECT_SCHEMA_NAME(sc.object_id)) + '.' + QUOTENAME(OBJECT_NAME(sc.object_id)) AS st_table,
                    QUOTENAME(s.name)                                                                        AS st_name,
                    QUOTENAME(c.name)                                                                        AS st_column,
                    sc.object_id,
                    sc.stats_id,
                    sc.stats_column_id
            INTO    #temp_stats
            FROM    sys.stats_columns AS sc
            JOIN    sys.stats         AS s
              ON    s.stats_id  = sc.stats_id
              AND   s.object_id = sc.object_id
            JOIN    sys.columns       AS c
              ON    c.object_id = sc.object_id
              AND   c.column_id = sc.column_id
            WHERE   sc.object_id = @object_id
            AND     sc.stats_id      = @stats_id;
            WITH
            cte AS
                (SELECT DISTINCT ts.st_table,
                                 ts.st_name,
                                 SUBSTRING((SELECT      ',' + st_column
                                            FROM        #temp_stats
                                            WHERE       stats_id = ts.stats_id
                                            ORDER BY    stats_column_id ASC
                                           FOR XML PATH('')),
                                           2,
                                           99999) AS st_col_list
                 FROM   #temp_stats AS ts)
            --Constructing the statement
            INSERT INTO #tempscriptstore
                (ScriptType,
                 script)
            SELECT  'AddStatistics',
                    'CREATE STATISTICS ' + cte.st_name + ' ON ' + cte.st_table + '(' + cte.st_col_list + ')'
            FROM    cte;
            DROP TABLE #temp_stats;
            FETCH NEXT FROM stats_cursor
            INTO @stats_id;
        END;
        CLOSE stats_cursor;
        DEALLOCATE stats_cursor;
    END;
    

    */

    /************************************************************************************************************************************
    *   If the table has unique constraints on affected columns, this creates the drop and recreate scripts                             *
    ************************************************************************************************************************************/
    IF @has_unique_constraint > 0
    BEGIN;
        -- Process unique constraints on affected columns, one by one
        DECLARE uniquecursor CURSOR FOR
        SELECT  kc.object_id,
                kc.name,
                kc.unique_index_id
        FROM    sys.key_constraints AS kc
        WHERE   kc.parent_object_id = @object_id
        AND     kc.type                 = 'UQ'
        AND     EXISTS (SELECT      *   -- Has to constrain at least one column with wrong collation
                        FROM        sys.index_columns AS ic
                        INNER JOIN  sys.columns       AS c
                           ON       c.object_id       = ic.object_id
                           AND      c.column_id       = ic.column_id
                           AND      c.collation_name  <> @DatabaseCollation
                        INNER JOIN  sys.types         AS ty
                           ON       ty.system_type_id = c.system_type_id
                           AND      ty.name           <> N'sysname'
                        WHERE       ic.object_id = kc.parent_object_id
                        AND         ic.index_id          = kc.unique_index_id);
    
        OPEN uniquecursor;
        FETCH NEXT FROM uniquecursor
        INTO @UQ_objectid,
             @UQ_name,
             @UQ_index_id;
    
        WHILE (@@FETCH_STATUS = 0)
        BEGIN;
            -- Find columns and other metadata for unique constraint
            SELECT      QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N' ASC' END     AS uq_col,
                        ic.key_ordinal,
                        i.name                                                                                    AS index_name,
                        i.type_desc                                                                               AS index_type,
                        QUOTENAME(fg.name)                                                                        AS filegroup_name,
                        N' WITH (PAD_INDEX = ' + CASE WHEN i.is_padded = 0 THEN N'OFF' ELSE N'ON' END
                        + N', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 0 THEN N'OFF' ELSE N'ON' END
                        + N', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 0 THEN N'OFF' ELSE N'ON' END
                        + ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 0 THEN N'OFF)' ELSE N'ON)' END AS index_property
            INTO        #temp_uq
            FROM        sys.indexes       AS i
            INNER JOIN  sys.index_columns AS ic
               ON       ic.object_id     = i.object_id
               AND      ic.index_id      = i.index_id
            INNER JOIN  sys.columns       AS c
               ON       c.object_id      = ic.object_id
               AND      c.column_id      = ic.column_id
            INNER JOIN  sys.filegroups    AS fg
               ON       fg.data_space_id = i.data_space_id
            WHERE       i.object_id    = @object_id
            AND         i.index_id             = @UQ_index_id
            AND         i.is_unique_constraint = 1;
    
            -- Generate DROP CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  'DropUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N';';
    
            -- Generate CREATE CONSTRAINT statement
            INSERT INTO #tempscriptstore (ScriptType,
                                          script)
            SELECT  TOP (1) -- One CREATE statement, even if there are multiple columns
                    'AddUniqueKey',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD CONSTRAINT '
                    + QUOTENAME(@UQ_name) + N' UNIQUE ' + CAST(index_type AS nvarchar(100)) + N' ('
                    + STUFF((SELECT     N', ' + uq_col  -- This generates a comma-separated list of the columns, in order
                             FROM       #temp_uq
                             ORDER BY   key_ordinal
                            FOR XML PATH('')),
                            1,
                            2,
                            N'') + N')' + index_property + N' ON ' + filegroup_name + N';'
            FROM    #temp_uq;
    
            DROP TABLE #temp_uq;
    
            FETCH NEXT FROM uniquecursor
            INTO @UQ_objectid,
                 @UQ_name,
                 @UQ_index_id;
        END;
        CLOSE uniquecursor;
        DEALLOCATE uniquecursor;
    END;
    
    
    /************************************************************************************************************************************
    *  If the table has default constraints on affected columns, this creates the drop and recreate scripts in the table                *
    ************************************************************************************************************************************/
    IF @has_default_constraint > 0
    BEGIN
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'DropDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' DROP CONSTRAINT '
                    + QUOTENAME(dc.name) + ' '
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc 
           ON       dc.object_id = c.default_object_id 
           AND      dc.parent_column_id = c.column_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    
    
        INSERT INTO #tempscriptstore (ScriptType,
                                      script)
        SELECT      'CreateDefault',
                    N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' ADD DEFAULT '
                    + dc.definition + ' FOR ' + QUOTENAME(c.name) 
        FROM        sys.columns AS c 
        INNER JOIN  sys.default_constraints dc on dc.object_id = c.default_object_id 
        INNER JOIN  sys.types   AS ty
           ON       ty.system_type_id = c.system_type_id
           AND      ty.name           <> N'sysname'
        WHERE       c.object_id = @object_id
        AND         c.collation_name    <> @DatabaseCollation;
    End
    
    FETCH NEXT FROM collationfix
    INTO @object_id,
         @SchemaName,
         @TableName,
         @has_key_constraint,
         @has_index,
         @has_foreign_key,
         @has_stats,
         @has_unique_constraint,
         @has_default_constraint;
    

    END;
    CLOSE collationfix;
    DEALLOCATE collationfix;

    /************************************************************************************************************************************
    * Returns all the created scripts in the correct order for running *
    * Also add GO in between some sections so the entire script can execute at once *
    ************************************************************************************************************************************/
    DECLARE @oddnum INT = 1;
    WITH ScriptInput
    AS (SELECT script,
    CASE WHEN ScriptType = ‘DropDefault’
    THEN 1
    WHEN ScriptType = ‘DropForeignKey’
    THEN 3
    WHEN ScriptType = ‘DropIndex’
    THEN 5
    WHEN ScriptType = ‘DropUniqueKey’
    THEN 7
    WHEN ScriptType = ‘DropPrimaryKey’
    THEN 9
    WHEN ScriptType = ‘DropStatistics’
    THEN 11
    WHEN ScriptType = ‘AlterCollation’
    THEN 13
    WHEN ScriptType = ‘AddPrimaryKey’
    THEN 15
    WHEN ScriptType = ‘AddUniqueKey’
    THEN 17
    WHEN ScriptType = ‘CreateIndex’
    THEN 19
    WHEN ScriptType = ‘AddStatistics’
    THEN 21
    WHEN ScriptType = ‘AddForeignKey’
    THEN 23
    WHEN ScriptType = ‘DisableForeignKey’
    THEN 25
    WHEN ScriptType = ‘CheckForeignKey’
    THEN 27
    WHEN ScriptType = ‘DisableIndex’
    THEN 29
    WHEN ScriptType = ‘CreateDefault’
    THEN 31
    ELSE 99
    END AS SortOrder
    FROM #tempscriptstore
    UNION ALL
    SELECT CASE WHEN n.num > 0
    THEN ‘GO’ + CHAR(13)+CHAR(10) + descrip
    ELSE descrip end,
    n.num
    FROM (VALUES (0, ‘—- Drop Default’),
    (2, ‘—- Drop Foreign Key’),
    (4, ‘—- Drop Index’),
    (6, ‘—- Drop Unique Key’),
    (8, ‘—- Drop Primary Key’),
    –(10, ‘—- Drop Statistics’),
    (12, ‘—- Alter Collation ‘),
    (14, ‘—- Add Primary Key ‘),
    (16, ‘—- Add Unique Key ‘),
    (18, ‘—- Create Index ‘),
    –(20, ‘—- Add Statistics ‘),
    (22, ‘—- Add Foreign Key ‘),
    (24, ‘—- Disable Foreign Key ‘),
    (26, ‘—- Check Foreign Key ‘),
    (28, ‘—- Disable Index ‘),
    (30, ‘—- Create Default ‘),
    (32, ‘—- End of script’)
    ) AS n (num, descrip) )
    SELECT ScriptInput.script
    FROM ScriptInput
    ORDER BY ScriptInput.SortOrder;

    DROP TABLE #tempscriptstore;
    END

    Reply
  • And I forgot to thank Hugo for sharing this. Awesome script, and not trivial to come up with! Much appreciated!

    Reply
  • Darren Rhymer
    January 6, 2020 10:24

    Hi

    Great post, thanks to all those that contributed.

    I have installed Oracle EPPM on a server with default collation SQL_Latin1_General_CP1_CI_AS.
    Now the client wants BI Publisher installed. However BIP wont install unless the collation is SQL_Latin1_General_CP1_CS_AS.

    I’ve run the above script but it’s failing due to the column being changed having check constraints on it.

    I’ve had a stab at it but dont want to paste the whole set of code incase its wrong.

    In initial select for counting:
    LEFT JOIN sys.check_constraints AS cc — Find check_constraints on any of the affected columns
    ON cc.parent_object_id = c.object_id
    AND cc.parent_column_id = c.column_id

    Main block for check_constraints.

    /************************************************************************************************************************************
    * If the table has check constraints on affected columns, this creates the drop and recreate scripts in the table *
    ************************************************************************************************************************************/
    IF @has_check_constraint > 0
    BEGIN
    INSERT INTO #tempscriptstore (ScriptType,
    script)
    SELECT ‘DropCheck’,
    N’ALTER TABLE ‘ + QUOTENAME(@SchemaName) + N’.’ + QUOTENAME(@TableName) + N’ DROP CONSTRAINT ‘
    + QUOTENAME(cc.name) + ‘ ‘
    FROM sys.columns AS c
    INNER JOIN sys.check_constraints cc
    ON c.object_id = cc.parent_object_id
    AND cc.parent_column_id = c.column_id
    INNER JOIN sys.types AS ty
    ON ty.system_type_id = c.system_type_id
    AND ty.name <> N’sysname’
    WHERE c.object_id = @object_id
    AND c.collation_name <> @DatabaseCollation;

    INSERT INTO #tempscriptstore (ScriptType,
                                  script)
    SELECT      'CreateCheck',
                N'ALTER TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N' WITH CHECK ADD CONSTRAINT '
                + QUOTENAME(cc.name) + ' CHECK ' + cc.definition 
    FROM        sys.columns AS c 
    INNER JOIN  sys.check_constraints cc 
        ON      cc.parent_object_id = c.object_id
        AND     cc.parent_column_id = c.column_id
    INNER JOIN  sys.types   AS ty
       ON       ty.system_type_id = c.system_type_id
       AND      ty.name           <> N'sysname'
    WHERE       c.object_id = @object_id
    AND         c.collation_name    <> @DatabaseCollation
    group by    cc.name, cc.definition;
    

    End

    plus all the other bits that need changing.

    This looks good in my tests but i welcome any comments.

    Thanks

    Reply
  • Darren Rhymer
    January 6, 2020 11:19

    This is a good one !

    select distinct allow_flag from dbo.profpriv;
    N
    Y

    ALTER TABLE [dbo].[PROFPRIV] ALTER COLUMN [allow_flag] varchar(1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL;

    select distinct allow_flag from dbo.profpriv;
    N
    n
    Y

    consequently meaning

    ALTER TABLE [dbo].[PROFPRIV] WITH CHECK ADD CONSTRAINT [ck_profpriv_allow_flag] CHECK ([allow_flag]=’N’ OR [allow_flag]=’Y’)

    will fail !

    Reply
  • Hello Experts,
    I am trying to run the last updated script by Sir Damon Clark but finding me the objects already exists , i dropped the tables from tempdb or userdb but still throwing error message. Please help me out i am stuck company database has collation issue while loading the data from third party application .

    IF OBJECT_ID(‘tempdb.dbo.#temp_pk’, ‘U’) IS NOT NULL
    DROP TABLE #temp_pk;

    IF OBJECT_ID(‘tempdb.dbo.#temp_fk’, ‘U’) IS NOT NULL
    DROP TABLE #temp_fk;

    IF OBJECT_ID(‘tempdb.dbo.#temp_stats’, ‘U’) IS NOT NULL
    DROP TABLE #temp_stats;

    IF OBJECT_ID(‘tempdb.dbo.#temp_uq’, ‘U’) IS NOT NULL
    DROP TABLE #temp_uq;

    Msg 2714, Level 16, State 1, Line 724
    There is already an object named ‘#temp_pk’ in the database.
    Msg 2714, Level 16, State 1, Line 817
    There is already an object named ‘#temp_fk’ in the database.
    Msg 2714, Level 16, State 1, Line 982
    There is already an object named ‘#temp_stats’ in the database.
    Msg 2714, Level 16, State 1, Line 1203
    There is already an object named ‘#temp_uq’ in the database.

    Reply
  • Hello Experts,
    I am able to run the script of Sir Walter Charrière… Complex scripting i don’t have high level skills..
    Please help the below means all script run successfully?

    —- Drop Default
    GO —- Drop Foreign Key
    GO —- Drop Unique Key
    GO —- Drop Index
    GO —- Drop Primary Key
    GO —- Alter Collation
    GO —- Add Primary Key
    GO —- Add Unique Key
    GO —- Create Index
    GO —- Add Foreign Key
    GO —- Disable Foreign Key
    GO —- Check Foreign Key
    GO —- Disable Index
    GO —- Create Default
    GO —- End of script

    Reply
  • Hello Experts !
    I am very sorry to bother you all because i am very confused. I am able Alter all the collation of the tables set to new collation but the script does not change the collation of SQL Views any idea as it is still under old collation ?

    Reply
  • Hi, these scrit is a MASTER piece!!! but i get one error for user defined types of columns.

    Can anyone, change script to control that?

    There are some fields, that inherit gets database colaltion from database, and thes gets error for changing.

    If i try to change database collation first, i get same error :/

    Your Scripts error:
    –No se puede utilizar la cláusula COLLATE en tipos de datos definidos por el usuario.

    Databse change collation error:
    El objeto ‘funPresAnaliticos_Comprometido_STD’ de tipo objeto es dependiente de intercalación de base de datos. La intercalación de base de datos no se puede cambiar si un objeto enlazado a un esquema depende de ella. Quite las dependencias de la intercalación de base de datos y vuelva a intentar la operación.

    Reply
  • Can anyone paste the entire script in one box

    Reply
  • This script is awesome and helped us fix collation issues in our Atlassia Jira instance.
    I uploaded the SQL script to my Github repository: https://github.com/basroovers/sysops-scripts/blob/master/sql/change_collation.sql
    It includes all changes by Walter and Damon.

    Reply
  • My tests report columns used in a view or check constraints that are still present. I’ll see if I can add that to the script. I created a complete script myself that works a little different and ran into this one when looking for something else 😉 . Nice work!

    Reply
  • Useful script, thanks for sharing. I haven’t used in anger, but in testing hit a couple of things that I’ve fixed:

    1) Added a drop the temp table if it already exists at the beginning of the script in case your last run failed with an error:

    — Drop temptable if it already exists
    IF OBJECT_ID(N’tempdb..#tempscriptstore’) IS NOT NULL
    BEGIN
    DROP TABLE #tempscriptstore;
    END
    GO

    2) Replaced the line:

                FILEGROUP_NAME(ix.data_space_id) AS FileGroupName
    

    with a hack to get around the bug that FILEGOUP_NAME requires a smallint, but your value could be bigger than that data type:

                CASE WHEN ix.data_space_id > 32767
                    THEN 'PRIMARY'
                    ELSE FILEGROUP_NAME(ix.data_space_id)
                END  AS FileGroupName
    

    3) Replaced the line:

            SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
    

    to remove trailing commas where the length of line was actually zero:

            IF LEN(@IndexColumns) > 0 SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns) - 1);
    

    Hope that’s helpful to some 🙂

    Reply
  • wow great script
    many thanks.
    one question about the index creation – when it create the index the script not create it with the same column order. how i can tune the script to create the indexes with the same column order as they was in the source?

    THX

    Reply
  • another problem that i found in the script is when the column on the source is nchar(1) the script makes it nchar(2). can it be fixed?

    Reply
  • Great work!

    Reply
  • This script saved my day!

    There is one issue creating Foreign Key with ON DELETE SET NULL actions. The Script returns ON DELETE SET_NULL instead.

    I replaced this:
    CASE WHEN fk.delete_referential_action <> 0 — No action
    THEN N’ ON DELETE ‘ + fk.delete_referential_action_desc
    ELSE N”
    END AS delete_cascade,
    CASE WHEN fk.update_referential_action <> 0 — No action
    THEN N’ ON UPDATE ‘ + fk.update_referential_action_desc
    ELSE N”
    END AS update_cascade,
    With this:
    CASE WHEN fk.delete_referential_action <> 0 — No action
    THEN N’ ON DELETE ‘ + REPLACE(fk.delete_referential_action_desc,’‘,’ ‘)
    ELSE N”
    END AS delete_cascade,
    CASE WHEN fk.update_referential_action <> 0 — No action
    THEN N’ ON UPDATE ‘ + REPLACE(fk.update_referential_action_desc,’
    ‘,’ ‘)
    ELSE N”
    END AS update_cascade,

    Now it works like a charm.

    Reply
  • An additional issue I found is if you have SCHEMABOUND views… this does not take that into account and will throw an error. It needs to drop the schemabound views (and save off any indexes!) and then re-apply the schemabound views (and indexes!). I’m looking into how best to fix this issue, but haven’t come up with a good automated solution just yet.

    Reply
  • Sylvain Lambert
    February 8, 2022 22:33

    This saved me hours of manual works thanks. I had to additionnaly drop and recreate Computed columns that were using varchar fields in the compuation. I just had a few so I didn’t script it but be advise that you can’t change collation of fields used in a computed column.

    Reply
  • I’ve been using this script and it’s saved my clients many times. On this latest database it is filling up the data file and failing. I am running it on a 200 GB database and the script 105,000 lines long. I changed the database to SIMPLE mode and executed it. After a few hours, it had added an additional 60GB and filled up the data file. I made a change to the script so that it did the drop/alter/create for each table together rather than all drops/all alters/all creates, plus I changed the index create to build them with fill factor=100. I restored the database from scratch and ran this new script, checking back every so often. After maybe 2 hours the data file didn’t appear to be growing. Then by about 3 hours 20 min and 45,000 lines it had filled the data file again!

    I guess my question here is, why is the data file filling up even though I’m not adding any data or new indexes, and forced fill factor to 100?

    Reply
  • This script can be used to change the collation of a database but then u need to drop the check constraint first

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close