[MSSQL] DB의 모든 테이블의 Collation 변경

작동버젼 : SQL Sserver 2005 이상
사용방법 : Edit Here 라고 표기되어 있는 곳 수정(타겟 DB명, 변경하고자 하는 Collation명으로 변경)
주의사항 : 여러 Collation이 존재해야 하는 DB에서는 사용하지 말것!
설      명 : DBMS의 Collation이 잘못 설치된 상태에서 디비 생성이 됬을 경우
               명시적으로 생성하지 않았다면 테이블의 char, varchar컬럼등도 변경이 필요 하기에 만든 스크립트
               전 영역 변경이 필요하지 않을 경우 스크립트 만 추출하여 실행 할 수 있음

--Edit Here
USE [YourDB];
GO

SET NOCOUNT ON;


IF OBJECT_ID('tempdb.dbo.#AlterCollateQuery', 'U') IS NOT NULL
	DROP TABLE #AlterCollateQuery;
GO

CREATE TABLE #AlterCollateQuery
(
	 ExecuteOrder int identity(1,1)
	,Query nvarchar(4000)
)
GO

IF OBJECT_ID('dbo.F_GetForeignKeyColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetForeignKeyColumns;
GO

CREATE FUNCTION dbo.F_GetForeignKeyColumns
(
	 @object_id	int
)
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @return				varchar(4000);
	DECLARE @referenced_object_id	int;
	DECLARE @parent_columns		varchar(4000);
	DECLARE @referenced_columns	varchar(4000);
	
	SET @parent_columns = '';
	SET @referenced_columns = '';

	SELECT
		 @referenced_object_id = a.referenced_object_id
		,@parent_columns = @parent_columns + N', [' + b.name + N']'
		,@referenced_columns = @referenced_columns + N', [' + c.name + N']'
	FROM sys.foreign_key_columns AS a
		INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
		INNER JOIN sys.columns AS c ON c.object_id = a.referenced_object_id AND c.column_id = a.referenced_column_id
	WHERE a.constraint_object_id = @object_id;
	
	SELECT @return = N'FOREIGN KEY(' + STUFF(@parent_columns, 1, 2, N'') + N') REFERENCES [' + a.name + N'].[' + b.name + N'](' + STUFF(@referenced_columns, 1, 2, N'') + N')'
	FROM sys.schemas AS a
		INNER JOIN sys.tables  AS b ON a.schema_id = b.schema_id
	WHERE b.object_id = @referenced_object_id;
	
	RETURN @return;
END;
GO



IF OBJECT_ID('dbo.F_GetIndexColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetIndexColumns;
GO

CREATE FUNCTION dbo.F_GetIndexColumns
(
	 @object_id	int
	,@index_id	int
)
RETURNS varchar(4000)
AS
BEGIN
	DECLARE @Return nvarchar(4000);
	SET @Return = '';
	
	SELECT @Return = @Return + N', [' + b.name + N'] ' + CASE WHEN a.is_descending_key = 0 THEN N'ASC' ELSE N'DESC' END
	FROM sys.index_columns AS a
		INNER JOIN sys.columns AS b ON a.object_id = b.object_id AND a.column_id = b.column_id
	WHERE a.object_id = @object_id
	  AND a.index_id = @index_id
	ORDER BY a.index_column_id;
	
	RETURN STUFF(@Return, 1, 2, '');
END;
GO

IF OBJECT_ID('tempdb.dbo.#QueryList', 'U') IS NOT NULL
	DROP TABLE #QueryList;
GO

CREATE TABLE #QueryList
(
	 executeDropOrder int
	,dropQuery nvarchar(4000)
	,executeCreateOrder int
	,createQuery nvarchar(4000)
);
GO




DECLARE @collation_name varchar(4000);
--Edit Here
SET @collation_name = N'Korean_Wansung_CI_AS';




INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 1 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] DROP CONSTRAINT [' + a.name + N'];' AS DropDF
	,4 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] ADD CONSTRAINT [' + a.name + N'] DEFAULT' + a.definition + N' FOR [' + b.name + N'];' AS CreateDF
