Zmiana zestawiania wszystkich kolumn wszystkich tabel w SQL Server

Więc znowu nie jestem zadowolony z odpowiedzi. Miałem za zadanie uaktualnić JIRA 6.4.x do JIRA Software 7.X i ja poszliśmy do tego konkretnego problemu z bazą danych i kolacją kolumn.

W SQL Server, Jeśli nie opuszczasz ograniczeń, takich jak klucz podstawowy lub klucz obcy, a nawet indeksy, skrypt podany powyżej jako odpowiedź w ogóle nie działa. Zmieni to jednak te bez tych właściwości. Jest to naprawdę problematyczne, ponieważ nie chcę ręcznie upuścić wszystkich ograniczenia i utworzyć je z powrotem. Ta operacja prawdopodobnie skończy się błędami. Z drugiej strony tworzenie skryptu automatyzującego zmianę może zająć wieki.

Więc znalazłem sposób, aby dokonać migracji po prostu za pomocą SQL Management Studio. Oto procedura:

• Zmień nazwę bazy danych przez coś innego. Na przykład, mój był "Jira", więc przemianowałem go "JiraTemp".
• Utwórz nową bazę danych o nazwie "Jira" i upewnij się, że ustawiłeś odpowiednią kolację. Wystarczy wybrać na stronie "Opcje" I zmienić zestawienie.
• Po utworzeniu wróć do "JiraTemp", kliknij prawym przyciskiem myszy " zadania - > Generuj Skrypty...".
  • Wybierz "Script whole database and all database objects".
  • Wybierz "Zapisz w nowym oknie zapytania" , a następnie wybierz "Zaawansowane"
  • zmień wartość "Script for Server Version" dla żądanej wartości
  • Włącz "uprawnienia na poziomie obiektu skryptu"," właściciel skryptu "i"indeksy pełnotekstowe skryptu"
  • zostawić wszystko inne jako jest lub Spersonalizuj go, jeśli chcesz.
• po wygenerowaniu Usuń sekcję "Utwórz bazę danych". Zastąp "JiraTemp" przez "Jira".
• Uruchom skrypt. Cała struktura bazy danych i jej uprawnienia są teraz replikowane do "Jira".
• zanim skopiujemy dane, musimy wyłączyć wszystkie ograniczenia. Wykonaj następujące polecenie w bazie danych "Jira": EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
• Teraz dane muszą zostać przesłane. Aby to zrobić, wystarczy kliknąć prawym przyciskiem myszy "JiraTemp", a następnie wybierz " zadania - > Eksportuj dane..."
  • Wybierz jako źródło danych i miejsce docelowe dostawcę OLE DB dla SQL Server.
  • źródłowa baza danych to "JiraTemp"
  • docelową bazą danych jest "Jira"
  • nazwa serwera jest technicznie taka sama dla source i destination (chyba że utworzyłeś bazę danych na innym serwerze).
  • wybierz "Kopiuj dane z jednej lub innych tabel lub widoków"
  • Zaznacz wszystkie tabele z wyjątkiem widoków. Następnie, gdy nadal podświetlony, kliknij " Edytuj Mappings". Zaznacz opcję "Enable identity insert"
  • Kliknij OK, Dalej, a następnie Zakończ
Transfer danych może chwilę potrwać. Po zakończeniu wykonaj następujące polecenie, aby ponownie włączyć wszystkie ograniczenia: exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Po zakończeniu ponownie uruchomiłem JIRA i moja baza danych była w porządku. Mam nadzieję, że pomoże to wielu ludziom!

Naprawiono problem z długością nvarchar i dodano 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

Aby to zrobić mam proste rozwiązanie, które działa dla mnie.

1. Utwórz nową bazę danych z nowym zestawieniem.
2. Eksportuj dane z oryginalnej bazy danych w trybie skryptowym.
3. zaimportuj zawartość do nowej bazy danych za pomocą skryptu (Zmień nazwę zdania USE na nową bazę danych).

Należy jednak zachować ostrożność, jeśli baza danych ma wyzwalacze, procedury lub podobne - bardziej niż tylko Dane i tabele.

Wprowadziłem małą zmianę w scenariuszu.

DECLARE @collate nvarchar(100);
DECLARE @table sysname;
DECLARE @schema sysname;
DECLARE @objectId int;
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 tbl.TABLE_SCHEMA,[name],obj.id
FROM sysobjects as obj
inner join INFORMATION_SCHEMA.TABLES as tbl
on obj.name = tbl.TABLE_NAME
WHERE OBJECTPROPERTY(obj.id, N'IsUserTable') = 1

OPEN local_table_cursor
FETCH NEXT FROM local_table_cursor
INTO @schema, @table, @objectId;

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 = @objectId
    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 ' + @schema + '.' + @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 @schema,@table,@objectId

END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor

GO

Naprawiono problem z długością nvarchar( include max), dołączono tekst i dodano 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

Uwaga: w przypadku, gdy musisz tylko zmienić jakiś konkretny warunek użycia, taki jak ten:

WHERE c.object_id = OBJECT_ID(@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    AND c.collation_name = 'collation to change'

Np. Nie the: AND c.collation_name <> @collate

W moim przypadku miałem poprawne / określone zestawienie niektórych kolumn i nie chciałem ich zmieniać.

Przepraszam za spóźnienie na imprezę, ale tutaj jest mój-dla tabeli ze schematem i śmiesznymi nazwami kolumn i tabel. Tak, miałem kilka z nich.

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'

Następujący skrypt będzie działał ze schematem tabeli wraz z najnowszymi typami, takimi jak (MAX), IMAGE, itd. Zmień typ sortowania zgodnie z potrzebami w tej linii (SET @collate = 'DATABASE_DEFAULT';)

SKRYPT SQL TUTAJ:

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

Zawsze preferuję czysty SQL tak:

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;

Używając powyższych wariacji kursora jako punktu wyjścia, poniższy skrypt po prostu wyświetli zestaw instrukcji UPDATE, aby ustawić DATABASE_DEFAULT, nie będzie faktycznie dokonywać aktualizacji.

Obsługuje schemat, pełny zestaw znaków i typów tekstu i zachowuje istniejące NULL / NOT NULL.

Planuję użyć wyjścia, aby znaleźć do instrukcji, które zawiodą w niższym środowisku, a następnie ręcznie dostosować skrypt wynikowy, aby upuścić i odtworzyć ograniczenia jako potrzebne.

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 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 = 'DATABASE_DEFAULT';

DECLARE local_table_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
ORDER BY s.[name], o.[name]

OPEN local_table_cursor FETCH NEXT FROM local_table_cursor INTO @schema,@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(@schema+'.'+@table) AND (t.Name LIKE '%char%' OR t.Name LIKE '%text%') 
    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'

        SET @sql =
        CASE 
            WHEN @data_type like '%text%' 
            THEN 'ALTER TABLE [' + @schema+ '].['+ @table + '] ALTER COLUMN [' + @column_name + '] ' + @data_type + ' COLLATE ' + @collate + ' ' + CASE WHEN @is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END
            ELSE 'ALTER TABLE [' + @schema+ '].['+ @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

        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 @schema, @table
END

CLOSE local_table_cursor
DEALLOCATE local_table_cursor
GO