固定长度问题nvarchar(包括max),包含文本并添加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
EXEC sp_executesql @sql
END TRY
BEGIN CATCH
PRINT 'ERROR (' + @table + '): Some index or constraint rely on the column ' + @column_name + '. No conversion possible.'
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
注意:如果您只需要更改某些特定排序规则,请使用以下条件:
WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%')
AND c.collation_name = 'collation to change'
例如:不要这样写:AND c.collation_name <> @collate
在我的情况下,我已经正确/指定了一些列的排序规则,并且不想更改它们。