Обновление сортировки всех полей в базе данных "на лету"

Недавно мы перенесли нашу базу данных с нашего сервера SQL Server 2005 на наш сервер SQL Server 2008. Все изменилось, но теперь мы находим, что у нас возникают конфликты сортировки. У старого сервера была другая сортировка с новым сервером.

Теперь наши таблицы, созданные до перемещения, являются одной сортировкой, а те, которые создаются после, являются еще одним сопоставлением.

Есть ли способ обновить таблицы/столбцы со старой сортировкой для новой сортировки?

Я понимаю, что настройка сортировки по умолчанию для базы данных/сервера не изменяет существующие таблицы (ссылка). Я действительно не хочу воссоздавать базу данных, если мне это не нужно.

Любая помощь действительно ценится.

UPDATE

Спасибо, что помогли, ребята, наконец-то заработали.

Для справки в будущем, вот мой последний script:

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_AS ' + 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

Вот сайт, содержащий script, на котором я основывался. Я должен был настроить его, чтобы он работал правильно.

Ответ 1

Вы можете изменить сортировку любых новых объектов, созданных в пользовательской базе данных, с помощью предложения COLLATE ALTER DATABASE. Этот оператор делает не изменение сортировки столбцов в любых существующих пользовательских таблицах. Их можно изменить с помощью предложения COLLATE ALTER TABLE.

Ссылка: Настройка и изменение сопоставления базы данных

Если слишком много столбцов, вы можете выполнить цикл через SYS.COLUMNS, чтобы применить оператор ALTER TABLE.

Ответ 2

На всякий случай, если кто-то смотрит на это с помощью SQL Server 2008, мне пришлось внести несколько изменений:

SELECT 'ALTER TABLE [' + sys.objects.name + '] ALTER COLUMN ['
+ sys.columns.name + '] ' + sys.types.name + 
    CASE sys.types.name
    WHEN 'text' THEN ' '
    ELSE
    '(' + RTRIM(CASE sys.columns.max_length
    WHEN -1 THEN 'MAX'
    ELSE CONVERT(CHAR,sys.columns.max_length)
    END) + ') ' 
    END

    + ' ' + ' COLLATE Latin1_General_BIN ' + CASE sys.columns.is_nullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
    FROM sys.columns , sys.objects , sys.types
    WHERE sys.columns.object_id = sys.objects.object_id
    AND sys.objects.TYPE = 'U'
    AND sys.types.system_type_id = sys.columns.system_type_id
    AND sys.columns.collation_name IS NOT NULL
    AND NOT ( sys.objects.NAME LIKE 'sys%' )
    AND NOT ( sys.types.name LIKE 'sys%' )

Ответ 3

Как насчет:

DECLARE @collation NVARCHAR(64)
SET @collation = 'Latin1_General_CI_AS'

SELECT
    'ALTER TABLE [' + TABLE_SCHEMA  + '].[' + TABLE_NAME + '] '
  + 'ALTER COLUMN [' + COLUMN_NAME + '] '
  + DATA_TYPE + '(' + CASE CHARACTER_MAXIMUM_LENGTH 
        WHEN -1 THEN 'MAX' 
        ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ') '
  + 'COLLATE ' + @collation + ' '
  + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
FROM INFORMATION_SCHEMA.columns
WHERE  COLLATION_NAME IS NOT NULL
AND COLLATION_NAME <> @collation

Ответ 4

Чтобы устранить эту проблему, вам понадобится гораздо больше энергии огня, чем это предусмотрено script. Я попробовал script и столкнулся с проблемами с зависимыми объектами, которые не удалось обновить: индексы, ключи и процедуры. Окончательное решение заняло всего 5 минут с этим приложением проекта кода. Приложение говорит, что это для Sql Server 2000, но я успешно использовал его с 2008 годом.

http://www.codeproject.com/Articles/12753/SQL-Server-2000-Collation-Changer

Я не могу это подчеркнуть. РЕЗЕРВИРОВАТЬ ВАШУ БАЗУ ДАННЫХ. Я должен был использовать свою резервную копию три раза для выполнения этой задачи.

Ответ 5

Один из вариантов заключается в использовании такой программы, как Red Gate SQL Compare (я уверен, что есть и другие). С его помощью вы можете сгенерировать script файлы для вашей схемы с включенной сортировкой (обязательно включите это в параметрах), затем выполните поиск/замену в файлах, обновляющих ее до новой сортировки, а затем повторно сравните их с вашей фактической базы данных.

