SQL Server에 있는 모든 테이블의 모든 열에 대한 데이터 정렬 변경
다른 데이터베이스와 비교할 데이터가 있는 데이터베이스를 가져왔습니다.
대상이에데정있습렬다니이터이가 있습니다.Latin1_General_CI_AS 소스 에는 " " "라는 이름이 있습니다.SQL_Latin1_General_CP1_CI_AS.
을 반적으로소데스의조다같변합다경니로 변경했습니다.Latin1_General_CI_ASSQL Server Management Studio는 SQL Server Management Studio입니다.그러나 내부의 테이블과 열은 이전 조합으로 남아 있습니다.
다음을 사용하여 열을 변경할 수 있습니다.
ALTER TABLE [table]
ALTER COLUMN [column] VARCHAR(100) COLLATE Latin1_General_CI_AS
하지만 저는 모든 테이블과 내부의 모든 열에 대해 이것을 해야 합니다.
전에 과 유형의 열 시작합니다.varchar테이블 및 열 커서 루프에서 변경...
절차의 모든 테이블에서 실행되는 스크립트로 이 작업을 수행하는 더 쉬운 방법을 아는 사람이 있습니까?
제대로 된 방법을 찾지 못해 대본을 작성했고 필요한 분들을 위해 공유하고 있습니다.스크립트는 모든 사용자 테이블을 통해 실행되고 열을 수집합니다.열 유형이 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
그래서 저는 여기서 다시 한 번 답에 만족하지 못합니다.JIRA 6.4.x를 JIRA Software 7.x로 업그레이드하는 작업을 맡았고 데이터베이스 및 열 조합과 관련된 특정 문제를 해결했습니다.
SQL Server에서 기본 키나 외부 키 또는 인덱스와 같은 제약 조건을 삭제하지 않으면 위에서 답변으로 제공한 스크립트가 전혀 작동하지 않습니다.그러나 이러한 속성이 없는 항목은 변경됩니다.모든 제약 조건을 수동으로 삭제하고 다시 만들고 싶지 않기 때문에 이것은 정말 문제가 됩니다.해당 작업이 오류로 끝날 수 있습니다.반면에 변경을 자동화하는 스크립트를 만드는 데는 시간이 오래 걸릴 수 있습니다.
그래서 SQL Management Studio를 사용하여 간단히 마이그레이션할 수 있는 방법을 찾았습니다.절차는 다음과 같습니다.
- 데이터베이스 이름을 다른 이름으로 변경합니다.예를 들어, 제 이름이 "지라"였기 때문에 "지라템프"라고 이름을 지었습니다.
- 이름이 "Jira"인 새 데이터베이스를 작성하고 올바른 조합을 설정해야 합니다."옵션" 페이지를 선택하고 정렬을 변경하기만 하면 됩니다.
- 생성된 후 "JiraTemp"로 돌아가 마우스 오른쪽 단추로 클릭합니다. "Tasks -> Generate Scripts...".
- 전체 데이터베이스 및 모든 데이터베이스 오브젝트 스크립팅을 선택합니다.
- 새 쿼리 창에 저장을 선택한 다음 "고급"을 선택합니다.
- Script for Server Version 값을 원하는 값으로 변경합니다.
- "스크립트 객체 수준 권한", "스크립트 소유자" 및 "스크립트 전체 텍스트 색인" 사용
- 다른 모든 것은 그대로 두거나 원하는 경우 개인화합니다.
- 생성되면 "CREATE DATABASE" 섹션을 삭제합니다."JiraTemp"를 "Jira"로 바꿉니다.
- 스크립트를 실행합니다.이제 데이터베이스의 전체 데이터베이스 구조와 권한이 "Jira"로 복제됩니다.
- 데이터를 복사하기 전에 모든 제약 조건을 해제해야 합니다.에서 합니다: "Jira"는 "Jira"입니다.
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" - 이제 데이터를 전송해야 합니다.이렇게 하려면 "JiraTemp"를 마우스 오른쪽 단추로 클릭한 다음 "Tasks -> Export Data(작업 -> 데이터 내보내기)를 선택합니다.."
- SQL 서버용 OLE DB 공급자를 데이터 원본 및 대상으로 선택합니다.
- 원본 데이터베이스가 "JiraTemp"입니다.
- 대상 데이터베이스가 "Jira"입니다.
- 서버 이름은 원본과 대상에 대해 기술적으로 동일합니다(다른 서버에 데이터베이스를 작성한 경우 제외).
- 하나 또는 다른 표 또는 보기에서 데이터 복사를 선택합니다.
- 뷰를 제외한 모든 테이블을 선택합니다.그런 다음 계속 강조 표시되면 "매핑 편집"을 클릭합니다.ID 삽입 사용을 선택합니다.
- 확인, 다음, 마침을 클릭합니다.
- 데이터 전송에는 시간이 걸릴 수 있습니다. 조건을 합니다.
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
완료되면 JIRA를 다시 시작했고 데이터베이스 수집 작업이 정상적으로 진행되었습니다.그것이 많은 사람들에게 도움이 되기를 바랍니다!
길이 문제 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
대본을 조금 수정했습니다.
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
이를 위해 저는 제게 맞는 쉬운 해결책을 가지고 있습니다.
- 새 데이터 집합을 사용하여 새 데이터베이스를 만듭니다.
- 스크립트 모드에서 원래 데이터베이스의 데이터를 내보냅니다.
- 스크립트를 사용하여 내용을 새 데이터베이스로 가져옵니다(USE 문장 이름을 새 데이터베이스로 변경).
그러나 데이터베이스에 데이터와 테이블뿐만 아니라 트리거, 프로시저 또는 유사한 항목이 있는 경우에는 주의해야 합니다.
고정 길이 문제 nvarchar(최대 포함), 텍스트 포함 및 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
저의 경우 일부 열의 정렬이 정확하거나 지정되어 변경을 원하지 않았습니다.
파티에 늦어서 미안하지만, 여기 제 것이 있습니다. 스키마와 재미있는 열과 테이블 이름이 있는 테이블을 위한 것입니다.네, 몇 개 먹었어요.
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'
다음 스크립트는 테이블 스키마와 함께 (MAX), IMAGE 등의 최신 유형과 함께 이 줄의 필요에 따라 정렬 유형을 변경합니다(SET @collate = 'DATABASE_DEFAULT';).
SQL 스크립트 위치:
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
다음 쿼리를 사용하여 테이블의 각 필드에 대한 데이터 정렬 업데이트 쿼리를 생성했습니다.
SELECT
[query] =
'ALTER TABLE '+ c.TABLE_SCHEMA +
'.[' + c.TABLE_NAME +
'] ALTER COLUMN [' + c.COLUMN_NAME +
'] nvarchar(' + IIF(c.CHARACTER_MAXIMUM_LENGTH = -1, 'MAX', CAST(c.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(50))) +
') COLLATE Persian_100_CI_AI_SC_UTF8'
FROM INFORMATION_SCHEMA.COLUMNS AS c
LEFT JOIN sys.computed_columns AS cc ON cc.[name] = c.COLUMN_NAME
WHERE c.TABLE_CATALOG = 'Your_Table_Name' AND DATA_TYPE = 'nvarchar' AND cc.[object_id] IS NULL
AND c.TABLE_NAME NOT IN ('sysdiagrams')
ORDER BY c.TABLE_NAME
계산된 열에서 작동하지 않습니다.
저는 항상 순수한 SQL을 선호하기 때문에:
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;
위의 커서 기반 변형을 시작점으로 사용하여 아래 스크립트는 DATABASE_DEFAULT로 설정하기 위해 일련의 UPDATE 문을 출력할 뿐 실제로는 UPDATES를 수행하지 않습니다.
스키마, 문자 및 텍스트 유형의 전체 집합을 지원하며 기존 NULL/NOT NULL을 유지합니다.
출력을 사용하여 하위 환경에서 실패한 문을 찾은 다음 결과 스크립트를 수동으로 조정하여 제약 조건을 삭제하고 필요에 따라 다시 생성할 계획입니다.
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
모든 필드를 포함하는 데이터베이스의 정렬을 변경하는 가장 쉬운 방법은 병합 복제입니다.
- 대상 데이터 정렬(서버 데이터 정렬 프롭)이 있는 서버 가져오기
- 이전 서버에 병합 게시 만들기
- 원본 데이터베이스의 모든 제품을 게시에 추가
- 스냅숏 에이전트를 실행하고 완료될 때까지 기다립니다.
- 좋은 조합으로 서버를 대상으로 하는 게시에 밀어넣기 구독 추가
- 구독 초기화
- 복제 모니터를 체크인하고 에이전트가 준비될 때까지 대기
- 구독 삭제
- 게시 삭제
다음 SQL 스크립트는 사용자 테이블에 대한 병합 게시를 만듭니다. 다른 단계는 Managemet Studio에서 수행하며 저장 프로시저, 보기 등의 스크립트 개체도 별도의 스크립트로 후속 단계에서 만듭니다.
ALTER PROCEDURE [dbo].[CreateMergePublication]
@PublicationName nvarchar(max) = N'Pubi'
AS BEGIN
SET NOCOUNT ON
BEGIN TRY
-- *** BEGIN BLL ***
declare @DBName nvarchar(max)
select top 1 @DBName = TABLE_CATALOG from INFORMATION_SCHEMA.TABLES
exec sp_replicationdboption @dbname = @DBName, @optname = N'merge publish', @value = N'true'
-- Mergeveröffentlichung wird hinzugefügt
declare @desc nvarchar(max) = N'Mergeveröffentlichung der ' + @dbname + '-Datenbank von Verleger ' + @@SERVERNAME
exec sp_addmergepublication
@publication = @PublicationName,
@description = @desc ,
@sync_mode = N'native',
@retention = 14,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'true',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_subdirectory = N'ftp',
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@dynamic_filters = N'false',
@conflict_retention = 14,
@keep_partition_changes = N'false',
@allow_synctoalternate = N'false',
@max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0,
@use_partition_groups = null,
@publication_compatibility_level = N'100RTM',
@replicate_ddl = 1,
@allow_subscriber_initiated_snapshot = N'false',
@allow_web_synchronization = N'false',
@allow_partition_realignment = N'true',
@retention_period_unit = N'days',
@conflict_logging = N'both',
@automatic_reinitialization_policy = 0
exec sp_addpublication_snapshot
@publication = @PublicationName,
@frequency_type = 4,
@frequency_interval = 14,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 1,
@frequency_subday_interval = 5,
@active_start_time_of_day = 500,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@job_login = null,
@job_password = null,
@publisher_security_mode = 1
declare @schema nvarchar(max), @table nvarchar(max), @uniquename nvarchar(max)
declare cr cursor for
select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'sys%' and TABLE_NAME not like 'ms%' and TABLE_NAME not like 'dtprop%'
order by TABLE_NAME
open cr
WHILE 1=1 BEGIN
FETCH cr INTO @schema, @table
IF @@FETCH_STATUS <> 0 BREAK
set @uniquename = @schema + @table
print @schema + '.' + @table + ' (' + @uniquename + ')'
exec sp_addmergearticle
@publication = @PublicationName,
@article = @uniquename,
@source_owner = @schema,
@source_object = @table,
@type = N'table',
@description = N'',
@creation_script = null,
@pre_creation_cmd = N'none',
@schema_option = 0x000000010C034FD1,
@identityrangemanagementoption = N'manual',
@destination_owner = @schema,
@force_reinit_subscription = 1,
@column_tracking = N'false',
@subset_filterclause = N'',
@vertical_partition = N'false',
@verify_resolver_signature = 1,
@allow_interactive_resolver = N'false',
@fast_multicol_updateproc = N'true',
@check_permissions = 0,
@subscriber_upload_options = 0,
@delete_tracking = N'true',
@compensate_for_errors = N'false',
@stream_blob_columns = N'false',
@partition_options = 0
END
close cr
deallocate cr
-- *** END BLL ***
END TRY
BEGIN CATCH
IF CURSOR_STATUS('global','cr') >= 0
BEGIN
close cr
deallocate cr
END
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity INT, @ErrorState INT;
SELECT @ErrMsg = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE()
RAISERROR(@ErrMsg, @ErrSeverity, @ErrorState)
END CATCH;
END
이 답변이 조금 늦었을 수도 있지만 문을 생성할 수도 있습니다.이것은 커서를 사용하는 것보다 훨씬 빠를 것입니다.
select
'ALTER TABLE '+ tb.TABLE_NAME + ' ALTER COLUMN '+ cl.COLUMN_NAME + ' ' + DATA_TYPE +'('+ cast(CHARACTER_MAXIMUM_LENGTH as nchar(3)) + ') ' + 'COLLATE Latin1_General_CI_AS '
FROM [DATABASE].INFORMATION_SCHEMA.columns cl
left join [DATABASE].tables tb on tb.TABLE_NAME = cl.TABLE_NAME and tb.Table_Schema=cl.table_schema
left join [DATABASE].INFORMATION_SCHEMA.KEY_COLUMN_USAGE kc on kc.table_name = tb.table_name and kc.TABLE_SCHEMA = cl.TABLE_SCHEMA and kc.column_name=cl.COLUMN_NAME
WHERE DATA_TYPE in ('nvarchar','nchar')
이것이 누군가를 도울 수 있기를 바랍니다.
언급URL : https://stackoverflow.com/questions/18122773/change-collations-of-all-columns-of-all-tables-in-sql-server
'programing' 카테고리의 다른 글
| MongoDB 삽입 속도/초를 높이는 방법은 무엇입니까? (0) | 2023.06.19 |
|---|---|
| Model.train()은 PyTorch에서 무엇을 합니까? (0) | 2023.06.19 |
| 어레이를 코어 데이터에 저장하는 방법은 무엇입니까? (0) | 2023.06.19 |
| R의 전역 및 국소 변수 (0) | 2023.06.14 |
| 날짜별 MySQL 순서 결과 및 사용자 ID별 그룹화 (0) | 2023.06.14 |