WARNING: The script presented in this post was for a specific situation at a specific client. It was not intended to be complete, and it is not actively maintained. Several people have suggested improvements in the comments, and Erland Sommarskog has written a long article where he presents a more refined version. Please check which version works best for you, and test with care!
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;
31 Comments. Leave new
This is a great piece of work! Saved us trying to migrate databases into Azure SQL. Thank you very much.
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.
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;
— 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;
*/
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;
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;
— 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;
*/
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;
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!!!)
End
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’)
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;
— 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;
*/
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;
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;
— 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;
*/
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
And I forgot to thank Hugo for sharing this. Awesome script, and not trivial to come up with! Much appreciated!
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;
End
plus all the other bits that need changing.
This looks good in my tests but i welcome any comments.
Thanks
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 !
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.
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
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 ?
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.
Can anyone paste the entire script in one box
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.
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!
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:
with a hack to get around the bug that FILEGOUP_NAME requires a smallint, but your value could be bigger than that data type:
3) Replaced the line:
to remove trailing commas where the length of line was actually zero:
Hope that’s helpful to some 🙂
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
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?
Great work!
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.
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.
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.
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?
This script can be used to change the collation of a database but then u need to drop the check constraint first
Script still needs to take into consideration if indexes has any compression enabled.
Surjit … and all others who left comments or questions about this script.
Please see the note that I just added at the top of this post. The script I shared here is not maintained and not intended to be used without lots of care. THe version on Erland’s site is probably better – but do check and verify!
Hi – when I execute the script the only output I get is . I don’t get any scripts to change the collation.
script
—- Drop Default
GO
—- Drop Foreign Key
GO
—- Drop Index
GO
—- Drop Unique Key
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
Miles, please first make sure that you have read my article (see Hugo’s note on top), and that you are using the version of the script from my article. Then check that you are actually changing from one collation to another. If you still have problems drop me a line.