В этот момент SQL Compare сможет применить эти изменения (или сохранить изменения в файле script, если вы предпочитаете), и ваши существующие столбцы все исправлены.

В теории вы могли бы все это сделать еще в течение пробного периода, хотя я бы посоветовал ему использовать хороший инструмент, чтобы упростить выполнение многих задач SQL!

Ответ 6

Хорошо, я переписал код edosoft и поставил его в цикле для выполнения реальных операторов T-SQL.

-- **************** BEGIN INPUT **********************
USE [YourDBName]

DECLARE @collation NVARCHAR(128)
-- enter you collation name below
SET @collation = N'Latin1_General_CI_AS'
-- **************** END INPUT ************************

-- **************** BEGIN LOGIC **********************
DECLARE @sqlCode VARCHAR(2048)

DECLARE myCursor CURSOR LOCAL FOR
    SELECT 'ALTER TABLE [' + sys.objects.name + '] 
        ALTER COLUMN ['+ sys.columns.name + '] ' + sys.types.name + 
        CASE sys.types.name
            WHEN 'text' THEN ' '
            WHEN 'ntext' THEN ' '
            ELSE '(' + RTRIM(
                CASE
                    WHEN sys.columns.max_length = -1 THEN 'MAX'
                    WHEN sys.columns.max_length > 4000 THEN 'MAX'
                    ELSE CONVERT(CHAR,sys.columns.max_length)
                END) + ')' 
        END
        + ' COLLATE ' + @collation + CASE sys.columns.is_nullable WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END
        FROM sys.columns , sys.objects , sys.types
        WHERE sys.columns.object_id = sys.objects.object_id
            AND sys.objects.TYPE = 'U'
            AND sys.types.system_type_id = sys.columns.system_type_id
            AND sys.columns.collation_name IS NOT NULL
            AND sys.columns.collation_name <> @collation
            AND NOT ( sys.objects.NAME LIKE 'sys%' )
            AND NOT ( sys.types.name LIKE 'sys%' )

OPEN myCursor
FETCH NEXT FROM myCursor INTO @sqlCode

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT 'Executing: ' + @sqlCode
    BEGIN TRY
        EXEC(@sqlCode);
        PRINT 'Done!' + CHAR(10)
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE() + CHAR(10)
    END CATCH
    FETCH NEXT FROM myCursor INTO @sqlCode
END

PRINT 'Finished!'
-- **************** END LOGIC **********************

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

ALTER TABLE [dbo].[YourTableName] REBUILD

Ответ 7

В коде не учитываются двойные байты NText, NChar и NVarchar. Если у вас есть NText, он не сработает, если Ntext (16) не сможет установить размер в NText.

Для NChar и NVarChar он удваивает длину, потому что он не может разделить размер на 2.

Еще одна необычная мелочь - это то, что для Nvarchar, по крайней мере, -1 не MAX, а 0.

Это очень уродливое взломание кода, просто чтобы проиллюстрировать проблемы:

ALTER TABLE [BlanketBruger] ALTER COLUMN [BrugerNavn] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BlanketBruger] ALTER COLUMN [BrugerFuldNavn] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketgruppe] ALTER COLUMN [GruppeNavn] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketSerie] ALTER COLUMN [SerieTitel] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [blanketSerie] ALTER COLUMN [SerieAlias] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [FormUse] ALTER COLUMN [HostName] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [value1] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [value2] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [ip] varchar(20)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [username] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [gruppenavn] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [scriptname] nvarchar(100)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BackendLog] ALTER COLUMN [querystring] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [useragent] nvarchar(400)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [sessionid] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BackendLog] ALTER COLUMN [htmlcontent] nvarchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [value1] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [value2] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [ip] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [querystring] nvarchar(1000)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [useragent] nvarchar(400)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [frontendlog] ALTER COLUMN [sessionid] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [log4net] ALTER COLUMN [Thread] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Level] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Logger] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Message] text   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [log4net] ALTER COLUMN [Exception] varchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [log4net] ALTER COLUMN [Server] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Server] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Thread] varchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Level] varchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [OioSamlLog] ALTER COLUMN [Message] varchar(MAX)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileContentIdentifier] nvarchar(50)   COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileContent] ntext   COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE [BlanketSubmitTemp] ALTER COLUMN [FileName] nvarchar(255)   COLLATE SQL_Latin1_General_CP1_CI_AS NULL