可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
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?
回答1:
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
回答2:
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!
回答3:
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
回答4:
To do this I have an easy solution that works for me.
- Create a new database with the new collation.
- Export the data of the original data base in script mode.
- 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.
回答5:
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
回答6:
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.
回答7:
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;
回答8:
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'
回答9:
For anyone looking for this solution who is using PHPMyAdmin, there is an option at the bottom of the "Operations" tab (click on a db first); 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