FROM sys.default_constraints AS a
	INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
	INNER JOIN sys.tables AS c ON b.object_id = c.object_id
	INNER JOIN sys.schemas AS d ON c.schema_id = d.schema_id;


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 2 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] DROP CONSTRAINT [' + a.name + N'];' AS DropCK
	,3 AS executeDropOrder
	,N'ALTER TABLE [' + d.name + N'].[' + c.name + N'] ADD CONSTRAINT [' + a.name + N'] CHECK' + a.definition + CASE WHEN a.is_not_for_replication = 1 THEN ' NOT FOR REPLICATION' ELSE '' END + N';' AS CreateCK
FROM sys.check_constraints AS a
	INNER JOIN sys.columns AS b ON b.object_id = a.parent_object_id AND b.column_id = a.parent_column_id
	INNER JOIN sys.tables AS c ON b.object_id = c.object_id
	INNER JOIN sys.schemas AS d ON c.schema_id = d.schema_id;


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT
	 3 AS executeDropOrder
	,N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] DROP CONSTRAINT [' + c.name + N'];' AS DropFK
	,2 AS executeDropOrder
	,N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] ADD CONSTRAINT [' + c.name + N'] ' + dbo.F_GetForeignKeyColumns(c.object_id) + CASE WHEN c.delete_referential_action_desc = 'NO_ACTION' THEN '' ELSE ' ON DELETE ' + c.delete_referential_action_desc END + CASE WHEN c.update_referential_action_desc = 'NO_ACTION' THEN '' ELSE ' ON UPDATE ' + c.update_referential_action_desc END + CASE WHEN c.is_not_for_replication = 1 THEN ' NOT FOR REPLICATION' ELSE '' END + N';' COLLATE Latin1_General_CI_AS_KS_WS AS CreateFK
FROM sys.schemas AS a
	INNER JOIN sys.tables AS b ON a.schema_id = b.schema_id
	INNER JOIN sys.foreign_keys AS c ON b.object_id = c.parent_object_id
WHERE EXISTS(SELECT TOP 1 '' FROM sys.foreign_key_columns AS d INNER JOIN sys.columns AS e ON d.parent_object_id = e.object_id AND d.parent_column_id = e.column_id WHERE e.collation_name <> @collation_name)
  AND EXISTS(SELECT TOP 1 '' FROM sys.foreign_key_columns AS d INNER JOIN sys.columns AS e ON d.referenced_object_id = e.object_id AND d.referenced_column_id = e.column_id WHERE e.collation_name <> @collation_name);


INSERT INTO #QueryList(executeDropOrder, dropQuery, executeCreateOrder, createQuery)
SELECT DISTINCT
	 4 AS executeDropOrder
	,CASE
		WHEN d.is_primary_key = 0 AND d.is_unique_constraint = 0 THEN N'DROP INDEX [' + d.name + N'] ON [' + f.name + N'].[' + a.name + N'];'
		ELSE N'ALTER TABLE [' + f.name + N'].[' + a.name + N'] DROP CONSTRAINT [' + d.name + N'];'
	END AS DropIndex
	,1 AS executeDropOrder
	,CASE
		WHEN d.is_primary_key = 0 AND d.is_unique_constraint = 0 THEN N'CREATE ' + CASE WHEN d.is_unique = 1 THEN N'UNIQUE ' ELSE '' END + d.type_desc + ' INDEX [' + d.name + N'] ON [' + f.name + N'].[' + a.name + N'](' + dbo.F_GetIndexColumns(d.object_id, d.index_id) + N')' + CASE WHEN d.fill_factor > 0 THEN N' WITH FILLFACTOR = ' + CONVERT(varchar, d.fill_factor) ELSE N'' END + N' ON [' + e.name + N'];'
		ELSE N'ALTER TABLE [' + f.name + N'].[' + a.name + N'] ADD CONSTRAINT [' + d.name + N'] ' + CASE WHEN d.is_primary_key = 1 THEN N'PRIMARY KEY ' WHEN d.is_unique_constraint = 1 THEN N'UNIQUE ' END + d.type_desc + N'(' + dbo.F_GetIndexColumns(d.object_id, d.index_id) + N')' + CASE WHEN d.fill_factor > 0 THEN N' WITH FILLFACTOR = ' + CONVERT(varchar, d.fill_factor) ELSE N'' END + N' ON [' + e.name + N'];'
	END COLLATE Latin1_General_CI_AS_KS_WS AS CreateIndex
FROM sys.tables AS a
	INNER JOIN sys.columns AS b ON a.object_id = b.object_id
	INNER JOIN sys.index_columns AS c ON b.column_id = c.column_id AND b.object_id = c.object_id
	INNER JOIN sys.indexes AS d ON c.object_id = d.object_id AND c.index_id = d.index_id
	INNER JOIN sys.data_spaces AS e ON d.data_space_id = e.data_space_id
	INNER JOIN sys.schemas AS f ON a.schema_id = f.schema_id;






INSERT INTO #AlterCollateQuery(Query)
SELECT dropQuery
FROM #QueryList
ORDER BY executeDropOrder;

INSERT INTO #AlterCollateQuery(Query)
SELECT N'ALTER TABLE [' + a.name + N'].[' + b.name + N'] ALTER COLUMN [' + c.name + N'] ' + d.name + N'(' + CASE WHEN c.max_length < 1 OR c.max_length > 4000 THEN 'MAX' ELSE CONVERT(varchar, c.max_length) END + N') COLLATE ' + @collation_name + CASE WHEN c.is_nullable = 0 THEN N' NOT NULL' ELSE N' NULL' END + N';'
FROM sys.schemas AS a
	INNER JOIN sys.tables AS b ON a.schema_id = b.schema_id
	INNER JOIN sys.columns AS c ON c.object_id = b.object_id
	INNER JOIN sys.types AS d ON d.user_type_id = c.user_type_id
WHERE c.collation_name <> @collation_name;


INSERT INTO #AlterCollateQuery(Query)
SELECT a.Query
FROM
	(
		SELECT N'ALTER DATABASE ' + DB_NAME() + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;' AS Query
		UNION ALL SELECT N'ALTER DATABASE ' + DB_NAME() + N' COLLATE ' + @collation_name + ';' AS Query
		UNION ALL SELECT N'ALTER DATABASE ' + DB_NAME() + N' SET MULTI_USER;' AS Query
	) AS a
WHERE EXISTS(SELECT TOP 1 '' FROM sys.databases WHERE name = DB_NAME() AND collation_name <> @collation_name);

INSERT INTO #AlterCollateQuery(Query)
SELECT createQuery
FROM #QueryList
ORDER BY executeCreateOrder;
GO



IF OBJECT_ID('dbo.F_GetForeignKeyColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetForeignKeyColumns;
GO

IF OBJECT_ID('dbo.F_GetIndexColumns', 'FN') IS NOT NULL
	DROP FUNCTION dbo.F_GetIndexColumns;
GO

IF OBJECT_ID('tempdb.dbo.#QueryList', 'U') IS NOT NULL
	DROP TABLE #QueryList;
GO


DECLARE @Query nvarchar(4000);
DECLARE Collate_Query CURSOR FOR
	SELECT a.Query
	FROM #AlterCollateQuery AS a WITH(NOLOCK)
	ORDER BY a.ExecuteOrder;

OPEN Collate_Query;

FETCH Collate_Query INTO @Query;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Query;
	EXECUTE sp_executesql @Query;
	
	FETCH Collate_Query INTO @Query;
END;

CLOSE Collate_Query;
DEALLOCATE Collate_Query;
GO


IF OBJECT_ID('tempdb.dbo.#AlterCollateQuery', 'U') IS NOT NULL
	DROP TABLE #AlterCollateQuery;
GO