Подтвердить что ты не робот

Изменение сортировки всех столбцов всех таблиц в SQL Server

Я импортировал базу данных с некоторыми данными для сравнения с другой базой данных.

Целевая база данных имеет сортировку Latin1_General_CI_AS, а исходная база данных имеет SQL_Latin1_General_CP1_CI_AS.

Я изменил сортировку исходной базы данных в целом на Latin1_General_CI_AS с помощью SQL Server Management Studio. Но таблицы и столбцы внутри остаются со старой сортировкой.

Я знаю, что я могу изменить столбец, используя:

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

Но я должен сделать это для всех таблиц и всех столбцов внутри.

Прежде чем я начну писать хранимую процедуру, которая читает все таблицы и внутри всего столбца типа varchar и изменяет их в цикле курсора таблицы и столбца...

Кто-нибудь знает более простой способ или единственный способ сделать это с помощью script, проходящего через все таблицы в процедуре?

4b9b3361

ОТВЕТЫ

Ответ 1

Поскольку я не нашел подходящего способа, я написал script, чтобы сделать это, и я делюсь им здесь для тех, кто в нем нуждается. script выполняется через все пользовательские таблицы и собирает столбцы. Если тип столбца - это любой тип char, он пытается преобразовать его в заданное сопоставление.

Столбцы должны быть индексом и ограничением, чтобы это работало.

Если у кого-то еще есть лучшее решение, отправьте его!

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

Итак, я снова не удовлетворен ответом. Мне было поручено обновить JIRA 6.4.x до JIRA Software 7.x, и я перешел к этой конкретной проблеме с сопоставлением базы данных и столбцов.

В SQL Server, если вы не отбрасываете ограничения, такие как первичный ключ или внешний ключ или даже индексы, script, указанный выше в качестве ответа, вообще не работает. Однако он изменит те, у кого нет этих свойств. Это действительно проблематично, потому что я не хочу вручную удалять все ограничения и создавать их обратно. Вероятно, эта операция может закончиться ошибками. С другой стороны, создание script автоматизации изменения может занять много времени.

Итак, я нашел способ сделать миграцию просто с помощью SQL Management Studio. Здесь процедура:

  • Переименуйте базу данных чем-то другим. Например, моя была "Джира", поэтому я переименовал ее в "JiraTemp" .
  • Создайте новую базу данных с именем "Jira" и не забудьте установить правильную сортировку. Просто выберите страницу "Параметры" и измените порядок сортировки.
  • После создания вернитесь к "JiraTemp" , щелкните правой кнопкой мыши "Задачи → Сгенерировать скрипты...".
    • Выберите "Script целая база данных и все объекты базы данных".
    • Выберите "Сохранить в новом окне запроса", затем выберите "Дополнительно"
    • Измените значение "Script для версии сервера" для желаемого значения
    • Включить "Script Разрешения на уровне объекта", "Script Владелец" и "Script Полнотекстовые индексы"
    • Оставьте все остальное как есть или настройте его, если хотите.
  • После создания удалите раздел "CREATE DATABASE". Замените "JiraTemp" на "Jira" .
  • Запустите script. Вся структура базы данных и разрешения базы данных теперь реплицируются на "Jira" .
  • Перед копированием данных нам необходимо отключить все ограничения. Выполните следующую команду, чтобы сделать это в базе данных "Jira" : EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
  • Теперь данные должны быть переданы. Для этого просто щелкните правой кнопкой мыши "JiraTemp" , затем выберите "Задачи → Экспорт данных...".
    • Выберите в качестве источника данных и назначения поставщик OLE DB для SQL Server.
    • Исходная база данных - "JiraTemp"
    • База данных назначения - "Jira"
    • Имя сервера технически одинаково для источника и адресата (кроме случаев, когда вы создали базу данных на другом сервере).
    • Выберите "Копировать данные из той или иной таблицы или представлений"
    • Выберите все таблицы, кроме представлений. Затем, когда все еще выделено, нажмите "Редактировать сопоставление". Установите флажок "Включить вставку для идентификации"
    • Нажмите "ОК", "Далее", затем "Готово"
  • Передача данных может занять некоторое время. После завершения выполните следующую команду, чтобы включить все ограничения: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

После завершения, я перезапустил JIRA, и моя сортировка базы данных была в порядке. Надеюсь, это поможет многим людям!

Ответ 3

Проблема с фиксированной длиной nvarchar и добавлена ​​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

Для этого я получил легкое решение, которое работает для меня.

  • Создайте новую базу данных с новой сортировкой.
  • Экспортировать данные исходной базы данных в режиме script.
  • Импортируйте содержимое в новую базу данных с помощью script (переименуйте предложение USE в новую базу данных).

Тем не менее, вам нужно проявлять осторожность, если у вашей базы данных есть триггеры, процедуры или подобные материалы больше, чем данные и таблицы.

Ответ 5

Проблема с фиксированной длиной 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
            --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

Обратите внимание: в случае, когда вам просто нужно изменить какое-то определенное условие использования сортировки, например:

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

В моем случае у меня была правильная/заданная сортировка некоторых столбцов и не хотелось их изменять.