Skip to content

Instantly share code, notes, and snippets.

@TheRockStarDBA

TheRockStarDBA/ChangeDatabaseCollation

Created April 10, 2015 19:24
Show Gist options
  • Save TheRockStarDBA/e30546e99dd6be278dd2 to your computer and use it in GitHub Desktop.
Save TheRockStarDBA/e30546e99dd6be278dd2 to your computer and use it in GitHub Desktop.
Download ZIP
Change Database Collation
Raw
ChangeDatabaseCollation
/************************************************************************************************************************************************************************************************************************************
************************************************************************************************************************************************************************************************************************************
Author : Kin Shah
Bug Identified The incuded columns were not generated as a part of original script.
and Fixed : Modified the script to include the included columns.
Disclaimer
The views expressed on my posts on this site are mine alone and do not reflect the views of my company. All posts of mine are provided "AS IS" with no warranties, and confers no rights.
The following disclaimer applies to all code, scripts and demos available on my posts:
This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
I grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree:
(i) to use my name, logo, or trademarks to market Your software product in which the Sample Code is embedded;
(ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and
(iii) to indemnify, hold harmless, and defend me from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.
************************************************************************************************************************************************************************************************************************************
*************************************************************************************************************************************************************************************************************************************/
DECLARE @FullName VARCHAR(200)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @statName VARCHAR(100)
DECLARE @columnName VARCHAR(100)
DECLARE @column VARCHAR(100)
DECLARE @indexName VARCHAR(100)
DECLARE @indexDesc VARCHAR(500)
DECLARE @indexUnique BIT
DECLARE @constraintName VARCHAR(100)
DECLARE @constraintType VARCHAR(100)
DECLARE @createStatSQL VARCHAR(MAX)
DECLARE @norecompute BIT
DECLARE @count INT
DECLARE @colLength INT
DECLARE @dataType VARCHAR(25)
DECLARE @keyColumn VARCHAR(MAX)
DECLARE @keyField VARCHAR(MAX)
DECLARE @definition VARCHAR(100)
DECLARE @isNullable VARCHAR(3)
DECLARE @NullStatus VARCHAR(8)
DECLARE @Required_Collation VARCHAR(100)
DECLARE @ViewSchema VARCHAR(100)
DECLARE @ViewName VARCHAR(100)
DECLARE @text VARCHAR(200)
DECLARE @view VARCHAR(200)
DECLARE @FKCOLUMNS VARCHAR(MAX)
DECLARE @PKCOLUMNS VARCHAR(MAX)
DECLARE @COUNTER INT
DECLARE @func_schema VARCHAR(100)
DECLARE @func_name VARCHAR(100)
DECLARE @full_func_name VARCHAR(100)
DECLARE @def_line VARCHAR(MAX)
DECLARE @dropFUNC NVARCHAR(MAX)
DECLARE @ALLdropFUNC NVARCHAR(MAX)
DECLARE @createFK VARCHAR(MAX)
DECLARE @included_columns VARCHAR(max)
SET NOCOUNT ON
SET @Required_Collation = 'SQL_Latin1_General_CP1_CI_AS'
IF OBJECT_ID('tempdb..#collationsToChange') IS NOT NULL
DROP TABLE #collationsToChange
-- Store the collations
SELECT T.TABLE_SCHEMA
,T.TABLE_NAME
,C.COLUMN_NAME
,C.DATA_TYPE
,C.CHARACTER_MAXIMUM_LENGTH
,C.IS_NULLABLE
INTO #collationsToChange
FROM [INFORMATION_SCHEMA].[TABLES] T
,[INFORMATION_SCHEMA].[COLUMNS] C
,[sys].[columns] SC
WHERE T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND SC.[object_id] = object_id(T.TABLE_SCHEMA + '.' + T.TABLE_NAME)
AND SC.[name] = C.COLUMN_NAME
AND T.TABLE_TYPE = 'BASE TABLE'
AND C.COLLATION_NAME <> @Required_Collation
AND SC.[is_computed] = 0
IF (
SELECT count(*)
FROM #collationsToChange
) = 0
PRINT 'No columns need altering to : ' + @Required_Collation
ELSE
BEGIN
IF OBJECT_ID('tempdb..#func_drop') IS NOT NULL
DROP TABLE #func_drop
CREATE TABLE #func_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#func_create') IS NOT NULL
DROP TABLE #func_create
CREATE TABLE #func_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#fk_drop') IS NOT NULL
DROP TABLE #fk_drop
CREATE TABLE #fk_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#fk_create') IS NOT NULL
DROP TABLE #fk_create
CREATE TABLE #fk_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#constraint_drop') IS NOT NULL
DROP TABLE #constraint_drop
CREATE TABLE #constraint_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#constraint_create') IS NOT NULL
DROP TABLE #constraint_create
CREATE TABLE #constraint_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#index_drop') IS NOT NULL
DROP TABLE #index_drop
CREATE TABLE #index_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#index_create') IS NOT NULL
DROP TABLE #index_create
CREATE TABLE #index_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#view_drop') IS NOT NULL
DROP TABLE #view_drop
CREATE TABLE #view_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#view_create') IS NOT NULL
DROP TABLE #view_create
CREATE TABLE #view_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#view_index_create') IS NOT NULL
DROP TABLE #view_index_create
CREATE TABLE #view_index_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#stats_drop') IS NOT NULL
DROP TABLE #stats_drop
CREATE TABLE #stats_drop (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#stats_create') IS NOT NULL
DROP TABLE #stats_create
CREATE TABLE #stats_create (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#collation') IS NOT NULL
DROP TABLE #collation
CREATE TABLE #collation (def_line VARCHAR(max) NULL)
IF OBJECT_ID('tempdb..#indexNames') IS NOT NULL
DROP TABLE #indexNames
CREATE TABLE #indexNames (
index_name SYSNAME
,index_description VARCHAR(max)
,index_keys NVARCHAR(max)
)
/* change made by Kin .. starts*/
IF OBJECT_ID('tempdb..#included_columns') IS NOT NULL
DROP TABLE #included_columns;
CREATE TABLE #included_columns (
index_name SYSNAME
,index_description VARCHAR(max)
,index_keys NVARCHAR(max)
,included_columns NVARCHAR(max)
);
INSERT INTO #included_columns
SELECT Ind.[name] AS index_name
,ind.type_desc AS index_description
,SUBSTRING((
SELECT ', ' + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS index_keys
,SUBSTRING((
SELECT ', ' + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH('')
), 2, 8000) AS included_columns
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
where Ind.[name] is not NULL
/*
Filter out NULL index names
Cannot insert the value NULL into column 'index_name', table 'tempdb.dbo.#included_columns___________________________________________________________________________________________________000000001757'; column does not allow nulls. INSERT fails.
*/
ORDER BY (Sch.NAME + Tab.[name])
/* change made by Kin .. ends */
-- Create script for all Functions
SET @ALLdropFUNC = ''
DECLARE cursor_functions CURSOR
FOR
SELECT ROUTINE_SCHEMA
,ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'
ORDER BY ROUTINE_SCHEMA
,ROUTINE_NAME
OPEN cursor_functions
FETCH NEXT
FROM cursor_functions
INTO @func_schema
,@func_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @full_func_name = '[' + @func_schema + '].[' + @func_name + ']'
INSERT #func_drop
VALUES ('PRINT ''DROP FUNCTION : ' + @full_func_name + '''')
INSERT #func_drop
VALUES ('GO')
INSERT #func_drop
VALUES ('DROP FUNCTION ' + @full_func_name)
INSERT #func_drop
VALUES ('GO')
INSERT #func_create
VALUES ('PRINT ''CREATE FUNCTION : ' + @full_func_name + '''')
INSERT #func_create
VALUES ('GO')
INSERT #func_create
EXEC sp_helptext @full_func_name
INSERT #func_create
VALUES ('GO')
SET @ALLdropFUNC = @ALLdropFUNC + @dropFUNC
FETCH NEXT
FROM cursor_functions
INTO @func_schema
,@func_name
END
CLOSE cursor_functions
DEALLOCATE cursor_functions
-- Create script for ALL VIEWS
--Get the views
DECLARE viewsCursor CURSOR FORWARD_ONLY
FOR
SELECT TABLE_SCHEMA
,TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
OPEN viewsCursor
FETCH NEXT
FROM viewsCursor
INTO @ViewSchema
,@ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @view = '[' + @ViewSchema + '].[' + @ViewName + ']'
INSERT #view_drop
VALUES ('PRINT ''DROP VIEW : ' + @view + '''')
INSERT #view_drop
VALUES ('GO')
INSERT #view_drop
VALUES ('DROP VIEW ' + @view)
INSERT #view_drop
VALUES ('GO')
INSERT #view_create
VALUES ('PRINT ''CREATE VIEW : ' + @view + '''')
INSERT #view_create
VALUES ('GO')
INSERT #view_create
EXEC sp_helptext @view
INSERT #view_create
VALUES ('GO')
-- Get indexes created on the current view
DECLARE ViewIndexesCursor CURSOR FORWARD_ONLY
FOR
SELECT i.NAME AS index_name
,i.type_desc
,is_unique
FROM sys.indexes AS i
WHERE is_hypothetical = 0
AND i.index_id <> 0
AND is_unique_constraint <> 1
AND i.object_id = OBJECT_ID(@view)
OPEN ViewIndexesCursor
FETCH NEXT
FROM ViewIndexesCursor
INTO @indexName
,@indexDesc
,@indexUnique
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @keyColumn = index_keys
FROM #indexNames
WHERE index_name = @indexName
INSERT #view_index_create
VALUES ('PRINT ''CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @view + ' (' + @keyColumn + ')''')
INSERT #view_index_create
VALUES ('CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @view + ' (' + @keyColumn + ')')
FETCH NEXT
FROM ViewIndexesCursor
INTO @indexName
,@indexDesc
,@indexUnique
END
CLOSE ViewIndexesCursor
DEALLOCATE ViewIndexesCursor
FETCH NEXT
FROM viewsCursor
INTO @ViewSchema
,@ViewName
END
CLOSE viewsCursor
DEALLOCATE viewsCursor
-------------------------------------------------------------------
-- Create script for ALL Foreign Keys
DECLARE cursor_tables CURSOR
FOR
SELECT TABLE_SCHEMA
,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN cursor_tables
FETCH NEXT
FROM cursor_tables
INTO @schemaName
,@tableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX)
DECLARE @FK_NAME SYSNAME
,@FK_OBJECTID INT
,@FK_DISABLED INT
,@FK_NOT_FOR_REPLICATION INT
,@DELETE_RULE SMALLINT
,@UPDATE_RULE SMALLINT
,@FKTABLE_NAME SYSNAME
,@FKTABLE_OWNER SYSNAME
,@PKTABLE_NAME SYSNAME
,@PKTABLE_OWNER SYSNAME
,@FKCOLUMN_NAME SYSNAME
,@PKCOLUMN_NAME SYSNAME
,@CONSTRAINT_COLID INT
DECLARE cursor_fkeys CURSOR
FOR
SELECT Fk.NAME
,Fk.OBJECT_ID
,Fk.is_disabled
,Fk.is_not_for_replication
,Fk.DELETE_referential_action
,Fk.update_referential_action
,OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name
,schema_name(Fk.schema_id) AS Fk_table_schema
,TbR.NAME AS Pk_table_name
,schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk
LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE TbR.NAME = @tableName
AND schema_name(TbR.schema_id) = @schemaName
OPEN cursor_fkeys
FETCH NEXT
FROM cursor_fkeys
INTO @FK_NAME
,@FK_OBJECTID
,@FK_DISABLED
,@FK_NOT_FOR_REPLICATION
,@DELETE_RULE
,@UPDATE_RULE
,@FKTABLE_NAME
,@FKTABLE_OWNER
,@PKTABLE_NAME
,@PKTABLE_OWNER
WHILE @@FETCH_STATUS = 0
BEGIN
-- create statement for dropping FK and also for recreating FK
-- drop statement
INSERT #fk_drop
VALUES ('PRINT ''ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] DROP CONSTRAINT [' + @FK_NAME + ']''')
INSERT #fk_drop
VALUES ('GO')
INSERT #fk_drop
VALUES ('ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] DROP CONSTRAINT [' + @FK_NAME + ']')
INSERT #fk_drop
VALUES ('GO')
-- create process
-- create cursor to get FK columns
DECLARE cursor_fkeyCols CURSOR
FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name
,COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk
LEFT OUTER JOIN sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id
INNER JOIN sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.NAME = @tableName
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008
ORDER BY Fk_Cl.constraint_column_id
OPEN cursor_fkeyCols
FETCH NEXT
FROM cursor_fkeyCols
INTO @FKCOLUMN_NAME
,@PKCOLUMN_NAME
SET @COUNTER = 1
SET @FKCOLUMNS = ''
SET @PKCOLUMNS = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ','
SET @PKCOLUMNS = @PKCOLUMNS + ','
END
SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
SET @COUNTER = @COUNTER + 1
FETCH NEXT
FROM cursor_fkeyCols
INTO @FKCOLUMN_NAME
,@PKCOLUMN_NAME
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
-- generate create FK statement
SET @createFK = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' + CASE @FK_DISABLED
WHEN 0
THEN ' CHECK '
WHEN 1
THEN ' NOCHECK '
END + ' ADD CONSTRAINT [' + @FK_NAME + '] FOREIGN KEY (' + @FKCOLUMNS + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] (' + @PKCOLUMNS + ') ON UPDATE ' + CASE @UPDATE_RULE
WHEN 0
THEN ' NO ACTION '
WHEN 1
THEN ' CASCADE '
WHEN 2
THEN ' SET_NULL '
END + ' ON DELETE ' + CASE @DELETE_RULE
WHEN 0
THEN ' NO ACTION '
WHEN 1
THEN ' CASCADE '
WHEN 2
THEN ' SET_NULL '
END + '' + CASE @FK_NOT_FOR_REPLICATION
WHEN 0
THEN ''
WHEN 1
THEN ' NOT FOR REPLICATION '
END
INSERT #fk_create
VALUES ('PRINT ''' + @createFK + '''')
INSERT #fk_create
VALUES ('GO')
INSERT #fk_create
VALUES (@createFK)
INSERT #fk_create
VALUES ('GO')
FETCH NEXT
FROM cursor_fkeys
INTO @FK_NAME
,@FK_OBJECTID
,@FK_DISABLED
,@FK_NOT_FOR_REPLICATION
,@DELETE_RULE
,@UPDATE_RULE
,@FKTABLE_NAME
,@FKTABLE_OWNER
,@PKTABLE_NAME
,@PKTABLE_OWNER
END
CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys
FETCH NEXT
FROM cursor_tables
INTO @schemaName
,@tableName
END
CLOSE cursor_tables
DEALLOCATE cursor_tables
-------------------------------------------------------------------
DECLARE tablescursor CURSOR FORWARD_ONLY
FOR
SELECT TABLE_SCHEMA
,TABLE_NAME
FROM #collationsToChange
GROUP BY TABLE_SCHEMA
,TABLE_NAME
OPEN tablescursor
FETCH NEXT
FROM tablescursor
INTO @SchemaName
,@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullName = '[' + @SchemaName + '].[' + @tableName + ']'
INSERT #collation
VALUES ('PRINT ''----------------------------------------------------------------------''')
INSERT #collation
VALUES ('PRINT ''-- Processing table: ' + @FullName + '''')
INSERT #collation
VALUES ('PRINT ''----------------------------------------------------------------------''')
INSERT #collation
VALUES ('GO')
INSERT #constraint_drop
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #constraint_drop
VALUES ('GO')
INSERT #constraint_create
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #constraint_create
VALUES ('GO')
INSERT #index_drop
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #index_drop
VALUES ('GO')
INSERT #index_create
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #index_create
VALUES ('GO')
INSERT #stats_drop
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #stats_drop
VALUES ('GO')
INSERT #stats_create
VALUES ('PRINT ''-- Table: ' + @FullName + '''')
INSERT #stats_create
VALUES ('GO')
DELETE #indexNames
IF (
SELECT count(*)
FROM sys.index_columns
WHERE object_id = object_id(@FullName)
) > 0
BEGIN
--Grab the index info from the system stored proc
INSERT INTO #indexNames
EXEC sp_helpindex @FullName
END
--Get the constraints that are not FOREIGN KEYS
DECLARE constraintsCursor CURSOR FORWARD_ONLY
FOR
SELECT Constraint_Name
,Constraint_Type
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE table_name = @TableName
AND table_schema = @SchemaName
AND Constraint_Type <> 'FOREIGN KEY'
OPEN constraintsCursor
FETCH NEXT
FROM constraintsCursor
INTO @constraintName
,@constraintType
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #constraint_drop
VALUES ('PRINT '' DROP CONSTRAINT [' + @constraintName + ']''')
INSERT #constraint_drop
VALUES ('GO')
INSERT #constraint_drop
VALUES ('ALTER TABLE ' + @FullName + ' DROP CONSTRAINT [' + @constraintName + ']')
INSERT #constraint_drop
VALUES ('GO')
IF @constraintType = 'CHECK'
BEGIN
SELECT @definition = DEFINITION
FROM sys.check_constraints
WHERE [name] = @constraintName
AND [object_id] = object_id(@FullName)
INSERT #constraint_create
VALUES ('PRINT '' ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @definition + ')''')
INSERT #constraint_create
VALUES ('GO')
INSERT #constraint_create
VALUES ('ALTER TABLE ' + @FullName + ' WITH CHECK ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @definition + ')')
INSERT #constraint_create
VALUES ('GO')
END
ELSE
BEGIN
SELECT @keyField = index_keys
FROM #indexNames
WHERE index_name = @constraintName
INSERT #constraint_create
VALUES ('PRINT '' ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @keyField + ')''')
INSERT #constraint_create
VALUES ('GO')
INSERT #constraint_create
VALUES ('ALTER TABLE ' + @FullName + ' WITH CHECK ADD CONSTRAINT [' + @constraintName + '] ' + @constraintType + ' (' + @keyField + ')')
INSERT #constraint_create
VALUES ('GO')
END
FETCH NEXT
FROM constraintsCursor
INTO @constraintName
,@constraintType
END
CLOSE constraintsCursor
DEALLOCATE constraintsCursor
-- Get indexes but do not get primary keys as they cannot be dropped in this way
-- They get dropped as constraints above
DECLARE indexesCursor CURSOR FORWARD_ONLY
FOR
SELECT i.NAME AS index_name
,i.type_desc
,is_unique
FROM sys.indexes AS i
WHERE is_hypothetical = 0
AND i.index_id <> 0
AND is_unique_constraint <> 1
AND i.object_id = OBJECT_ID(@fullName)
--**************
AND i.NAME NOT LIKE 'PK%' -- is_primary_key != 1 --
OPEN indexesCursor
FETCH NEXT
FROM indexesCursor
INTO @indexName
,@indexDesc
,@indexUnique
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'dropping '+@indexName
SELECT @keyColumn = index_keys
FROM #indexNames
WHERE index_name = @indexName
SELECT @included_columns = included_columns
FROM #included_columns
WHERE index_name = @indexName
--select * from #included_columns where index_name = 'I_Staging_Investor_ComplianceId'
--and included_columns is null
INSERT #index_drop
VALUES ('PRINT '' DROP INDEX [' + @indexName + '] ON ' + @fullName + '''')
INSERT #index_drop
VALUES ('GO')
INSERT #index_drop
VALUES ('DROP INDEX [' + @indexName + '] ON ' + @fullName)
INSERT #index_drop
VALUES ('GO')
--************************
INSERT #index_create
VALUES (
'PRINT '' CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @fullName + ' (' + @keyColumn + ')' + CASE
WHEN @included_columns IS NOT NULL
THEN ' include(' + @included_columns + ')'''
ELSE ''''
END
) --# in print you need quote to complete the print statement
INSERT #index_create
VALUES ('GO')
INSERT #index_create
VALUES (
'CREATE ' + @indexDesc + ' INDEX [' + @indexName + '] ON ' + @fullName + ' (' + @keyColumn + ')' + CASE
WHEN @included_columns IS NOT NULL
THEN ' include(' + @included_columns + ')'
ELSE ''
END
) -- # in create you do not need the quote
INSERT #index_create
VALUES ('GO')
--print 'creating '+@indexName
FETCH NEXT
FROM indexesCursor
INTO @indexName
,@indexDesc
,@indexUnique
END
CLOSE indexesCursor
DEALLOCATE indexesCursor
--Get the use created statistics
DECLARE statisticsCursor CURSOR FORWARD_ONLY
FOR
SELECT [name]
,[no_recompute]
FROM sys.stats
WHERE user_created = 1
AND object_id = object_id(@FullName)
OPEN statisticsCursor
FETCH NEXT
FROM statisticsCursor
INTO @statName
,@norecompute
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #stats_drop
VALUES ('PRINT '' DROP STATISTICS ' + @FullName + '.[' + @statName + ']''')
INSERT #stats_drop
VALUES ('GO')
INSERT #stats_drop
VALUES ('DROP STATISTICS ' + @FullName + '.[' + @statName + ']')
INSERT #stats_drop
VALUES ('GO')
SET @createStatSQL = 'CREATE STATISTICS [' + @statName + '] on ' + @FullName + '('
DECLARE statColumnCursor CURSOR FORWARD_ONLY
FOR
SELECT c.NAME
FROM sys.stats s
INNER JOIN sys.stats_columns sc ON sc.object_id = s.object_id
AND sc.stats_id = s.stats_id
INNER JOIN sys.columns c ON c.object_id = sc.object_id
AND c.column_id = sc.column_id
WHERE s.user_created = 1
AND s.object_id = object_id(@FullName)
AND s.NAME = @statName
OPEN statColumnCursor
FETCH NEXT
FROM statColumnCursor
INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @createStatSQL = @createStatSQL + '[' + @columnName + '],'
FETCH NEXT
FROM statColumnCursor
INTO @columnName
END
CLOSE statColumnCursor
DEALLOCATE statColumnCursor
SET @createStatSQL = substring(@createStatSQL, 1, len(@createStatSQL) - 1)
IF @norecompute = 1
SET @createStatSQL = @createStatSQL + ') with NORECOMPUTE'
ELSE
SET @createStatSQL = @createStatSQL + ')'
INSERT #stats_create
VALUES ('PRINT ' + @createStatSQL)
INSERT #stats_create
VALUES ('GO')
INSERT #stats_create
VALUES (@createStatSQL)
INSERT #stats_create
VALUES ('GO')
FETCH NEXT
FROM statisticsCursor
INTO @statName
,@norecompute
END
CLOSE statisticsCursor
DEALLOCATE statisticsCursor
--Get the columns for this table
DECLARE columnsCursor CURSOR FORWARD_ONLY
FOR
SELECT column_name
,character_maximum_length
,data_type
,is_nullable
FROM #collationsToChange
WHERE table_name = @TableName
AND table_schema = @SchemaName
OPEN columnsCursor
FETCH NEXT
FROM columnsCursor
INTO @column
,@colLength
,@dataType
,@isNullable
WHILE @@FETCH_STATUS = 0
BEGIN
--ALTER the table to SET the collation
SET @NullStatus = ''
IF @isNullable = 'YES'
SET @NullStatus = 'NULL'
ELSE
SET @NullStatus = 'NOT NULL'
IF @dataType IN (
'text'
,'ntext'
)
BEGIN
INSERT #collation
VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + ' COLLATE ' + @Required_Collation + ' ' + @NullStatus)
END
ELSE
BEGIN
IF @colLength = - 1 -- MAX --
INSERT #collation
VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + '(max) COLLATE ' + @Required_Collation + ' ' + @NullStatus)
ELSE
INSERT #collation
VALUES ('ALTER TABLE ' + @FullName + ' ALTER COLUMN [' + @column + '] ' + @dataType + '(' + CONVERT(VARCHAR(6), @colLength) + ') COLLATE ' + @Required_Collation + ' ' + @NullStatus)
END
FETCH NEXT
FROM columnsCursor
INTO @column
,@colLength
,@dataType
,@isNullable
END
CLOSE columnsCursor
DEALLOCATE columnsCursor
INSERT #collation
VALUES ('GO')
--Get the next table
FETCH NEXT
FROM tablescursor
INTO @SchemaName
,@TableName
END
CLOSE tablescursor
DEALLOCATE tablescursor
--------------------------------------------------------------------------
PRINT ' '
PRINT 'PRINT '' NOTE: Delete all preceeding lines ***'' '
PRINT 'PRINT '' '' '
PRINT 'PRINT ''--------------------------------------------------'''
PRINT 'PRINT ''--------------------------------------------------'''
PRINT 'PRINT ''-- ***** Update collation for all tables ***** --'''
PRINT 'PRINT ''--------------------------------------------------'''
PRINT 'PRINT ''--------------------------------------------------'''
PRINT 'PRINT ''--'''
PRINT 'PRINT ''-- New collation: ' + @Required_Collation + ''''
PRINT 'PRINT ''--'''
PRINT 'PRINT ''--------------------------------------------------'''
PRINT 'PRINT '' '' '
PRINT 'GO'
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #fk_drop
) > 0
BEGIN
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL FOREIGN KEYS'''
PRINT 'GO'
DECLARE cursor_fk CURSOR
FOR
SELECT def_line
FROM #fk_drop
OPEN cursor_fk
FETCH NEXT
FROM cursor_fk
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_fk
INTO @def_line
END
CLOSE cursor_fk
DEALLOCATE cursor_fk
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #constraint_drop
) > 0
BEGIN
-- Drop Constraints
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL CONSTRAINTS'''
PRINT 'GO'
DECLARE cursor_constraint CURSOR
FOR
SELECT def_line
FROM #constraint_drop
OPEN cursor_constraint
FETCH NEXT
FROM cursor_constraint
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_constraint
INTO @def_line
END
CLOSE cursor_constraint
DEALLOCATE cursor_constraint
PRINT 'GO'
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #func_drop
) > 0
BEGIN
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL FUNCTIONS'''
PRINT 'GO'
DECLARE cursor_func CURSOR
FOR
SELECT def_line
FROM #func_drop
OPEN cursor_func
FETCH NEXT
FROM cursor_func
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_func
INTO @def_line
END
CLOSE cursor_func
DEALLOCATE cursor_func
PRINT 'GO'
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #view_drop
) > 0
BEGIN
-- Drop Views
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL VIEWS'''
PRINT 'GO'
DECLARE cursor_views CURSOR
FOR
SELECT def_line
FROM #view_drop
OPEN cursor_views
FETCH NEXT
FROM cursor_views
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_views
INTO @def_line
END
CLOSE cursor_views
DEALLOCATE cursor_views
PRINT 'GO'
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #stats_drop
) > 0
BEGIN
-- Drop Stats
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL user created STATISTICS'''
PRINT 'GO'
DECLARE cursor_stats CURSOR
FOR
SELECT def_line
FROM #stats_drop
OPEN cursor_stats
FETCH NEXT
FROM cursor_stats
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_stats
INTO @def_line
END
CLOSE cursor_stats
DEALLOCATE cursor_stats
PRINT 'GO'
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #index_drop
) > 0
BEGIN
-- Drop Indexes
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Drop ALL Table INDEXES'''
PRINT 'GO'
DECLARE cursor_index CURSOR
FOR
SELECT def_line
FROM #index_drop
OPEN cursor_index
FETCH NEXT
FROM cursor_index
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_index
INTO @def_line
END
CLOSE cursor_index
DEALLOCATE cursor_index
PRINT 'GO'
END
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Alter Collation
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Alter COLLATION'''
PRINT 'GO'
DECLARE cursor_alter CURSOR
FOR
SELECT def_line
FROM #collation
OPEN cursor_alter
FETCH NEXT
FROM cursor_alter
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_alter
INTO @def_line
END
CLOSE cursor_alter
DEALLOCATE cursor_alter
PRINT 'GO'
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- Change default database collation
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Alter default database COLLATION'''
PRINT 'GO'
PRINT 'alter database ' + db_name() + ' set single_user with rollback immediate'
PRINT 'alter database ' + db_name() + ' collate ' + @Required_Collation
PRINT 'alter database ' + db_name() + ' set multi_user'
PRINT 'GO'
--------------------------------------------------------------------------
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #constraint_create
) > 0
BEGIN
-- Recreate Constraints
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate Table CONSTRAINTS'''
PRINT 'GO'
DECLARE cursor_constraint CURSOR
FOR
SELECT def_line
FROM #constraint_create
OPEN cursor_constraint
FETCH NEXT
FROM cursor_constraint
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_constraint
INTO @def_line
END
CLOSE cursor_constraint
DEALLOCATE cursor_constraint
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #index_create
) > 0
BEGIN
-- Recreate Indexes
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate Table INDEXES'''
PRINT 'GO'
DECLARE cursor_index CURSOR
FOR
SELECT def_line
FROM #index_create
OPEN cursor_index
FETCH NEXT
FROM cursor_index
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_index
INTO @def_line
END
CLOSE cursor_index
DEALLOCATE cursor_index
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #stats_create
) > 0
BEGIN
-- Recreate Stats
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate Table STATISTICS'''
PRINT 'GO'
DECLARE cursor_stats CURSOR
FOR
SELECT def_line
FROM #stats_create
OPEN cursor_stats
FETCH NEXT
FROM cursor_stats
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_stats
INTO @def_line
END
CLOSE cursor_stats
DEALLOCATE cursor_stats
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #fk_create
) > 0
BEGIN
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate ALL FOREIGN KEYS'''
PRINT 'GO'
DECLARE cursor_fk CURSOR
FOR
SELECT def_line
FROM #fk_create
OPEN cursor_fk
FETCH NEXT
FROM cursor_fk
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_fk
INTO @def_line
END
CLOSE cursor_fk
DEALLOCATE cursor_fk
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #view_create
) > 0
BEGIN
-- Recreate Views
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate ALL VIEWS'''
PRINT 'GO'
DECLARE cursor_view CURSOR
FOR
SELECT def_line
FROM #view_create
OPEN cursor_view
FETCH NEXT
FROM cursor_view
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_view
INTO @def_line
END
CLOSE cursor_view
DEALLOCATE cursor_view
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #index_create
) > 0
BEGIN
-- Recreate Indexes
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate ALL View INDEXES'''
PRINT 'GO'
DECLARE cursor_view_index CURSOR
FOR
SELECT def_line
FROM #view_index_create
OPEN cursor_view_index
FETCH NEXT
FROM cursor_view_index
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_view_index
INTO @def_line
END
CLOSE cursor_view_index
DEALLOCATE cursor_view_index
END
--------------------------------------------------------------------------
IF (
SELECT count(*)
FROM #func_create
) > 0
BEGIN
PRINT 'PRINT ''------------------------------------------------------------------------------'''
PRINT 'PRINT ''-- Recreate ALL FUNCTIONS'''
PRINT 'GO'
DECLARE cursor_func CURSOR
FOR
SELECT def_line
FROM #func_create
OPEN cursor_func
FETCH NEXT
FROM cursor_func
INTO @def_line
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @def_line
FETCH NEXT
FROM cursor_func
INTO @def_line
END
CLOSE cursor_func
DEALLOCATE cursor_func
END
--------------------------------------------------------------------------
PRINT 'PRINT '' '''
PRINT 'PRINT ''--------------------------------------------'''
PRINT 'PRINT ''--------------------------------------------'''
PRINT 'PRINT ''-- ***** Update collation complete ***** --'''
PRINT 'PRINT ''--------------------------------------------'''
PRINT 'PRINT ''--------------------------------------------'''
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment