--make sure the db is in good shape
DBCC CHECKDB WITH DATA_PURITY
--running the 'alter database' (below) showed these errors, fix them *** YOU WILL PROBABLY NOT NEED THIS, IT IS A LOCAL ISSUE
SELECT * From sys.objects where upper(name) = 'SEEGLGROSSPROFITYTDMETRICMULTICOCALLER'
drop proc seeGLGrossProfitYTDMetricMultiCOCaller
SELECT * From sys.objects where upper(name) = 'SEETOTALPURCHASESMETRICMULTICOCALLER'
drop proc seeTotalPurchasesMetricMultiCoCaller
--run 020 to script all the constraints. Drop them now, we'll recreate later
--run 035 to script dropping and recreating views
--run 025 to script all table valued functions
--this finally worked
ALTER DATABASE mbi COLLATE SQL_Latin1_General_CP1_CI_AS
--now, the DATABASE collation is changed
--run 040 sp_DropAndCreateIndexes
--run 050 Disable Change Tracking. Warning: I never turn this on (because I don't care :) )
--run 060 drop and recreate foriegn key constraints
--run 070 DropOrCreatePrimaryIndexes
--run 080 Fix field collations
--the above script gave me one odd constraint error, I manually scripted and dropped the collation in the '090' script
--run 090 to put back the odd constraint
--run 070 to put the foriegn key indexes back
--run 060 to put the foriegn key constraints back
--run 020 to put the constraints back
--run 025 to put the functions back
--run 035 to put the views back
020 Drop and Create CK constraints
SELECT
'ALTER TABLE ' + QuoteName(OBJECT_NAME(so.parent_obj)) + ' DROP CONSTRAINT ' + QuoteName(CONSTRAINT_NAME)
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN sys.sysobjects so ON upper(cc.CONSTRAINT_NAME) = upper(so.[name])
order by cc.CONSTRAINT_NAME
SELECT
'ALTER TABLE ' + QuoteName(OBJECT_NAME(so.parent_obj)) + ' ADD CONSTRAINT ' + QuoteName(CONSTRAINT_NAME) + ' CHECK ' + CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc
INNER JOIN sys.sysobjects so ON upper(cc.CONSTRAINT_NAME) = upper(so.[name])
order by cc.CONSTRAINT_NAME
025 drop and create table valued functions
SELECT 'DROP FUNCTION ' + SPECIFIC_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE = 'FUNCTION' and DATA_TYPE = 'TABLE' order by upper(specific_NAME)
SELECT m.definition + char(13) + char(10) + 'GO'
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o ON m.[object_id] = o.[object_id]
WHERE o.type IN ('TF');
035 Drop and Recreate views
DECLARE JOB_CURSOR CURSOR FOR
SELECT OBJECT_ID FROM SYS.OBJECTS WHERE [TYPE] IN ('v')
OPEN JOB_CURSOR
DECLARE @OBJECT_ID AS INT
FETCH NEXT FROM JOB_CURSOR
INTO @OBJECT_ID
DECLARE @SQL AS VARCHAR(MAX)
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'IF OBJECT_ID (''['+ S.NAME + '].['+ O.NAME + ']'') IS NOT NULL BEGIN DROP ' + CASE O.TYPE
WHEN 'P' THEN 'PROCEDURE '
WHEN 'V' THEN 'VIEW '
END + ' ['+ S.NAME + '].['+ O.NAME + ']' + ' end'
FROM SYS.OBJECTS O
INNER JOIN SYS.SQL_MODULES M ON O.OBJECT_ID = M.OBJECT_ID
INNER JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID
WHERE O.OBJECT_ID = @OBJECT_ID
PRINT @SQL
FETCH NEXT FROM JOB_CURSOR
INTO @OBJECT_ID
END
CLOSE JOB_CURSOR
DEALLOCATE JOB_CURSOR
print ''
print ''
DECLARE JOB_CURSOR CURSOR FOR
SELECT OBJECT_ID FROM SYS.OBJECTS WHERE [TYPE] IN ('v')
OPEN JOB_CURSOR
FETCH NEXT FROM JOB_CURSOR
INTO @OBJECT_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = M.DEFINITION + 'GO' + char(13) + char(10)
FROM SYS.OBJECTS O
INNER JOIN SYS.SQL_MODULES M ON O.OBJECT_ID = M.OBJECT_ID
INNER JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID
WHERE O.OBJECT_ID = @OBJECT_ID
PRINT @SQL
FETCH NEXT FROM JOB_CURSOR
INTO @OBJECT_ID
END
CLOSE JOB_CURSOR
DEALLOCATE JOB_CURSOR
040 sp_DropAndCreateIndexes.sql
DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)
DECLARE CursorIndexes CURSOR FOR
SELECT schema_name(t.schema_id), t.name, i.name
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
and (is_primary_key=0 and is_unique_constraint=0)
order by t.name
OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
PRINT @TSQLDropIndex
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END
CLOSE CursorIndexes
DEALLOCATE CursorIndexes
--declare @SchemaName varchar(100)
--declare @TableName varchar(256)
--declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare CursorIndex cursor for
select
schema_name(t.schema_id) [schema_name], t.name, ix.name, --SchemaName, @TableName, @IndexName
case when ix.is_unique = 1 then 'UNIQUE ' else '' END -- @is_unique
, ix.type_desc,
case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
+ case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
+ case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
+ case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
+ case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
+ 'SORT_IN_TEMPDB = OFF' AS IndexOptions
, ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName
from sys.tables t
inner join sys.indexes ix on t.object_id=ix.object_id
where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
order by t.name, schema_name(t.schema_id), ix.name
open CursorIndex
fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
while (@@fetch_status=0)
begin
declare @IndexColumns varchar(max)
declare @IncludedColumns varchar(max)
set @IndexColumns=''
set @IncludedColumns=''
--================================================================================
--inner cursor
--================================================================================
declare CursorIndexColumn cursor for
select col.name, ixc.is_descending_key, ixc.is_included_column
from sys.tables tb
inner join sys.indexes ix on tb.object_id=ix.object_id
inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id
where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
order by ixc.index_column_id
open CursorIndexColumn
fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
while (@@fetch_status=0) begin
if @IsIncludedColumn=0
set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end
else
set @IncludedColumns=@IncludedColumns + @ColumnName +', '
fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
end
close CursorIndexColumn
deallocate CursorIndexColumn
--================================================================================
--end inner cursor
--================================================================================
set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
-- print @IndexColumns
-- print @IncludedColumns
set @TSQLScripCreationIndex = ''
set @TSQLScripDisableIndex = ''
set @TSQLScripCreationIndex = 'CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '('+@IndexColumns+') '+
case when len(@IncludedColumns) > 0 then ' INCLUDE (' + @IncludedColumns+ ')' else '' end + ' WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'
print @TSQLScripCreationIndex
if @is_disabled=1 begin
set @TSQLScripDisableIndex= CHAR(13) + char(10) + 'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)
print @TSQLScripDisableIndex
end
fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
end
close CursorIndex
deallocate CursorIndex
060 drop and recreate foriegn key constraints
SELECT N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
SELECT N'
ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
070 DropOrCreatePrimaryIndexes This one is actually a stored procedure. The rest are scripts
create Proc sp_DropOrCreatePrimaryIndexes
-- sp_DropOrCreatePrimaryIndexes 'create'
-- sp_DropOrCreatePrimaryIndexes 'drop'
@action CHAR(6) --drop or create
as
DECLARE @object_id int;
DECLARE @parent_object_id int;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key bit;
DECLARE @col1 BIT;
DECLARE PKcursor CURSOR FOR
select kc.object_id, kc.parent_object_id
from sys.key_constraints kc
inner join sys.objects o on kc.parent_object_id = o.object_id
where kc.type = 'PK' and o.type = 'U'
and o.name not in ('dtproperties','sysdiagrams') -- not true user tables
order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
,QUOTENAME(OBJECT_NAME(kc.parent_object_id));
OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @action = 'DROP'
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
ELSE
BEGIN
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
+ ' PRIMARY KEY'
+ CASE INDEXPROPERTY(@parent_object_id
,OBJECT_NAME(@object_id),'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END
+ ' (';
DECLARE ColumnCursor CURSOR FOR
select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
from sys.indexes i
inner join sys.index_columns ic
on i.object_id = ic.object_id and i.index_id = ic.index_id
where i.object_id = @parent_object_id
and i.name = OBJECT_NAME(@object_id)
order by ic.key_ordinal;
OPEN ColumnCursor;
SET @col1 = 1;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
SET @TSQL = @TSQL + ',';
SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
+ ' '
+ CASE @is_descending_key
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END;
FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
END;
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
SET @TSQL = @TSQL + ');';
END;
PRINT @TSQL;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;
CLOSE PKcursor;
DEALLOCATE PKcursor;
090 Handle odd Constraint issue. When I got this error I manually created the scripts below by right clicking on the object and scripting it in SSMS.
/****** Object: Index [UQ__RecoveryMaster__636F8578] Script Date: 9/23/2017 4:20:49 PM ******/
ALTER TABLE [dbo].[RecoveryMaster] DROP CONSTRAINT [UQ__RecoveryMaster__636F8578]
ALTER TABLE [dbo].[RecoveryMaster] DROP CONSTRAINT [UQ__Recovery__72E12F1BC6DDD2CF]
ALTER TABLE [dbo].[RecoveryMaster] ADD UNIQUE NONCLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
ALTER TABLE [dbo].[RecoveryMaster] ADD UNIQUE NONCLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO