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

Question

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

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

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

Есть ли способ обновить таблицы / столбцы со старым сопоставлением до нового сопоставления?

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

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

ОБНОВИТЬ

Спасибо за вашу помощь, ребята, наконец-то все заработало.

Для дальнейшего использования, вот мой последний скрипт:

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

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

33

sql-server-2008

Источник

user154186 10 дек '09 в 03:15

7 ответов

Решение

На всякий случай, если кто-то смотрит на это, использует 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%' )

11

Источник

user392970 15 июл '10 в 16:32

Как насчет:

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

5

Источник

user1215942 17 фев '12 в 10:14

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

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

Я не могу подчеркнуть это достаточно. BACKUP ВАША БАЗА ДАННЫХ. Мне пришлось использовать свою резервную копию три раза, чтобы выполнить эту задачу.

3

Источник

user530658 28 окт '12 в 15:22

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

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

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

2

Источник

user14454 10 дек '09 в 04:53

В дополнение к ответу Овайса Икбала, который переписал код edosoft и поместил его в цикл для выполнения фактических операторов T-SQL, вы можете получить сопоставление по умолчанию из базы данных

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

-- **************** BEGIN GET DB COLLATION ***********
DECLARE @collation NVARCHAR(128)
SELECT @collation = collation_name
FROM sys.databases WHERE database_id = DB_ID()

PRINT 'Default database collation: ' + @collation
PRINT ''
-- **************** END GET DB COLLATION *************

-- **************** BEGIN LOGIC **********************

1

Источник

user12364748 28 окт '20 в 15:50

Хорошо, я переписал код с помощью 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". Перестройте таблицу, для которой вы получаете сообщение об ошибке, и снова запустите приведенный выше скрипт.

ALTER TABLE [dbo].[YourTableName] REBUILD

0

Источник

user3582180 11 ноя '14 в 13:04

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

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

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

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

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

-3

Источник

user3798213 02 июл '14 в 14:44

Другие вопросы по тегам sql-server-2008

user135152 10 дек '09 в 03:27 2009-12-10 03:27 · Accepted Answer · 2009-12-10 03:27

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

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

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