Álvaro García
Reputation: 19356
how to change the collate to all the columns of the database?
I would like to change the collation of all the columns of all the tables of my database. In stack overflow, I have found this script: (post)
SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General__CI_AI ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
GO
However, when I see the collation of the columns I see that the collation is the old collation.
The actual collation is AS, so I can have "ANIMAL" and "ÁNIMAL". When I execute the script, I don't get any error. I think that I would get an error because the new collation is AI. So this makes me think that the script does nothing.
How can I change the collation of all the columns of all my tables in the database?
Thanks.
Upvotes: 42
Views: 78318
Answers (10)
arda-a
Reputation: 31
While none of the other approaches worked for me, this article by Douglas P. Castilho provided me a complete solution. I am sharing it in case it helps anyone.
- Create backups of all databases.
- Stop the SQL Server Services on the server.
- Open a Command Prompt and Navigate to the Binn Directory, like
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn
- Execute the command below to apply a new collation.
sqlservr -m -T4022 -T3659 -q"[Enter Collation Name]"
- Start SQL Server Service again.
That's it.
Note: This is a solution for Server level collation not database level. It doesnt change a user database collation or any user table collation.
Sql Server has 4 level of collation
- Server and\or Instance level collation
- Database level (system database's collation are inherited from the server)
- table level collation
- column level collation
by default there are collation inheritance from top to bottom. If you dont want to use the default then you can set each level manually. If all columns of a table collation are same then there are single collation for the table
Upvotes: 3
fernan2
Reputation: 1
Part One
Note: This is not my solution, it is a compilation of comments posts that worked for me from this page https://sqlserverfast.com/blog/hugo/2019/01/collation-change-script/.
/*******************************************************************************
*
* 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)
*
* Modified 2022-11-14 by Fernan2
* Added Darren Rhymer check constraint drop and create
*
************************************************************************************/
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,
@has_check_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,
count(cc.object_id) as has_check_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
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
WHERE t.is_ms_shipped = 0 -- Exclude Microsoft-shipped tables
GROUP BY t.object_id
--order by 3
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,
@has_check_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;
Upvotes: 0
fernan2
Reputation: 1
Part two
Note: This is not my solution, it is a compilation comments posts that worked for me from this url https://sqlserverfast.com/blog/hugo/2019/01/collation-change-script/.
/************************************************************************************************************************************
* 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;
/*comment code
-- 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
/************************************************************************************************************************************
* 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
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,
@has_check_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 = 'DropCheck'
THEN 13
WHEN ScriptType = 'AlterCollation'
THEN 15
WHEN ScriptType = 'AddPrimaryKey'
THEN 17
WHEN ScriptType = 'AddUniqueKey'
THEN 19
WHEN ScriptType = 'CreateIndex'
THEN 21
WHEN ScriptType = 'AddStatistics'
THEN 23
WHEN ScriptType = 'AddForeignKey'
THEN 25
WHEN ScriptType = 'DisableForeignKey'
THEN 27
WHEN ScriptType = 'CheckForeignKey'
THEN 29
WHEN ScriptType = 'DisableIndex'
THEN 31
WHEN ScriptType = 'CreateDefault'
THEN 33
WHEN ScriptType = 'CreateCheck'
THEN 35
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, '--- Drop Check '),
(14, '--- Alter Collation '),
(16, '--- Add Primary Key '),
(18, '--- Add Unique Key '),
(20, '--- Create Index '),
--(22, '--- Add Statistics '),
(24, '--- Add Foreign Key '),
(26, '--- Disable Foreign Key '),
(28, '--- Check Foreign Key '),
(30, '--- Disable Index '),
(32, '--- Create Default '),
(34, '--- Create Check '),
(36, '--- End of script')
) AS n (num, descrip) )
SELECT ScriptInput.script
FROM ScriptInput
ORDER BY ScriptInput.SortOrder;
DROP TABLE #tempscriptstore;
Upvotes: 0
Shawn Kelly
Reputation: 1
Perhaps a simpler approach is using PowerShell and SMO, e.g.:
$db = Get-DbaDatabase -SqlInstance MyServer -Database MyDb
$db.Tables | % {
$t = $_
$t | % {
If ($_.Collation -eq 'SQL_Latin1_General_CP1_CI_AS') {
$_.Collation = 'Latin1_General_CI_AS'
}
}
}
Upvotes: 0
thephilipc
Reputation: 371
I've pulled together the script below from various different sources that drop the dependencies updates the collation then recreate the dependant objects. This solves the problem of updating the collation of columns that have dependencies (indexes, foreign key constraints, etc.)
/*******************************************************************************
*
* Created 2017-06-16 By Philip C
* Shared on: https://stackoverflow.com/questions/16730114/how-to-change-the-collate-to-all-the-columns-of-the-database/44587493#44587493
*
* 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
*
* Minor edit by Darren S to support SQL2019: Replaced AND ty.name !='sysname' with AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%')
*********************************************************************************/
SET ANSI_WARNINGS OFF;
GO
DECLARE @SchemaName VARCHAR(100);
DECLARE @TableName VARCHAR(256);
DECLARE @IndexName VARCHAR(256);
DECLARE @ColumnName VARCHAR(100);
DECLARE @is_unique VARCHAR(100);
DECLARE @IndexTypeDesc VARCHAR(100);
DECLARE @FileGroupName VARCHAR(100);
DECLARE @is_disabled VARCHAR(100);
DECLARE @IndexOptions VARCHAR(MAX);
DECLARE @IndexColumnId INT;
DECLARE @IsDescendingKey INT;
DECLARE @IsIncludedColumn INT;
DECLARE @TSQLScripCreationIndex VARCHAR(MAX);
DECLARE @TSQLScripDisableIndex VARCHAR(MAX);
DECLARE @Collation_objectid INT;
DECLARE @Collation_columnid INT;
DECLARE @Collation_constraint INT;
DECLARE @Collation_index INT;
DECLARE @Collation_foreign INT;
DECLARE @Collation_stats INT;
DECLARE @stats_id INT;
DECLARE @Collation_fkid INT;
DECLARE @Collation_unique INT;
DECLARE @DatabaseCollation VARCHAR(100);
CREATE TABLE #tempscriptstore (ScriptType VARCHAR(20),
script NVARCHAR(MAX));
SELECT @DatabaseCollation=collation_name
FROM sys.databases
WHERE database_id=DB_ID();
/************************************************************************************************************************************
* Generates a list of all the columns where their collation doesn't match the database default and the depenmdancies they have. *
************************************************************************************************************************************/
DECLARE collationfix CURSOR FOR
SELECT t.object_id, c.column_id, 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.columns c
INNER JOIN sys.tables t ON c.object_id=t.object_id
INNER JOIN sys.types ty ON c.system_type_id=ty.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id
LEFT JOIN sys.key_constraints kc 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 uq 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 fk ON fk.referenced_object_id=c.object_id AND fk.constraint_column_id=c.column_id
LEFT JOIN sys.stats_columns st ON st.object_id=c.object_id AND st.column_id=c.column_id AND st.stats_column_id !=1
WHERE t.is_ms_shipped=0 AND c.collation_name<>@DatabaseCollation AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%')
GROUP BY t.object_id, c.column_id;
OPEN collationfix;
FETCH NEXT FROM collationfix
INTO @Collation_objectid, @Collation_columnid, @Collation_constraint, @Collation_index, @Collation_foreign, @Collation_stats, @Collation_unique;
WHILE(@@FETCH_STATUS=0)BEGIN
/************************************************************************************************************************************
* Generates the code to update the columns colation *
************************************************************************************************************************************/
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AlterCollation', 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +QUOTENAME(t.name)+' ALTER COLUMN '+QUOTENAME(c.name)+' '+CASE WHEN ty.name='ntext' THEN ty.name+' COLLATE '+@DatabaseCollation+' ' ELSE ty.name+'('+CASE WHEN c.max_length=-1 THEN 'MAX' ELSE CASE WHEN ty.name='nvarchar' THEN CAST(c.max_length / 2 AS VARCHAR(20))ELSE CAST(c.max_length AS VARCHAR(20))END END+') COLLATE '+@DatabaseCollation+' ' END+CASE WHEN c.is_nullable=1 THEN 'NULL;' ELSE 'NOT NULL;' END
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id=t.object_id
INNER JOIN sys.types ty ON c.system_type_id=ty.system_type_id
LEFT JOIN sys.index_columns ic ON ic.object_id=c.object_id AND ic.column_id=c.column_id
WHERE t.is_ms_shipped=0 AND c.collation_name<>@DatabaseCollation AND (ty.Name LIKE '%char%' OR ty.Name LIKE '%text%') AND c.column_id=@Collation_columnid AND t.object_id=@Collation_objectid;
/************************************************************************************************************************************
* If the column is in an index this creates the drop and recreate index script *
************************************************************************************************************************************/
IF @Collation_index>0 BEGIN
DECLARE CursorIndex CURSOR FOR
SELECT DISTINCT SCHEMA_NAME(t.schema_id) [schema_name], t.name, ix.name, CASE WHEN ix.is_unique=1 THEN 'UNIQUE ' ELSE '' END, ix.type_desc, CASE WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END+CASE WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END+CASE WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END+CASE WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics')=1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END+CASE WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END+'SORT_IN_TEMPDB = OFF, FILLFACTOR ='+CASE WHEN ix.fill_factor=0 THEN CAST(100 AS VARCHAR(3))ELSE CAST(ix.fill_factor AS VARCHAR(3))END AS IndexOptions, ix.is_disabled, FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
JOIN sys.indexes ix ON t.object_id=ix.object_id
JOIN sys.columns c ON c.object_id=t.object_id
JOIN sys.index_columns ic ON ic.index_id=ix.index_id AND ic.column_id=c.column_id AND ic.object_id=t.object_id
WHERE ix.type>0 AND ix.is_primary_key=0 AND ix.is_unique_constraint=0
--AND schema_name(tb.schema_id)= @SchemaName
--AND tb.name=@TableName
AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams' AND c.column_id=@Collation_columnid AND t.object_id=@Collation_objectid AND ic.column_id=@Collation_columnid
ORDER BY SCHEMA_NAME(t.schema_id), t.name, ix.name;
OPEN CursorIndex;
FETCH NEXT FROM CursorIndex
INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName;
WHILE(@@fetch_status=0)BEGIN
DECLARE @IndexColumns VARCHAR(MAX);
DECLARE @IncludedColumns VARCHAR(MAX);
SET @IndexColumns='';
SET @IncludedColumns='';
DECLARE CursorIndexColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id=ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id=col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND(ix.is_primary_key=0 OR ix.is_unique_constraint=0)AND SCHEMA_NAME(tb.schema_id)=@SchemaName AND tb.name=@TableName AND ix.name=@IndexName
ORDER BY ixc.index_column_id;
OPEN CursorIndexColumn;
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn;
WHILE(@@fetch_status=0)BEGIN
IF @IsIncludedColumn=0
SET @IndexColumns=@IndexColumns+@ColumnName+CASE WHEN @IsDescendingKey=1 THEN ' DESC, ' ELSE ' ASC, ' END;
ELSE SET @IncludedColumns=@IncludedColumns+@ColumnName+', ';
FETCH NEXT FROM CursorIndexColumn
INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn;
END;
CLOSE CursorIndexColumn;
DEALLOCATE CursorIndexColumn;
SET @IndexColumns=SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns)-1);
SET @IncludedColumns=CASE WHEN LEN(@IncludedColumns)>0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns)-1)ELSE '' END;
-- print @IndexColumns
-- print @IncludedColumns
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'DropIndex', 'DROP INDEX '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+'.'+QUOTENAME(@IndexName)+';';
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'CreateIndex', 'CREATE '+@is_unique+@IndexTypeDesc+' INDEX '+QUOTENAME(@IndexName)+' ON '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+'('+@IndexColumns+') '+CASE WHEN LEN(@IncludedColumns)>0 THEN CHAR(13)+'INCLUDE ('+@IncludedColumns+')' ELSE '' END+CHAR(13)+'WITH ('+@IndexOptions+') ON '+QUOTENAME(@FileGroupName)+';';
IF @is_disabled=1
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT 'DisableIndex', 'ALTER INDEX '+QUOTENAME(@IndexName)+' ON '+QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName)+' DISABLE;';
FETCH NEXT FROM CursorIndex
INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @is_disabled, @FileGroupName;
END;
CLOSE CursorIndex;
DEALLOCATE CursorIndex;
END;
/************************************************************************************************************************************
* If the column has a 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 @Collation_constraint>0 BEGIN
-------------------------------------------------
--ALTER TABLE DROP PRIMARY KEY CONSTRAINT Queries
-------------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropPrimaryKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sys.key_constraints skc
WHERE type='PK' AND parent_object_id=@Collation_objectid;
---------------------------------------------------
--ALTER TABLE CREATE PRIMARY KEY CONSTRAINT Queries
---------------------------------------------------
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) AS pk_table, --PK table name
skc.object_id AS constid, QUOTENAME(skc.name) AS constraint_name, --PK name
QUOTENAME(iskcu.COLUMN_NAME)+CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END AS pk_col, iskcu.ORDINAL_POSITION, CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END AS index_unique_type, si.name AS index_name, si.type_desc AS index_type, QUOTENAME(fg.name) AS filegroup_name, 'WITH('+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END+','+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END+')' AS index_property
--,*
INTO #temp_pk
FROM sys.key_constraints skc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE iskcu ON skc.name=iskcu.CONSTRAINT_NAME
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id AND si.is_primary_key=1
INNER JOIN sys.index_columns sic ON si.object_id=sic.object_id AND si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg ON si.data_space_id=fg.data_space_id
WHERE skc.type='PK' AND iskcu.COLUMN_NAME=c.name AND skc.parent_object_id=@Collation_objectid
ORDER BY skc.parent_object_id, skc.name, ORDINAL_POSITION;
WITH cte AS (SELECT pk_table, constraint_name, index_type, SUBSTRING((SELECT ','+pk_col FROM #temp_pk WHERE constid=t.constid FOR XML PATH('')), 2, 99999) AS pk_col_list, index_unique_type, filegroup_name, index_property
FROM #temp_pk t)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddPrimaryKey', 'ALTER TABLE '+pk_table+' ADD CONSTRAINT '+constraint_name+' PRIMARY KEY '+CAST(index_type COLLATE DATABASE_DEFAULT AS VARCHAR(100))+' ('+pk_col_list+')'+index_property+' ON '+filegroup_name+''
FROM cte;
--dropping the temp tables
DROP TABLE #temp_pk;
END;
/************************************************************************************************************************************
* If the column has a foreign key constraint this creates the drop and recreate constraint script *
* this has been taken and adapted from a script found online cretaed by Jayakumaur R *
************************************************************************************************************************************/
IF @Collation_foreign>0 BEGIN
DECLARE foreignkeycursor CURSOR FOR
SELECT constraint_object_id
FROM sys.foreign_key_columns
WHERE referenced_object_id=@Collation_objectid AND referenced_column_id=@Collation_columnid;
OPEN foreignkeycursor;
FETCH NEXT FROM foreignkeycursor
INTO @Collation_fkid;
WHILE(@@FETCH_STATUS=0)BEGIN
---------------------------------------------
--ALTER TABLE DROP FOREIGN CONSTRAINT Queries
---------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropForeignKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid))+' DROP CONSTRAINT '+QUOTENAME(OBJECT_NAME(constid))
FROM sys.sysforeignkeys sfk
WHERE sfk.constid=@Collation_fkid;
------------------------------------------------
--ALTER TABLE CREATE FOREIGN CONSTRAINT Queries
------------------------------------------------
--Obtaining the necessary info from the sys tables
SELECT constid, QUOTENAME(OBJECT_NAME(constid)) AS constraint_name, CASE WHEN fk.is_not_trusted=1 THEN 'WITH NOCHECK' ELSE 'WITH CHECK' END AS trusted_status, QUOTENAME(OBJECT_SCHEMA_NAME(fkeyid))+'.'+QUOTENAME(OBJECT_NAME(fkeyid)) AS fk_table, QUOTENAME(c1.name) AS fk_col, QUOTENAME(OBJECT_SCHEMA_NAME(rkeyid))+'.'+QUOTENAME(OBJECT_NAME(rkeyid)) AS rk_table, QUOTENAME(c2.name) AS rk_col, CASE WHEN fk.delete_referential_action=1 AND fk.delete_referential_action_desc='CASCADE' THEN 'ON DELETE CASCADE ' ELSE '' END AS delete_cascade, CASE WHEN fk.update_referential_action=1 AND fk.update_referential_action_desc='CASCADE' THEN 'ON UPDATE CASCADE ' ELSE '' END AS update_cascade, CASE WHEN fk.is_disabled=1 THEN 'NOCHECK' ELSE 'CHECK' END AS check_status
--,sysfk.*,fk.*
INTO #temp_fk
FROM sys.sysforeignkeys sysfk
INNER JOIN sys.foreign_keys fk ON sysfk.constid=fk.object_id
INNER JOIN sys.columns c1 ON sysfk.fkeyid=c1.object_id AND sysfk.fkey=c1.column_id
INNER JOIN sys.columns c2 ON sysfk.rkeyid=c2.object_id AND sysfk.rkey=c2.column_id
WHERE sysfk.constid=@Collation_fkid
ORDER BY constid, sysfk.keyno
--building the column list for foreign/primary key tables
;
WITH cte AS (SELECT DISTINCT constraint_name, trusted_status, fk_table, SUBSTRING((SELECT ','+fk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')), 2, 99999) AS fk_col_list, rk_table, SUBSTRING((SELECT ','+rk_col FROM #temp_fk WHERE constid=c.constid FOR XML PATH('')), 2, 99999) AS rk_col_list, check_status, delete_cascade, update_cascade
FROM #temp_fk c)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddForeignKey', 'ALTER TABLE '+fk_table+' '+trusted_status+' ADD CONSTRAINT '+constraint_name+' FOREIGN KEY('+fk_col_list+') REFERENCES '+rk_table+'('+rk_col_list+')'+' '+delete_cascade+update_cascade+';'+' ALTER TABLE '+fk_table+' '+check_status+' CONSTRAINT '+constraint_name
FROM cte;
--dropping the temp tables
DROP TABLE #temp_fk;
FETCH NEXT FROM foreignkeycursor
INTO @Collation_fkid;
END;
CLOSE foreignkeycursor;
DEALLOCATE foreignkeycursor;
END;
/************************************************************************************************************************************
* If the column has statistics that aren't part of an index this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @Collation_stats>0 AND @Collation_index=0 BEGIN
DECLARE stats_cursor CURSOR FOR
SELECT sc.stats_id
FROM sys.stats_columns sc
JOIN sys.stats s ON s.object_id=sc.object_id AND s.stats_id=sc.stats_id AND s.user_created=1
WHERE sc.object_id=@Collation_objectid 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 s
WHERE s.object_id=@Collation_objectid 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 sc
JOIN sys.stats s ON s.stats_id=sc.stats_id AND s.object_id=sc.object_id
JOIN sys.columns c ON c.object_id=sc.object_id AND c.column_id=sc.column_id
WHERE sc.object_id=@Collation_objectid AND sc.stats_id=@stats_id;
WITH cte AS (SELECT DISTINCT st_table, 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 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 column has a unique constraint this creates the drop and recreate scripts *
************************************************************************************************************************************/
IF @Collation_unique>0 BEGIN
-------------------------------------------------
--ALTER TABLE DROP UNIQUE CONSTRAINT Queries
-------------------------------------------------
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'DropUniqueKey', 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id))+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sys.key_constraints skc
JOIN sys.index_columns ic ON ic.object_id=skc.parent_object_id AND ic.index_id=skc.unique_index_id
WHERE type='UQ' AND parent_object_id=@Collation_objectid AND ic.column_id=@Collation_columnid;
---------------------------------------------------
--ALTER TABLE CREATE UNIQUE CONSTRAINT Queries
---------------------------------------------------
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))+'.'+QUOTENAME(OBJECT_NAME(parent_object_id)) AS uq_table, --PK table name
skc.object_id AS constid, QUOTENAME(skc.name) AS constraint_name, --PK name
QUOTENAME(iskcu.COLUMN_NAME)+CASE WHEN sic.is_descending_key=1 THEN ' DESC' ELSE ' ASC' END AS uq_col, iskcu.ORDINAL_POSITION, CASE WHEN unique_index_id=1 THEN 'UNIQUE' ELSE '' END AS index_unique_type, si.name AS index_name, si.type_desc AS index_type, QUOTENAME(fg.name) AS filegroup_name, 'WITH('+' PAD_INDEX = '+CASE WHEN si.is_padded=0 THEN 'OFF' ELSE 'ON' END+','+' IGNORE_DUP_KEY = '+CASE WHEN si.ignore_dup_key=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_ROW_LOCKS = '+CASE WHEN si.allow_row_locks=0 THEN 'OFF' ELSE 'ON' END+','+' ALLOW_PAGE_LOCKS = '+CASE WHEN si.allow_page_locks=0 THEN 'OFF' ELSE 'ON' END+')' AS index_property
--,*
INTO #temp_uq
FROM sys.key_constraints skc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE iskcu ON skc.name=iskcu.CONSTRAINT_NAME
INNER JOIN sys.indexes si ON si.object_id=skc.parent_object_id AND si.is_unique=1
INNER JOIN sys.index_columns sic ON si.object_id=sic.object_id AND si.index_id=sic.index_id
INNER JOIN sys.columns c ON sic.object_id=c.object_id AND sic.column_id=c.column_id
INNER JOIN sys.filegroups fg ON si.data_space_id=fg.data_space_id
WHERE skc.type='UQ' AND iskcu.COLUMN_NAME=c.name AND skc.parent_object_id=@Collation_objectid AND c.column_id=@Collation_columnid
ORDER BY skc.parent_object_id, skc.name, ORDINAL_POSITION;
WITH cte AS (SELECT uq_table, constraint_name, index_type, SUBSTRING((SELECT ','+uq_col FROM #temp_uq WHERE constid=t.constid FOR XML PATH('')), 2, 99999) AS uq_col_list, index_unique_type, filegroup_name, index_property
FROM #temp_uq t)
--forming the ADD CONSTRAINT query
INSERT INTO #tempscriptstore(ScriptType, script)
SELECT DISTINCT 'AddUniqueKey', 'ALTER TABLE '+uq_table+' ADD CONSTRAINT '+constraint_name+' UNIQUE '+CAST(index_type COLLATE DATABASE_DEFAULT AS VARCHAR(100))+' ('+uq_col_list+')'+index_property+' ON '+filegroup_name+''
FROM cte;
--dropping the temp tables
DROP TABLE #temp_uq;
END;
FETCH NEXT FROM collationfix
INTO @Collation_objectid, @Collation_columnid, @Collation_constraint, @Collation_index, @Collation_foreign, @Collation_stats, @Collation_unique;
END;
CLOSE collationfix;
DEALLOCATE collationfix;
/************************************************************************************************************************************
* Returns all the created scripts in the correct order for running *
************************************************************************************************************************************/
SELECT DISTINCT script, CASE WHEN ScriptType='DropForeignKey' THEN 1
WHEN ScriptType='DropPrimaryKey' THEN 2
WHEN ScriptType='DropUniqueKey' THEN 3
WHEN ScriptType='DropIndex' THEN 4
WHEN ScriptType='DropStatistics' THEN 5
WHEN ScriptType='AlterCollation' THEN 6
WHEN ScriptType='CreateIndex' THEN 7
WHEN ScriptType='DisableIndex' THEN 8
WHEN ScriptType='AddStatistics' THEN 9
WHEN ScriptType='AddUniqueKey' THEN 10
WHEN ScriptType='AddPrimaryKey' THEN 11
WHEN ScriptType='AddForeignKey' THEN 12 ELSE 99 END AS [exec_order]
FROM #tempscriptstore
WHERE script !=''
ORDER BY exec_order ASC;
DROP TABLE #tempscriptstore;
Upvotes: 37
SmokeyTBear
Reputation: 129
For anyone looking for this solution who is using PHPMyAdmin, there is an option at the bottom of the "Database Operations" tab (click on a db first, then "operations" tab on the top toolbar); scroll down to the collation box, select a collation, check the two checkboxes(one appears when the first is checked) :
- Change all tables collations
- Change all tables columns collations
Appears to basically run a script like the ones posted; the console shows the final command executed:
ALTER TABLE `DB_NAME`.`LAST_TABLE_IN_DB`DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci
Upvotes: 1
Recep Selli
Reputation: 32
Before doing this operation please read this article first. https://www.mssqltips.com/sqlservertip/3221/how-to-change-the-collation-of-a-sql-server-column/
After reading above article and make sure you removed below references from your database.
- A computed column
- An index Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
- A CHECK constraint
- A FOREIGN KEY constraint
Hit the below script on your database after that don't forget to change also database collation.
DECLARE @collate nvarchar(100);
DECLARE @schema_name nvarchar(255);
DECLARE @table_name nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @is_nullable bit;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
SET @collate = 'SQL_Latin1_General_CP1_CI_AS';
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id) schemaname,
t.name table_name
, c.name column_name
, s.Name data_type
, c.max_length
, c.is_nullable
, c.column_id
FROM sys.tables t INNER JOIN
sys.columns c ON c.object_id=t.object_id INNER JOIN
sys.types s ON s.user_type_id=c.user_type_id
WHERE
(c.collation_name LIKE 'SQL_Latin1_General_CP1254_CI_AS' OR c.collation_name LIKE 'Turkish_CI_AS') AND
t.type like 'U'
AND t.name not like 'spt%'
AND t.name not like 'MSrep%'
AND c.collation_name is NOT NULL
order by schemaname
OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @schema_name,@table_name, @column_name, @data_type, @max_length,@is_nullable, @column_id
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @nullable nvarchar(255),
@length varchar(10);
IF @is_nullable = 0
BEGIN
SET @nullable = ' NOT NULL';
END
ELSE
BEGIN
SET @nullable = ' NULL';
END
IF @data_type IN ('nchar', 'nvarchar') AND @max_length != -1
BEGIN
SET @length= CAST(@max_length / 2 AS VARCHAR(10));
END
ELSE IF @data_type IN ('char', 'varchar') AND @max_length != -1
BEGIN
SET @length= CAST(@max_length AS VARCHAR(10));
END
ELSE IF @data_type IN ('nchar', 'nvarchar', 'char', 'varchar') AND @max_length = -1
BEGIN
SET @length= 'MAX';
END
ELSE
BEGIN
SET @length= CAST(@max_length AS VARCHAR(10));
END
BEGIN TRY
IF @schema_name <> 'dbo'
BEGIN
SET @sql = 'ALTER TABLE ['+ @schema_name +'].[' + @table_name + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @length + ') COLLATE ' + @collate + ''+ @nullable+' '
PRINT @sql
EXEC sp_executesql @sql
END
END TRY
BEGIN CATCH
PRINT 'ERROR: Some index or constraint rely on the column' + @column_name + '. No conversion possible.'
PRINT @sql
END CATCH
FETCH NEXT FROM local_change_cursor
INTO @schema_name,@table_name, @column_name, @data_type, @max_length,@is_nullable, @column_id
END
CLOSE local_change_cursor
DEALLOCATE local_change_cursor
END
GO
Upvotes: 1
Devart
Reputation: 121902
Try this one -
Query:
DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Output:
-------------------------------------------------- ------------------------------------------------------------------------------------------------------------------
[dbo].[CategoryType] -> CategoryTypeCD ALTER TABLE [dbo].[CategoryType] ALTER COLUMN [CategoryTypeCD] VARCHAR(20) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Employee] -> TabelNumber ALTER TABLE [dbo].[Employee] ALTER COLUMN [TabelNumber] VARCHAR(12) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[Supplement] -> WorkFactorCD ALTER TABLE [dbo].[Supplement] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> WorkFactorCD ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[Surcharge] -> Condition ALTER TABLE [dbo].[Surcharge] ALTER COLUMN [Condition] NVARCHAR(MAX) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkFactor] -> WorkFactorCD ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [WorkFactorCD] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkFactor] -> Name ALTER TABLE [dbo].[WorkFactor] ALTER COLUMN [Name] NVARCHAR(200) COLLATE Cyrillic_General_CI_AS NOT NULL
[dbo].[WorkOut] -> WorkShiftCD ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [WorkShiftCD] NVARCHAR(40) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> AbsenceCode ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [AbsenceCode] VARCHAR(50) COLLATE Cyrillic_General_CI_AS NULL
[dbo].[WorkOut] -> PaymentType ALTER TABLE [dbo].[WorkOut] ALTER COLUMN [PaymentType] CHAR(4) COLLATE Cyrillic_General_CI_AS NULL
Upvotes: 85
wajahat kaleem
Reputation: 21
Thanks all for suggestion, it help me lot while migrating my database from SQL Server 2008 R2 to 2014
NOTE: Some of the resulted query have invalid VARCHAR(-), I changed it into VARCHAR(MAX), and it is working fine.
Steps to change Collation:
- Run above query and get Alter table query
- Drop all indexes of tables and Table valued functions
- Run the generated query of step 1
- Create indexes of tables and table valued functions.
Upvotes: 2
David
Reputation: 1774
I'm posting an edit to the above answer, which addresses char and varchar lengths, as my previous edit looks like it's being rejected.
DECLARE @collate SYSNAME
SELECT @collate = 'Cyrillic_General_CS_AS'
SELECT
'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name
, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']
ALTER COLUMN [' + c.name + '] ' +
UPPER(t.name) +
CASE WHEN t.name NOT IN ('ntext', 'text')
THEN '(' +
CASE
WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN t.name IN ('char', 'varchar') AND c.max_length != -1
THEN CAST(c.max_length AS VARCHAR(10))
WHEN t.name IN ('nchar', 'nvarchar', 'char', 'varchar') AND c.max_length = -1
THEN 'MAX'
ELSE CAST(c.max_length AS VARCHAR(10))
END + ')'
ELSE ''
END + ' COLLATE ' + @collate +
CASE WHEN c.is_nullable = 1
THEN ' NULL'
ELSE ' NOT NULL'
END
FROM sys.columns c WITH(NOLOCK)
JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')
AND c.collation_name != @collate
AND o.[type] = 'U'
Upvotes: 8
Related Questions
- Change collations of all columns of all tables in SQL Server
- Collate column to SQL default collation
- Update Collation of all fields in database on the fly
- How to set collation of a column with SQL?
- How to make permanent change to the column collation?
- SQL Server: How to change collation of all columns of my select on the fly
- how do I reset collation for all columns in the database?
- SQL column collation change
- change collation on all objects in a database
- how can I change the collation of a data base?