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);
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
