Change collations of all columns of all tables in SQL Server

I imported a database with some data to compare with another database.

The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS.

I did change the collation of the source database in general to Latin1_General_CI_AS using the SQL Server Management Studio. But the tables and columns inside remains with the old collation.

I know that I can change a column using:

ALTER TABLE [table]
ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS

But I have to do this for all tables and all columns inside.

Before I know start to write a stored procedure that reads all tables and inside all column of type varchar and change them in a table and column cursor loop...

Does anyone know an easier way or is the only way to do this with a script running through all tables in a procedure?

154059 次浏览
小开
最佳答案

As I did not find a proper way I wrote a script to do it and I'm sharing it here for those who need it. The script runs through all user tables and collects the columns. If the column type is any char type then it tries to convert it to the given collation.

Columns has to be index and constraint free for this to work.

If someone still has a better solution to this please post it!

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);


SET @collate = 'Latin1_General_CI_AS';


DECLARE local_table_cursor CURSOR FOR


SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1


OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table


WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE local_change_cursor CURSOR FOR


SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id


OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id


WHILE @@FETCH_STATUS = 0
BEGIN


IF (@max_length = -1) OR (@max_length > 4000) SET @max_length = 4000;


IF (@data_type LIKE '%char%')
BEGIN TRY
SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
PRINT @sql
EXEC sp_executesql @sql
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 @row_id, @column_name, @data_type, @max_length, @column_id


END


CLOSE local_change_cursor
DEALLOCATE local_change_cursor


FETCH NEXT FROM local_table_cursor
INTO @table


END


CLOSE local_table_cursor
DEALLOCATE local_table_cursor


GO
小开

Fixed length problem nvarchar and added NULL/NOT NULL

DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
DECLARE @is_Nullable bit;
DECLARE @null nvarchar(25);


SET @collate = 'Latin1_General_CI_AS';


DECLARE local_table_cursor CURSOR FOR


SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1


OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table


WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE local_change_cursor CURSOR FOR


SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table)
ORDER BY c.column_id


OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


WHILE @@FETCH_STATUS = 0
BEGIN


IF (@max_length = -1) SET @max_length = 4000;
set @null=' NOT NULL'
if (@is_nullable = 1) Set @null=' NULL'
if (@Data_type='nvarchar') set @max_length=cast(@max_length/2 as bigint)
IF (@data_type LIKE '%char%')
BEGIN TRY
SET @sql = 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + rtrim(@column_name) + '] ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) +  ') COLLATE ' + @collate + @null
PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR: Some index or contraint rely on the column ' + @column_name + '. No conversion possible.'
PRINT @sql
END CATCH


FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_Nullable


END


CLOSE local_change_cursor
DEALLOCATE local_change_cursor


FETCH NEXT FROM local_table_cursor
INTO @table


END


CLOSE local_table_cursor
DEALLOCATE local_table_cursor


GO
小开

To do this I have an easy solution that works for me.

  1. Create a new database with the new collation.
  2. Export the data of the original data base in script mode.
  3. Import the content to the new database using the script (rename the USE sentence to the new database).

However, you need to exercise caution if your database has triggers, procedures or similar - more that just data and tables.

小开

So here I am, once again, not satisfied with the answer. I was tasked to upgrade JIRA 6.4.x to JIRA Software 7.x and I went to that particular problem with the database and column collation.

In SQL Server, if you do not drop constrains such as primary key or foreign key or even indexes, the script provided above as an answer doesn't work at all. It will however change those without those properties. This is really problematic, because I don't want to manually drop all constrains and create them back. That operation could probably ends up with errors. On the other side, creating a script automating the change could take ages to make.

So I found a way to make the migration simply by using SQL Management Studio. Here's the procedure:

  • Rename the database by something else. By example, mine's was "Jira", so I renamed it "JiraTemp".
  • Create a new database named "Jira" and make sure to set the right collation. Simply select the page "Options" and change the collation.
  • Once created, go back to "JiraTemp", right click it, "Tasks -> Generate Scripts...".
    • Select "Script entire database and all database objects".
    • Select "Save to new query window", then select "Advanced"
    • Change the value of "Script for Server Version" for the desired value
    • Enable "Script Object-Level Permissions", "Script Owner" and "Script Full-Text Indexes"
    • Leave everything else as is or personalize it if you wish.
  • Once generated, delete the "CREATE DATABASE" section. Replace "JiraTemp" by "Jira".
  • Run the script. The entire database structure and permissions of the database is now replicated to "Jira".
  • Before we copy the data, we need to disable all constrains. Execute the following command to do so in the database "Jira": EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
  • Now the data needs to be transferred. To do so, simply right click "JiraTemp", then select "Tasks -> Export Data..."
    • Select as data source and destination the OLE DB Provider for SQL Server.
    • Source database is "JiraTemp"
    • Destination database is "Jira"
    • The server name is technically the same for source and destination (except if you've created the database on another server).
    • Select "Copy data from one or another tables or views"
    • Select all tables except views. Then, when still highlighted, click on "Edit Mappings". Check "Enable identity insert"
    • Click OK, Next, then Finish
  • Data transfer can take a while. Once finished, execute the following command to re enable all constrains: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Once completed, I've restarted JIRA and my database collation was in order. Hope it helps a lot of people!

小开

Fixed length problem nvarchar (include max), included text and added NULL/NOT NULL.

USE [put your database name here];


begin tran


DECLARE @collate nvarchar(100);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);


SET @collate = 'Latin1_General_CI_AS';


DECLARE local_table_cursor CURSOR FOR


SELECT [name]
FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY [name]


OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @table


WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE local_change_cursor CURSOR FOR


SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, col.CHARACTER_MAXIMUM_LENGTH
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%')
AND c.collation_name <> @collate
ORDER BY c.column_id


OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


WHILE @@FETCH_STATUS = 0
BEGIN


set @max_length_str = @max_length
IF (@max_length = -1) SET @max_length_str = 'max'
IF (@max_length > 4000) SET @max_length_str = '4000'


BEGIN TRY
SET @sql =
CASE
WHEN @data_type like '%text%'
THEN 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
ELSE 'ALTER TABLE ' + @table + ' ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length_str + ') COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
END
--PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR (' + @table + '): Some index or constraint rely on the column ' + @column_name + '. No conversion possible.'
--PRINT @sql
END CATCH


FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


END


CLOSE local_change_cursor
DEALLOCATE local_change_cursor


FETCH NEXT FROM local_table_cursor
INTO @table


END


CLOSE local_table_cursor
DEALLOCATE local_table_cursor


commit tran


GO

Notice : in case when you just need to change some specific collation use condition like this :

WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%')
AND c.collation_name = 'collation to change'

e.g. NOT the : AND c.collation_name <> @collate

In my case, I had correct / specified collation of some columns and didn't want to change them.

小开

Sorry late to the party, but here is mine - cater for table with a schema and funny column and table names. Yes I had some of them.

SELECT
'ALTER TABLE [' +  TABLE_SCHEMA + '].[' + TABLE_NAME
+ '] ALTER COLUMN [' + COLUMN_NAME + '] ' + DATA_TYPE
+ '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(100))
+ ') COLLATE ' + 'Latin1_General_CI_AS'
+ CASE WHEN IS_NULLABLE = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
DATA_TYPE like '%char'
小开

Following script will work with table schema along with latest Types like (MAX), IMAGE, and etc. change your collation type according to your need on this line (SET @collate = 'DATABASE_DEFAULT';)

SQL SCRIPT HERE:

BEGIN
DECLARE @collate nvarchar(100);
declare @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length varchar(100);
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);


SET @collate = 'DATABASE_DEFAULT';


DECLARE tbl_cursor CURSOR FOR SELECT (s.[name])schemaName, (o.[name])[tableName]
FROM sysobjects sy
INNER JOIN sys.objects  o on o.name = sy.name
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1


OPEN tbl_cursor FETCH NEXT FROM tbl_cursor INTO @schema,@table


WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE tbl_cursor_changed CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id like OBJECT_ID(@schema+'.'+@table)
ORDER BY c.column_id




OPEN tbl_cursor_changed
FETCH NEXT FROM tbl_cursor_changed
INTO @row_id, @column_name, @data_type, @max_length, @column_id






WHILE @@FETCH_STATUS = 0
BEGIN
IF (@max_length = -1) SET @max_length = 'MAX';
IF (@data_type LIKE '%char%')
BEGIN TRY
SET @sql = 'ALTER TABLE ' +@schema+'.'+ @table + ' ALTER COLUMN ' + @column_name + ' ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) + ') COLLATE ' + @collate
print @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR:'
PRINT @sql
END CATCH


FETCH NEXT FROM tbl_cursor_changed
INTO @row_id, @column_name, @data_type, @max_length, @column_id


END


CLOSE tbl_cursor_changed
DEALLOCATE tbl_cursor_changed


FETCH NEXT FROM tbl_cursor
INTO @schema, @table


END


CLOSE tbl_cursor
DEALLOCATE tbl_cursor


PRINT 'Collation For All Tables Done!'
END
小开

I always prefer pure SQL so : 

SELECT 'ALTER TABLE [' + l.schema_n + '].['
+ l.table_name + '] ALTER COLUMN ['
+ l.column_name + '] ' + l.data_type + '('
+ Cast(l.new_max_length AS NVARCHAR(100))
+ ') COLLATE ' + l.dest_collation_name + ';',
l.schema_n,
l.table_name,
l.column_name,
l.data_type,
l.max_length,
l.collation_name
FROM   (SELECT Row_number()
OVER (
ORDER BY c.column_id) AS row_id,
Schema_name(o.schema_id)  schema_n,
ta.NAME                   table_name,
c.NAME                    column_name,
t.NAME                    data_type,
c.max_length,
CASE
WHEN c.max_length = -1
OR ( c.max_length > 4000 ) THEN 4000
ELSE c.max_length
END                       new_max_length,
c.column_id,
c.collation_name,
'French_CI_AS'            dest_collation_name
FROM   sys.columns c
INNER JOIN sys.tables ta
ON c.object_id = ta.object_id
INNER JOIN sys.objects o
ON c.object_id = o.object_id
JOIN sys.types t
ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
WHERE  1 = 1
AND c.collation_name = 'SQL_Latin1_General_CP1_CI_AS'
--'French_CI_AS'-- ALTER DONE YET OLD VALUE :'SQL_Latin1_General_CP1_CI_AS'
) l
ORDER  BY l.column_id;
小开

I made a little change on the script.

DECLARE @collate nvarchar(100);
DECLARE @table sysname;
DECLARE @schema sysname;
DECLARE @objectId int;
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);
DECLARE @is_Nullable bit;
DECLARE @null nvarchar(25);


SET @collate = 'Latin1_General_CI_AS';


DECLARE local_table_cursor CURSOR FOR


SELECT tbl.TABLE_SCHEMA,[name],obj.id
FROM sysobjects as obj
inner join INFORMATION_SCHEMA.TABLES as tbl
on obj.name = tbl.TABLE_NAME
WHERE OBJECTPROPERTY(obj.id, N'IsUserTable') = 1


OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @schema, @table, @objectId;


WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR
SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, c.max_length
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = @objectId
ORDER BY c.column_id


OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


WHILE @@FETCH_STATUS = 0
BEGIN
IF (@max_length = -1) SET @max_length = 4000;
set @null=' NOT NULL'
if (@is_nullable = 1) Set @null=' NULL'
if (@Data_type='nvarchar') set @max_length=cast(@max_length/2 as bigint)
IF (@data_type LIKE '%char%')
BEGIN TRY
SET @sql = 'ALTER TABLE ' + @schema + '.' + @table + ' ALTER COLUMN [' + rtrim(@column_name) + '] ' + @data_type + '(' + CAST(@max_length AS nvarchar(100)) +  ') COLLATE ' + @collate + @null
PRINT @sql
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR: Some index or contraint rely on the column ' + @column_name + '. No conversion possible.'
PRINT @sql
END CATCH


FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_Nullable


END


CLOSE local_change_cursor
DEALLOCATE local_change_cursor


FETCH NEXT FROM local_table_cursor
INTO @schema,@table,@objectId


END


CLOSE local_table_cursor
DEALLOCATE local_table_cursor


GO
小开

Using the cursor based variations above as a starting point, the script below will just output a set of UPDATE statements to set to DATABASE_DEFAULT, it won't actually do the UPDATES.

It supports schema, the full set of char and text types and retains the existing NULL / NOT NULL.

I plan to use the output to find to statements that fail in a lower environment and then manually adapt the resulting script to drop and recreate the constraints as needed.

DECLARE @collate nvarchar(100);
DECLARE @schema nvarchar(255);
DECLARE @table nvarchar(255);
DECLARE @column_name nvarchar(255);
DECLARE @column_id int;
DECLARE @data_type nvarchar(255);
DECLARE @max_length int;
DECLARE @max_length_str nvarchar(100);
DECLARE @is_nullable bit;
DECLARE @row_id int;
DECLARE @sql nvarchar(max);
DECLARE @sql_column nvarchar(max);


SET @collate = 'DATABASE_DEFAULT';


DECLARE local_table_cursor CURSOR FOR


SELECT (s.[name])schemaName, (o.[name])[tableName]
FROM sysobjects sy
INNER JOIN sys.objects  o on o.name = sy.name
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE OBJECTPROPERTY(sy.id, N'IsUserTable') = 1
ORDER BY s.[name], o.[name]


OPEN local_table_cursor FETCH NEXT FROM local_table_cursor INTO @schema,@table


WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE local_change_cursor CURSOR FOR


SELECT ROW_NUMBER() OVER (ORDER BY c.column_id) AS row_id
, c.name column_name
, t.Name data_type
, col.CHARACTER_MAXIMUM_LENGTH
, c.column_id
, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN INFORMATION_SCHEMA.COLUMNS col on col.COLUMN_NAME = c.name and c.object_id = OBJECT_ID(col.TABLE_NAME)
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID(@schema+'.'+@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%')
ORDER BY c.column_id


OPEN local_change_cursor
FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


WHILE @@FETCH_STATUS = 0
BEGIN


SET @max_length_str = @max_length
IF (@max_length = -1) SET @max_length_str = 'max'
IF (@max_length > 4000) SET @max_length_str = '4000'


SET @sql =
CASE
WHEN @data_type like '%text%'
THEN 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
ELSE 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + '(' + @max_length_str + ') COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
END
PRINT @sql


FETCH NEXT FROM local_change_cursor
INTO @row_id, @column_name, @data_type, @max_length, @column_id, @is_nullable


END


CLOSE local_change_cursor
DEALLOCATE local_change_cursor


FETCH NEXT FROM local_table_cursor
INTO @schema, @table
END


CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO
小开

The easiest way to change the collation of a database including all fields goes over merge replication:

  • Take a server with the target collation (server collation prop)
  • Create a merge publication on the old server
  • Add all products of the source database to the publication
  • Run the snapsoht agent and wait it is completed
  • Add a push subscription to your publication targeting the server with the nice collation
  • Initialize Subscription
  • Check in replication monitor and wait until agent ready
  • Delete Subsciption
  • Delete Publication

The following sql scipt creates a merge publication for your user tables The other steps I do in Managemet Studio, as also I create the script objects like stored procedures, views etc in a subsequents step with a separate scirpt.

ALTER PROCEDURE [dbo].[CreateMergePublication]
@PublicationName nvarchar(max) = N'Pubi'
AS BEGIN
SET NOCOUNT ON
BEGIN TRY




-- *** BEGIN BLL ***




declare @DBName nvarchar(max)
select top 1 @DBName = TABLE_CATALOG from INFORMATION_SCHEMA.TABLES
exec sp_replicationdboption @dbname = @DBName, @optname = N'merge publish', @value = N'true'


-- Mergeveröffentlichung wird hinzugefügt
declare @desc nvarchar(max) = N'Mergeveröffentlichung der ' + @dbname + '-Datenbank von Verleger ' + @@SERVERNAME
exec sp_addmergepublication
@publication = @PublicationName,
@description = @desc ,
@sync_mode = N'native',
@retention = 14,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_subdirectory = N'ftp',
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@dynamic_filters = N'false',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_synctoalternate = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@use_partition_groups = null,
@publication_compatibility_level = N'100RTM',
@replicate_ddl = 1,
@allow_subscriber_initiated_snapshot = N'false',
@allow_web_synchronization = N'false',
@allow_partition_realignment = N'true',
@retention_period_unit = N'days',
@conflict_logging = N'both',
@automatic_reinitialization_policy = 0




exec sp_addpublication_snapshot
@publication = @PublicationName,
@frequency_type = 4,
@frequency_interval = 14,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 1,
@frequency_subday_interval = 5,
@active_start_time_of_day = 500,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1


declare @schema nvarchar(max), @table nvarchar(max), @uniquename nvarchar(max)
declare cr cursor for
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'sys%' and TABLE_NAME not like 'ms%' and TABLE_NAME not like 'dtprop%'
order by TABLE_NAME
open cr
WHILE 1=1 BEGIN
FETCH cr INTO @schema, @table
IF @@FETCH_STATUS <> 0 BREAK
set @uniquename = @schema + @table


print @schema + '.' + @table + ' (' + @uniquename + ')'
exec sp_addmergearticle
@publication = @PublicationName,
@article = @uniquename,
@source_owner = @schema,
@source_object = @table,
@type = N'table',
@description = N'',
@creation_script = null,
@pre_creation_cmd = N'none',
@schema_option = 0x000000010C034FD1,
@identityrangemanagementoption = N'manual',
@destination_owner = @schema,
@force_reinit_subscription = 1,
@column_tracking = N'false',
@subset_filterclause = N'',
@vertical_partition = N'false',
@verify_resolver_signature = 1,
@allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true',
@check_permissions = 0,
@subscriber_upload_options = 0,
@delete_tracking = N'true',
@compensate_for_errors = N'false',
@stream_blob_columns = N'false',
@partition_options = 0


END


close cr
deallocate cr










-- *** END BLL ***


END TRY
BEGIN CATCH
IF CURSOR_STATUS('global','cr') >= 0
BEGIN
close cr
deallocate cr
END


DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT, @ErrorState INT;
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()
RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)




END CATCH;


END
小开

This answer is maybe a bit late but you could generate the statements as well. This will be much faster than using CURSOR.

select
'ALTER TABLE '+ tb.TABLE_NAME + ' ALTER COLUMN '+ cl.COLUMN_NAME + ' ' + DATA_TYPE +'('+ cast(CHARACTER_MAXIMUM_LENGTH as nchar(3)) + ') ' + 'COLLATE Latin1_General_CI_AS '
FROM [DATABASE].INFORMATION_SCHEMA.columns cl
left join [DATABASE].tables tb on tb.TABLE_NAME = cl.TABLE_NAME and tb.Table_Schema=cl.table_schema
left join  [DATABASE].INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.table_name = tb.table_name and kc.TABLE_SCHEMA = cl.TABLE_SCHEMA  and kc.column_name=cl.COLUMN_NAME
WHERE DATA_TYPE in ('nvarchar','nchar')

Hope this can helps somebody.

小开

I've used the following query to generate collation update queries on each field of a table:

SELECT
[query] =
'ALTER TABLE '+ c.TABLE_SCHEMA +
'.[' + c.TABLE_NAME +
'] ALTER COLUMN [' + c.COLUMN_NAME +
'] nvarchar(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50))) +
') COLLATE Persian_100_CI_AI_SC_UTF8'
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT JOIN sys.computed_columns  AS cc  ON cc.[name] = c.COLUMN_NAME
WHERE c.TABLE_CATALOG = 'Your_Table_Name' AND DATA_TYPE = 'nvarchar' AND cc.[object_id] IS NULL
AND c.TABLE_NAME NOT IN ('sysdiagrams')
ORDER BY c.TABLE_NAME

It's not work on Computed Columns


提交答案