SQL Collate Change


:SETVAR destdb MSDAX2012
:SETVAR desired_collation SQL_Latin1_General_CP1256_CI_AS
:SETVAR script_only 0


USE $( destdb)
GO


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_ON_QUOTED_ON]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_ON]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_ON]
        @stmt nvarchar (max)
AS


EXEC sp_executesql @stmt


GO


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_ON_QUOTED_OFF]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_OFF]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_OFF]
        @stmt nvarchar (max)
AS


EXEC sp_executesql @stmt


GO


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_OFF_QUOTED_ON]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_ON]
GO
d
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_ON]
        @stmt nvarchar (max)
AS


SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON


EXEC sp_executesql @stmt


GO


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_OFF_QUOTED_OFF]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_OFF]
GO


SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


CREATE PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_OFF]
        @stmt nvarchar (max)
AS


SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF


EXEC sp_executesql @stmt


GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


SET NOCOUNT ON


PRINT '--Job start date: ' + cast(getdate () as varchar( 40))


PRINT 'USE $(destdb)'
USE $( destdb)


IF $( script_only) <> 0
        PRINT 'DBCC CHECKDB WITH DATA_PURITY'
ELSE
        BEGIN
               PRINT CONVERT (varchar( 30), getdate(), 109 )
               DBCC CHECKDB WITH DATA_PURITY
               PRINT CONVERT (varchar( 30), getdate(), 109 )
               PRINT '-- **** CHECKDB FINISHED **** '
               PRINT '-- **** **** '
               PRINT '-- **** **** '
               PRINT '-- **** **** '
        END




DECLARE @RecoveryModel int


SELECT @RecoveryModel = recovery_model
FROM sys .databases
WHERE [name] = '$(destdb)'


PRINT '-- Database original recovery model - ' +
        (case @RecoveryModel when 3 then 'SIMPLE' when 2 then 'BULK_LOGGED' when 1 then 'FULL' else 'UNKNOWN' end )


IF @RecoveryModel <> 3
BEGIN
        PRINT '-- Waiting to change recovery model to SIMPLE...'


        IF $( script_only) = 0
               BEGIN
                      ALTER DATABASE $(destdb ) SET RECOVERY SIMPLE


                      IF @@error <> 0
                            BEGIN
                                   PRINT 'Failed to set recovery model to SIMPLE. Script stopped. Please try again'
                                   RETURN
                            END
                      ELSE
                            BEGIN
                                   PRINT 'Recovery model now SIMPLE, please do not forget to restore recovery model if necessary.'
                            END
               END
        ELSE
               BEGIN
                      PRINT 'ALTER DATABASE $(destdb) SET RECOVERY SIMPLE'
                      PRINT 'IF @@error <> 0'
                      PRINT '              BEGIN'
                      PRINT '                    PRINT ''Failed to set recovery model to SIMPLE. Script stopped. Please try again.'''
                      PRINT '                    RETURN'
                      PRINT '              END'
               END
END


DECLARE @has_error int, @num_cycles int, @max_cycles int , @object_id int
SELECT @max_cycles = 10 , @num_cycles = 0


DECLARE @old_name varchar( 512), @new_name varchar (512)


DECLARE @obj_def nvarchar( max), @obj_name nvarchar (128), @stmt nvarchar(max ), @obj_name1 sysname
        , @obj_name2 sysname, @obj_name3 sysname, @flag int , @flag1 int, @mode char(1 ), @obj_id int
        , @field_list varchar( max), @field varchar (max), @definition varchar(max ), @ansi_nulls int
        , @quoted_identifier int


DECLARE @owner sysname, @table_name sysname, @col_name sysname , @length int, @type_name sysname
        , @nullable varchar( 8), @precision tinyint , @scale tinyint, @is_user_defined int , @is_identity int
        , @is_computed int, @system_type_id int, @seed int , @increment int


DECLARE @fk_name sysname, @constraint_column_name sysname
        , @referenced_object sysname, @ref_owner sysname, @referenced_column_name sysname
        , @is_disabled int, @is_not_for_replication int, @delete_referential_action int
        , @update_referential_action int, @with_check varchar(15 )


DECLARE @fk_name1 sysname, @table_name1 sysname, @owner1 sysname , @constraint_column_name1 sysname
        , @referenced_object1 sysname, @ref_owner1 sysname, @referenced_column_name1 sysname
        , @is_disabled1 int, @is_not_for_replication1 int, @delete_referential_action1 int
        , @update_referential_action1 int, @with_check1 varchar(15 )


DECLARE @col_list1 varchar( 8000), @col_list2 varchar (8000)


DECLARE       @idx_name sysname, @idx_type tinyint , @idx_type_desc nvarchar( 60), @is_unique bit , @ignore_dup_key bit,
        @is_unique_constraint bit , @fill_factor tinyint, @is_padded bit, @allow_row_locks bit ,
        @allow_page_locks bit , @key_ordinal tinyint, @is_descending_key bit, @is_included_column bit ,
        @fg_name sysname


DECLARE       @idx_name1 sysname, @idx_type1 tinyint , @idx_type_desc1 nvarchar(60 ), @is_unique1 bit, @ignore_dup_key1 bit,
        @is_unique_constraint1 bit , @fill_factor1 tinyint, @is_padded1 bit, @allow_row_locks1 bit ,
        @allow_page_locks1 bit , @key_ordinal1 tinyint, @is_descending_key1 bit, @is_included_column1 bit ,
        @col_name1 sysname , @fg_name1 sysname


PRINT 'USE tempdb'
USE tempdb
PRINT '-- create tempdb tables'


IF $( script_only) = 0
        BEGIN


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[check_constr]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [check_constr]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[def_constr]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [def_constr]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[fk_constr]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [fk_constr]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[fk_constr_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [fk_constr_cols]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[udf]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [udf]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[udv]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [udv]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[comp_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [comp_cols]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[char_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [char_cols]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[indexes]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [indexes]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[ind_columns]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [ind_columns]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[pk_constr]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [pk_constr]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[pk_constr_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [pk_constr_cols]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[triggers]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [triggers]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[trigger_order]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [trigger_order]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[permissions]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [permissions]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[user_stat]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [user_stat]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[user_stat_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [user_stat_cols]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[bad_tables]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [bad_tables]


               IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[bad_table_cols]' ) AND type in ( N'U'))
               DROP TABLE [dbo]. [bad_table_cols]


        END


PRINT '-- LOCK DATABASE'
PRINT '-- ***'


PRINT 'USE $(destdb)'
USE $( destdb)


IF $( script_only) = 0
        BEGIN
               ALTER DATABASE $(destdb ) SET single_user WITH ROLLBACK IMMEDIATE


               IF @@error <> 0
               BEGIN
                      PRINT 'Could not alter database to sinle_use mode'
                      RAISERROR('Could not alter database to sinle_use mode' , 16, 1)
                      RETURN
               END
        END
ELSE
        BEGIN
               PRINT 'ALTER DATABASE $(destdb) SET single_user WITH ROLLBACK IMMEDIATE'
               PRINT 'IF @@error <> 0 '
               PRINT '              BEGIN'
               PRINT '                    PRINT ''Could not alter database to sinle_use mode'''
               PRINT '                    RAISERROR(''Could not alter database to sinle_use mode'', 16, 1)'
               PRINT '                    RETURN'
               PRINT '              END'
        END


PRINT '--Collect info section start time: ' + cast(getdate () as varchar( 40))




CREATE TABLE tempdb. dbo.udf (
        [object_id] [int] ,
        [udf_name] [sysname] ,
        [owner] [sysname] ,
        [definition] [nvarchar] (max),
        [dropped] [int] ,
        [ansi_nulls] [int] ,
        [quoted_identifier] [int]
)


PRINT '-- GET UDF INFO (both schema-bound and uses_database_collation)'


INSERT INTO tempdb. dbo.udf
        ([object_id], [udf_name], [owner], [definition], [ansi_nulls], [quoted_identifier])
SELECT o .[object_id]
        , [udf_name] = o.[name]
        , owner = s.[name]
        , m .[definition]
        , [ansi_nulls] = m.uses_ansi_nulls
        , [quoted_identifier] = m.uses_quoted_identifier
FROM sys .sql_modules m
        JOIN sys .objects o on o. [object_id] = m. [object_id]
        join sys .schemas s on s. schema_id = o. schema_id
WHERE (
        m.uses_database_collation = 1
               or
               m.is_schema_bound = 1
        )
        and o .[type] in ( 'TF', 'IF', 'FN')


IF @@error <> 0
BEGIN
        PRINT '-- UDF INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('UDF INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


CREATE TABLE tempdb. dbo.udv (
        [object_id] [int] ,
        [udv_name] [sysname] ,
        [owner] [sysname] ,
        [definition] [nvarchar] (max),
        [dropped] [int] ,
        [ansi_nulls] [int] ,
        [quoted_identifier] [int]
)


PRINT '-- GET VIEW INFO (both schema-bound and uses_database_collation)'


INSERT INTO tempdb. dbo.udv
        ([object_id], [udv_name], [owner], [definition], [ansi_nulls], [quoted_identifier])
SELECT o .[object_id]
        , [udv_name] = o.[name]
        , owner = s.[name]
        , m .[definition]
        , [ansi_nulls] = m.uses_ansi_nulls
        , [quoted_identifier] = m.uses_quoted_identifier
FROM sys .sql_modules m
        JOIN sys .objects o on o. [object_id] = m. [object_id]
        join sys .schemas s on s. schema_id = o. schema_id
WHERE (
        m.uses_database_collation = 1
               or
               m.is_schema_bound = 1
        )
        and o .[type] = 'V'


IF @@error <> 0
BEGIN
        PRINT '-- VIEW INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('VIEW INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[pk_constr] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [idx_name] [sysname]
)


CREATE TABLE tempdb. [dbo].[pk_constr_cols] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [idx_name] [sysname] ,
        [idx_type] [tinyint] ,
        [idx_type_desc] [nvarchar] (60),
        [is_unique] [bit] ,
        [ignore_dup_key] [bit] ,
        [is_unique_constraint] [bit] ,
        [fill_factor] [tinyint] ,
        [is_padded] [bit] ,
        [is_disabled] [bit] ,
        [allow_row_locks] [bit] ,
        [allow_page_locks] [bit] ,
        [col_name] [sysname] ,
        [key_ordinal] [tinyint] ,
        [is_descending_key] [bit] ,
        [is_included_column] [bit] ,
        [fg_name] [sysname]
)


PRINT '-- GET PK INFO'


INSERT INTO tempdb. [dbo].[pk_constr]
SELECT [table_name] = o.[name]
        , owner = s.[name]
        , [idx_name] = kc.[name]
FROM sys .key_constraints kc
        join sys .objects o on o. [object_id] = kc. [parent_object_id]
        join sys .schemas s on s. schema_id = o. schema_id
WHERE kc .[type] = 'PK'


IF @@error <> 0
BEGIN
        PRINT '-- PK INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('PK INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




INSERT INTO tempdb. [dbo].[pk_constr_cols]
SELECT [table_name] = o.[name]
               , owner = s.[name]
               , idx_name = kc.[name]
               , idx_type = i.[type]
               , idx_type_desc = i.type_desc
               , i .is_unique
               , i .ignore_dup_key
               , i .is_unique_constraint
               , i .fill_factor
               , i .is_padded
               , i .is_disabled
               , i .allow_row_locks
               , i .allow_page_locks
               , [col_name] = c.[name]
               , ic .key_ordinal
               , ic .is_descending_key
               , ic .is_included_column
               , [fg_name] = ds.[name]
FROM sys .key_constraints kc
        JOIN sys .objects o on o. [object_id] = kc. [parent_object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
        JOIN sys .indexes i on i. [object_id] = kc. [parent_object_id] AND i. index_id = kc .unique_index_id
        LEFT JOIN sys. index_columns ic on ic.[object_id] = i.[object_id] and ic.index_id = i.index_id
        LEFT JOIN sys. columns c on c.[object_id] = ic.[object_id] and c.column_id = ic.column_id
        JOIN sys .data_spaces ds on ds. data_space_id = i. data_space_id
WHERE kc .[type] = 'PK'
ORDER BY o. [name], s.[name] , i .[name], ic. key_ordinal


IF @@error <> 0
BEGIN
        PRINT '-- PK COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('PK COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[fk_constr] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [fk_constr] [sysname] ,
        [referenced_table] [sysname] ,
        [referenced_table_owner] [sysname] ,
        [update_referential_action] [tinyint] ,
        [delete_referential_action] [tinyint] ,
        [is_not_for_replication] [bit] ,
        [is_disabled] [bit] ,
        [const_id] [int]
)


CREATE TABLE tempdb. [dbo].[fk_constr_cols] (
        [foreign_key_name] [sysname] ,
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [constraint_column_name] [nvarchar] (128),
        [referenced_object] [sysname] ,
        [ref_owner] [sysname] ,
        [referenced_column_name] [nvarchar] (128),
        [is_disabled] [bit] ,
        [is_not_for_replication] [bit] ,
        [delete_referential_action] [tinyint] ,
        [update_referential_action] [tinyint] ,  
        [constraint_column_id] [int] ,
        [with_check] [varchar] (12)
)


PRINT '-- GET FOREIGN KEY CONSTRAINTS INFO'


INSERT INTO tempdb. [dbo].[fk_constr]
SELECT [table_name] = ot.[name]
               , [owner] = st.[name]
               , [fk_constr] = f.[name]
               , [referenced_table] = otr.[name]
               , [referenced_table_owner] = st1.[name]
               , f .[update_referential_action]
               , f .[delete_referential_action]
               , f .[is_not_for_replication]
               , f .[is_disabled]
               , [const_id] = f.[object_id]
FROM sys .foreign_keys f
        join sys .objects ot on ot. [object_id] = f. parent_object_id
        join sys .schemas st on st. schema_id = ot. schema_id
        join sys .objects otr on otr. [object_id] = f. referenced_object_id
        join sys .schemas st1 on st1. schema_id = otr. schema_id


IF @@error <> 0
BEGIN
        PRINT '-- FOREIGN KEY INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('FOREIGN KEY INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




PRINT '-- GET FOREIGN KEY CONSTRAINTS & COLUMNS INFO'
INSERT INTO tempdb. [dbo].[fk_constr_cols]
SELECT
        f.[name] AS foreign_key_name
        , ot .[name] AS table_name
        , st .[name] AS owner
        , COL_NAME (fc. parent_object_id, fc.parent_column_id ) AS constraint_column_name
        , otr .[name] AS referenced_object
        , st1 .[name] AS ref_owner
        , COL_NAME (fc. referenced_object_id, fc.referenced_column_id ) AS referenced_column_name
        , f .is_disabled      
        , f .[is_not_for_replication]
        , f .delete_referential_action--_desc
        , f .update_referential_action--_desc
        , fc .constraint_column_id
        , [with_check] = case f .is_not_trusted when 1 then 'WITH NOCHECK' else '' end
FROM sys .foreign_keys AS f
        JOIN sys .foreign_key_columns AS fc ON f.[object_id] = fc.constraint_object_id
        join sys .objects ot on ot. [object_id] = f. parent_object_id
        join sys .schemas st on st. schema_id = ot. schema_id
        join sys .objects otr on otr. [object_id] = f. referenced_object_id
        join sys .schemas st1 on st1. schema_id = otr. schema_id
        JOIN tempdb .dbo. [fk_constr] tfc on tfc.[const_id] = fc.constraint_object_id
order by f. [name], fc.constraint_column_id


IF @@error <> 0
BEGIN
        PRINT '-- FOREIGN KEY COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('FOREIGN KEY COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[def_constr] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [default_name] [sysname] ,
        [col_name] [sysname] ,
        [definition] [nvarchar] (max)
)


PRINT '-- GET DEFAULT CONSTRAINTS INFO'


INSERT INTO tempdb. [dbo].[def_constr]
SELECT [table_name] = o.[name]
               , [owner] = s.[name]
               , [default_name] = dc.[name]
               , [col_name] = c.[name]
               , dc .definition
FROM sys .default_constraints dc
        join sys .columns c on c. [object_id] = dc. parent_object_id and c. column_id = dc.parent_column_id
        join sys .objects o on o. [object_id] = dc. parent_object_id
        join sys .schemas s on s. schema_id = o. schema_id


IF @@error <> 0
BEGIN
        PRINT '-- DEFAULT CONSTRAINTS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('DEFAULT CONSTRAINTS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


CREATE TABLE tempdb. dbo.[check_constr] (
        [name] [sysname] ,
        [par_obj_name] [sysname] ,
        [owner] [sysname] ,
        [definition] [nvarchar] (max),
        [with_check] varchar (12)
)


PRINT '-- GET CHECK CONSTRAINTS INFO'


INSERT INTO tempdb. dbo.[check_constr]
SELECT cc .[name]
        , par_obj_name = o.[name]
        , owner = s.[name]
        , cc .definition
        , [with_check] = case cc .is_not_trusted when 1 then 'WITH NOCHECK' else '' end
FROM sys .check_constraints cc
        join sys .objects o on o. [object_id] = cc. parent_object_id
        join sys .schemas s on s. schema_id = o. schema_id


IF @@error <> 0
BEGIN
        PRINT '-- CHECK CONSTRAINTS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('CHECK CONSTRAINTS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[comp_cols] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [col_name] [sysname] ,
        [definition] [nvarchar] (max),
        [object_id] [int] ,
        [column_id] [int] ,
        [drop_safe] [int]
)


PRINT '-- GET COMPUTED COLUMNS INFO'


INSERT INTO tempdb. [dbo].[comp_cols]
SELECT table_name = o.[name]
        , owner = s.[name]
        , [col_name] = c.[name]
        , c .definition
        , c .[object_id]
        , c .column_id
        , drop_safe = isnull(b .recreate_manually, 0)
FROM sys .computed_columns c
        join sys .types t on t. user_type_id = c. user_type_id
        join sys .objects o on o. [object_id] = c. [object_id]
        join sys .schemas s on s. schema_id = o. schema_id
        left join (
               SELECT DISTINCT a. table_object_id, a.table_name
                      , recreate_manually = case when a. recreate_manually > 0 then 1 else 0 end
               FROM (
                            select [table_name] = o.[name]
                                   , [table_object_id] = cc.[object_id]
                                   , column_name = cc.[name]
                                   , cc .column_id
                                   , recreate_manually = ISNULL(
                                                                             (SELECT TOP 1 c .column_id FROM sys .columns c
                                                                            WHERE c.[object_id] = cc.[object_id]
                                                                                   AND c .column_id > cc .column_id
                                                                                   AND c .is_computed = 0 )
                                                                            , 0)
                            from sys .computed_columns cc
                                   join sys .objects o on o. [object_id] = cc.[object_id]


                            ) a
                      WHERE a .recreate_manually > 0
                      ) b on b.table_object_id = o.[object_id]
WHERE (c .system_type_id in ( 35, 99 , 167, 175, 231 , 239, 256)
               or
               c.uses_database_collation = 1 )
        and o .[type] = 'U'
ORDER BY s. [name], o.[name] , c .column_id


IF @@error <> 0
BEGIN
        PRINT '-- GET COMPUTED COLUMN INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('GET COMPUTED COLUMN INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[triggers] (
        [trigger_name] [sysname] ,
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [is_disabled] [bit] ,
        [is_not_for_replication] [bit] ,
        [is_instead_of_trigger] [bit] ,
        [definition] [nvarchar] (max),
        [ansi_nulls] int ,
        [quoted_identifier] int
)


PRINT '-- GET TRIGGER INFO'


INSERT INTO tempdb. [dbo].[triggers]
SELECT trigger_name = t.[name]
               , table_name = o.[name]
               , owner = s.[name]
               , t .is_disabled
               , t .is_not_for_replication
               , t .is_instead_of_trigger
               , m .definition
               , [ansi_nulls] = m.uses_ansi_nulls
               , [quoted_identifier] = m.uses_quoted_identifier
FROM sys .triggers t
        join sys .sql_modules m on m. [object_id] = t. [object_id]
        JOIN sys .objects o on o. [object_id] = t. parent_id
        JOIN sys .schemas s on s. schema_id = o. schema_id
WHERE t .parent_class = 1 -- only table & view triggers
        and t .[type] = 'TR'


IF @@error <> 0
BEGIN
        PRINT '-- GET TRIGGER INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('TRIGGER INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[trigger_order] (
        [trigger_name] [sysname] ,
        [owner] [sysname] ,
        [type_desc] [nvarchar] (60),
        [action] [varchar] (5)
)


PRINT '-- GET TRIGGER ORDER INFO'


INSERT INTO tempdb. [dbo].[trigger_order]
SELECT trigger_name = o.[name]
        , owner = s.[name]
        , te .[type_desc]
        , [action] = cast('First' as varchar(5 ))
--into a1
FROM sys .trigger_events te
        JOIN sys .objects o on o. [object_id] = te. [object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
        JOIN sys .triggers t on t. [object_id] = te. [object_id]
WHERE te .is_first = 1
        and t .parent_class = 1 -- only table & view triggers
        and t .[type] = 'TR'


UNION ALL


SELECT trigger_name = o.[name]
        , owner = s.[name]
        , te .[type_desc]
        , [action] = cast('Last' as varchar(5 ))
FROM sys .trigger_events te
        JOIN sys .objects o on o. [object_id] = te. [object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
        JOIN sys .triggers t on t. [object_id] = te. [object_id]
WHERE te .is_last = 1
        and t .parent_class = 1 -- only table & view triggers
        and t .[type] = 'TR'


IF @@error <> 0
BEGIN
        PRINT '-- GET TRIGGER ORDER INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('TRIGGER ORDER INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


CREATE TABLE tempdb. [dbo].[indexes] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [idx_name] [sysname] ,
        [is_unique_constraint] [bit]
)


CREATE TABLE tempdb. [dbo].[ind_columns] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [idx_name] [sysname] ,
        [idx_type] [tinyint] ,
        [idx_type_desc] [nvarchar] (60) ,
        [is_unique] [bit] ,
        [ignore_dup_key] [bit] ,
        [is_unique_constraint] [bit] ,
        [fill_factor] [tinyint] ,
        [is_padded] [bit] ,
        [is_disabled] [bit] ,
        [allow_row_locks] [bit] ,
        [allow_page_locks] [bit] ,
        [col_name] [sysname] ,
        [key_ordinal] [tinyint] ,
        [is_descending_key] [bit] ,
        [is_included_column] [bit] ,
        [fg_name] [sysname]
)


PRINT '-- GET INDEXES INFO'


INSERT INTO tempdb. [dbo].[indexes]
SELECT [table_name] = o.[name]
               , owner = s.[name]
               , idx_name = i.[name]
               , i .is_unique_constraint
FROM sys .indexes AS i
        JOIN sys .objects o on o. [object_id] = i. [object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
WHERE o .[type] = 'U'
        and i .is_primary_key = 0
        and i .[type] <> 0


IF @@error <> 0
BEGIN
        PRINT '-- GET INDEX INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('INDEX INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




INSERT INTO tempdb. [dbo].[ind_columns]
SELECT [table_name] = o.[name]
               , owner = s.[name]
               , idx_name = i.[name]
               , idx_type = i.[type]
               , idx_type_desc = i.type_desc
               , i .is_unique
               , i .ignore_dup_key
               , i .is_unique_constraint
               , i .fill_factor
               , i .is_padded
               , i .is_disabled
               , i .allow_row_locks
               , i .allow_page_locks
               , [col_name] = c.[name]
               , ic .key_ordinal
               , ic .is_descending_key
               , ic .is_included_column
               , [fg_name] = ds.[name]
FROM sys .indexes i
        JOIN sys .objects o on o. [object_id] = i. [object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
        left join sys. index_columns ic on ic.[object_id] = i.[object_id] and ic.index_id = i.index_id
        LEFT JOIN sys. columns c on c.[object_id] = ic.[object_id] and c.column_id = ic.column_id
        join sys .data_spaces ds on ds. data_space_id = i. data_space_id
WHERE o .[type] = 'U'
        and i .is_primary_key = 0
        and i .[type] <> 0
ORDER BY o. [name], s.[name] , i .[name], ic. key_ordinal


IF @@error <> 0
BEGIN
        PRINT '-- GET INDEX COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('INDEX COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


 
CREATE TABLE tempdb. [dbo].[permissions] (
        [object_id] [int] ,
        [state] [char] (1),
        [state_desc] [nvarchar] (60),
        [permission_name] [nvarchar] (128),
        [owner] [sysname] ,
        [obj_name] [sysname] ,
        [principal_name] [sysname] ,
        [col_name] [sysname]
)


PRINT '-- GET PERMISSIONS INFO'


INSERT INTO tempdb. [dbo].[permissions]
SELECT o .[object_id]
               , dp .state
               , dp .state_desc
               , dp .permission_name
               , [owner] = s.[name]
               , [obj_name] = o.[name]
               , [principal_name] = p.[name]
               , [col_name] = isnull(c .[name], '')
FROM  sys .database_permissions dp
        join sys .database_principals p on p. principal_id = dp. grantee_principal_id
        JOIN sys .objects o on dp. major_id = o. [object_id]
        join sys .schemas s on o. schema_id = s. schema_id
        left join sys. columns c on c.column_id = dp.minor_id and c.[object_id] = dp.major_id
WHERE major_id in --(1189579276, 78623323)
        (
               SELECT [object_id] FROM tempdb.dbo .udf
               UNION ALL
               SELECT [object_id] FROM tempdb.dbo .udv
               UNION ALL
               SELECT [object_id] FROM tempdb.dbo .[comp_cols]
        )
       
IF @@error <> 0
BEGIN
        PRINT '-- PERMISSIONS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('PERMISSIONS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


 
CREATE TABLE tempdb. [dbo].[user_stat] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [stat_name] [sysname] ,
        [no_recompute] [bit]
)


PRINT '-- GET USER STATISTICS INFO'


INSERT INTO tempdb. [dbo].[user_stat]
SELECT [table_name] = o.[name]
        , owner = sc.[name]
        , [stat_name] = s.[name]
        , s .no_recompute
FROM sys .stats s
        join sys .objects o on o. [object_id] = s. [object_id]
        join sys .schemas sc on sc. schema_id = o. schema_id
WHERE s .user_created = 1


IF @@error <> 0
BEGIN
        PRINT '-- USER STATISTICS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('USER STATISTICS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


PRINT '-- GET USER STATISTICS INFO WITH COLUMNS'


CREATE TABLE tempdb. [dbo].[user_stat_cols] (
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [stat_name] [sysname] ,
        [col_order] [int] ,
        [no_recompute] [bit] ,
        [column_name] [sysname]
)


INSERT INTO tempdb. [dbo].[user_stat_cols]
SELECT [table_name] = o.[name]
        , owner = sch.[name]
        , [stat_name] = s.[name]
        , [col_order] = sc.stats_column_id
        , s .no_recompute
        , column_name = c.[name]
FROM sys .stats s
        join sys .objects o on o. [object_id] = s. [object_id]
        join sys .schemas sch on sch. schema_id = o. schema_id
        join sys .stats_columns sc on sc. [object_id] = s. [object_id] and sc. stats_id = s .stats_id
        join sys .columns c on c. [object_id] = sc. [object_id] and c. column_id = sc.column_id
WHERE s .user_created = 1
ORDER BY sch. [name], o.[name] , s .[name], sc. stats_column_id


IF @@error <> 0
BEGIN
        PRINT '-- USER STATISTICS INFO WITH COLUMNS FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('USER STATISTICS INFO WITH COLUMNS FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
        RETURN
END


PRINT '--'
PRINT '--Collect info section finish time: ' + cast(getdate () as varchar( 40))
PRINT '--'
 


PRINT '--'
PRINT '--DROP info section start time: ' + cast(getdate () as varchar( 40))
PRINT '--'


 
PRINT '-- ***'
PRINT '-- DROP UDF'


SET @num_cycles = 0


TRY_DELETE_AGAIN:


SET @has_error = 0


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. udf WHERE isnull( [dropped], 0 ) = 0)
        GOTO SKIP_DROP_UDF


DECLARE ms_func_cursor CURSOR FOR
SELECT [object_id] , udf_name , owner
FROM tempdb .dbo. udf
WHERE isnull ([dropped], 0) = 0
 
open ms_func_cursor
fetch next from ms_func_cursor into @object_id, @table_name, @owner


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @owner + '].[' +
                      @table_name + ']'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))' + CHAR(13 ) + CHAR( 10) +
                      'DROP FUNCTION [' + @owner + '].[' + @table_name + ']'


        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
               BEGIN
                      PRINT '-- DROP FUNCTION FAILED. Wait for a next cycle.'
                     
                      SET @has_error = 1
               END
        ELSE
               BEGIN
                      PRINT '-- DROP FUNCTION SUCCEED'
                      UPDATE tempdb .dbo. udf SET [dropped] = 1 WHERE [object_id] = @object_id
                     
                      IF @@error <> 0
                            BEGIN
                                   PRINT '-- update temp table when DROP FUNCTION FAILED. SEE ERROR LOG FOR DETAILS.'
                                   RAISERROR('update temp table when DROP FUNCTION FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                                   RETURN
                            END
               END


        fetch next from ms_func_cursor into @object_id, @table_name, @owner
END
 
CLOSE ms_func_cursor
DEALLOCATE ms_func_cursor


SKIP_DROP_UDF:


IF @has_error = 0
        PRINT '-- DROP UDF passed.'
 


PRINT '-- ***'
PRINT '-- DROP VIEWS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. udv WHERE isnull( [dropped], 0 ) = 0)
        GOTO SKIP_DROP_UDV


DECLARE ms_view_cursor CURSOR FOR
SELECT [object_id] , udv_name , owner
FROM tempdb .dbo. udv
WHERE isnull ([dropped], 0) = 0


-- open cursor
open ms_view_cursor
fetch next from ms_view_cursor into @object_id, @table_name, @owner


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N''[' + @owner + '].[' +
                      @table_name + ']''))' + CHAR (13) + CHAR(10 ) +
                      'DROP VIEW [' + @owner + '].[' + @table_name + ']'


        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
               BEGIN
                      PRINT '-- DROP VIEW FAILED. Wait for a next cycle.'
                      SET @has_error = 1
               END
        ELSE
               BEGIN
                      PRINT '-- DROP VIEW SUCCEED'
                      UPDATE tempdb .dbo. udv SET [dropped] = 1 WHERE [object_id] = @object_id
                     
                      IF @@error <> 0
                            BEGIN
                                   PRINT '-- update temp table when DROP VIEW FAILED. SEE ERROR LOG FOR DETAILS.'
                                   RAISERROR('update temp table when DROP VIEW FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                                   RETURN
                            END
               END


        fetch next from ms_view_cursor into @object_id, @table_name, @owner
END
 
CLOSE ms_view_cursor
DEALLOCATE ms_view_cursor


SKIP_DROP_UDV:


IF @has_error = 1
        BEGIN
               SET @num_cycles = @num_cycles + 1
               IF @num_cycles > @max_cycles
                      BEGIN
                            PRINT '-- maximum count of attempts when DROP VIEW is reached. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('maximum count of attempts when DROP VIEW is reached. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                            RETURN
                      END
               ELSE
                      BEGIN
                            PRINT '-- trying to delete binding objects again...(' + cast (@num_cycles as varchar (3)) + ')'
                            GOTO TRY_DELETE_AGAIN
                      END
        END


PRINT '-- DROP VIEW passed.'


 
PRINT '-- ***'
PRINT '-- DROP TRIGGERS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [triggers])
        GOTO SKIP_DROP_TRIGGERS


DECLARE ms_trig_cursor CURSOR FOR
SELECT [trigger_name] , [table_name] , [owner] FROM tempdb.dbo .[triggers]


-- open cursor
open ms_trig_cursor
fetch next from ms_trig_cursor into @obj_name, @table_name, @owner


WHILE @@fetch_status >= 0
BEGIN
--     SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DISABLE TRIGGER [' + @obj_name + ']'
        SET @stmt = 'DROP TRIGGER [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '--DROP TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
               RETURN
        END


        fetch next from ms_trig_cursor into @obj_name, @table_name, @owner
END
 
-- close cursor
CLOSE ms_trig_cursor
DEALLOCATE ms_trig_cursor


SKIP_DROP_TRIGGERS:


PRINT '-- DROP TRIGGERS PASSED.'
 
PRINT '-- ***'
PRINT '-- DROP HAND-MADE STATISTICS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [user_stat])
        GOTO SKIP_DROP_USER_STAT


DECLARE ms_stat_cursor CURSOR FOR
SELECT owner , table_name , stat_name FROM tempdb.dbo .[user_stat]


open ms_stat_cursor
fetch next from ms_stat_cursor into @owner, @table_name, @obj_name


WHILE @@fetch_status >= 0
BEGIN
 


        SET @stmt = 'DROP STATISTICS [' + @owner + '].[' + @table_name + '].[' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DROP STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
               RETURN
        END


        fetch next from ms_stat_cursor into   @owner, @table_name, @obj_name
END
 
CLOSE ms_stat_cursor
DEALLOCATE ms_stat_cursor


SKIP_DROP_USER_STAT:


PRINT '-- DROP HAND-MADE STATISTICS passed'
 
PRINT '-- ***'
PRINT '-- DROP FK'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [fk_constr])
        GOTO SKIP_DROP_FK


DECLARE ms_fk_cursor CURSOR FOR
SELECT owner , table_name , fk_constr FROM tempdb.dbo .[fk_constr]


open ms_fk_cursor


fetch next from ms_fk_cursor into @owner, @table_name, @obj_name


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DROP FK FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP FK FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
               RETURN
        END


        fetch next from ms_fk_cursor into @owner, @table_name, @obj_name
END
 
CLOSE ms_fk_cursor
DEALLOCATE ms_fk_cursor


SKIP_DROP_FK:


PRINT '-- DROP FK passed'
 
PRINT '-- ***'
PRINT '-- DROP DEFAULT CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [def_constr])
        GOTO SKIP_DROP_DEFAULTS


DECLARE ms_dc_cursor CURSOR FOR
SELECT [table_name] , [owner] , default_name FROM tempdb.dbo .[def_constr]


open ms_dc_cursor
fetch next from ms_dc_cursor into @table_name, @owner, @obj_name


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DROP DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP DROP DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_dc_cursor into @table_name, @owner, @obj_name
END


-- close cursor
CLOSE ms_dc_cursor
DEALLOCATE ms_dc_cursor


SKIP_DROP_DEFAULTS:


PRINT '-- DROP DEFAULT CONSTRAINTS passed'
PRINT '-- ***'
PRINT '-- DROP CHECK CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [check_constr])
        GOTO SKIP_DROP_CHECK_CONSTR


DECLARE ms_cc_cursor CURSOR FOR
SELECT [name] , [owner] , par_obj_name FROM tempdb.dbo .[check_constr]


open ms_cc_cursor
fetch next from ms_cc_cursor into @obj_name, @owner, @table_name


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_cc_cursor into @obj_name, @owner, @table_name
END
 
CLOSE ms_cc_cursor
DEALLOCATE ms_cc_cursor


SKIP_DROP_CHECK_CONSTR:


PRINT '-- DROP CHECK CONSTRAINTS failed'
 
PRINT '-- ***'
PRINT '-- DROP INDEXES'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. indexes)
        GOTO SKIP_DROP_INDEXES


DECLARE ms_ind_cursor CURSOR FOR
SELECT table_name , [owner] , [idx_name] , is_unique_constraint FROM tempdb.dbo .indexes


open ms_ind_cursor
fetch next from ms_ind_cursor into @table_name, @owner, @obj_name, @flag


WHILE @@fetch_status >= 0
BEGIN
        IF @flag = 1
               BEGIN
                      SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']'
               END
        ELSE
               BEGIN
                      SET @stmt = 'DROP INDEX [' + @obj_name + '] ON [' + @owner + '].[' + @table_name + ']'
               END
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DROP INDEX (OR CONSTRAINT) FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP INDEX (OR CONSTRAINT) FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_ind_cursor into @table_name, @owner, @obj_name, @flag
END
 
CLOSE ms_ind_cursor
DEALLOCATE ms_ind_cursor


SKIP_DROP_INDEXES:


PRINT '-- DROP INDEXES passes'
 
PRINT '-- ***'
PRINT '-- DROP PK'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [pk_constr])
        GOTO SKIP_DROP_PK


DECLARE ms_pk_cursor CURSOR FOR
SELECT table_name , [owner] , [idx_name] FROM tempdb.dbo .[pk_constr]


open ms_pk_cursor
fetch next from ms_pk_cursor into @table_name, @owner, @obj_name


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP CONSTRAINT [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '--DROP PK FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DROP PK FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
               RETURN
        END


        fetch next from ms_pk_cursor into @table_name, @owner, @obj_name
END


-- close cursor
CLOSE ms_pk_cursor
DEALLOCATE ms_pk_cursor


SKIP_DROP_PK:


PRINT '-- DROP PK passed'


 
PRINT '-- ***'
PRINT '-- DROP COMPUTED COLUMNS'
 
IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [comp_cols] WHERE drop_safe = 0)
        GOTO SKIP_DROP_COMP_COLS


DECLARE ms_comp_col_cursor CURSOR FOR
SELECT table_name , owner , [col_name]
FROM tempdb .dbo. [comp_cols]
WHERE drop_safe = 0


open ms_comp_col_cursor
fetch next from ms_comp_col_cursor into @table_name, @owner, @obj_name


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DROP COLUMN [' + @obj_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- ALTER COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('ALTER COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_comp_col_cursor into @table_name, @owner, @obj_name
END


-- close cursor
CLOSE ms_comp_col_cursor
DEALLOCATE ms_comp_col_cursor


SKIP_DROP_COMP_COLS:


PRINT '-- DROP COMPUTED COLUMNS passed.'


 


PRINT '-- ***'
PRINT '-- GET BAD TABLES INFO'


CREATE TABLE tempdb. [dbo].[bad_tables] (
        [object_id] [int] NOT NULL,
        [table_name] [sysname] ,
        [owner] [sysname] ,
        [file_group] [sysname]
)


INSERT INTO tempdb. dbo.[bad_tables]
SELECT DISTINCT o. [object_id]
        , [table_name] = o.[name]
        , [owner] = s.[name]
        , [file_group] = d.[name]
FROM sys .objects o
        JOIN tempdb .dbo. comp_cols c on c.[object_id] = o.[object_id]
        JOIN sys .schemas s on s. schema_id = o. schema_id
        JOIN sys .indexes i on i. [object_id] = o. [object_id]
        JOIN sys .data_spaces d on d. data_space_id = i. data_space_id
where o .[type] = 'U' -- [name] = 'a'
        and i .index_id < 2
        and c .[drop_safe] <> 0


IF @@error <> 0
BEGIN
        PRINT '-- GET BAD TABLES INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('GET BAD TABLES INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END




CREATE TABLE tempdb. [dbo].[bad_table_cols] (
        [object_id] [int] NOT NULL,
        [col_name] [sysname] ,
        [column_id] [int] NOT NULL,
        [length] [int] NULL,
        [precision] [tinyint] NOT NULL,
        [scale] [tinyint] NOT NULL,
        [type_name] [sysname] ,
        [nullable] [varchar] (8),
        [is_user_defined] [bit] NOT NULL,
        [is_identity] [bit] NOT NULL,
        [is_computed] [bit] NOT NULL,
        [system_type_id] [tinyint] NOT NULL,
        [seed_value] [int] NULL,
        [increment_value] [int] NULL,
        [definition] [nvarchar] (max)
)


PRINT '-- GET BAD TABLE COLUMNS INFO'


INSERT INTO tempdb. dbo.[bad_table_cols]
SELECT c .[object_id]
        , [col_name] = c.[name]
        , c .column_id
        , length = case when t. [name] like 'n%' then c .max_length / 2  else c.max_length end
        , [precision] = c.[precision]
        , c .scale
        , type_name = t.[name]
        , nullable = case when c. is_nullable=0 then 'NOT ' else '' end + 'NULL'
        , t .is_user_defined
        , c .is_identity
        , c .is_computed
        , [system_type_id] = c.user_type_id -- system_type_id
        , seed_value = cast(i .seed_value as int )
        , increment_value = cast(i .increment_value as int )
        , [definition] = case c .is_computed when 0 then '' else cc .definition end
FROM sys .columns c
        LEFT JOIN tempdb. dbo.comp_cols cc on cc .[object_id] = c .[object_id] and cc.[column_id] = c.[column_id]
        JOIN ( SELECT DISTINCT [object_id], [drop_safe] FROM tempdb .dbo. comp_cols) c1 on c1. [object_id] = c. [object_id]
        JOIN sys .types t on t. system_type_id = c. system_type_id and t. user_type_id = c .user_type_id
        LEFT JOIN sys. identity_columns i on c.[object_id] = i.[object_id] and c.column_id = i.column_id
WHERE c1 .[drop_safe] <> 0
--     and c.[object_id] = 2099048
ORDER BY c. [object_id], c.column_id


IF @@error <> 0
BEGIN
        PRINT '-- GET BAD TABLE COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('GET BAD TABLE COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


PRINT '--'
PRINT '--Special section for copying table start time: ' + cast(getdate () as varchar(40 ))
PRINT '--'


 
PRINT '-- ***'
PRINT '-- COPY TABLES FOR COMPUTED COLUMNS'


-- only "good columns"
IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [bad_tables])
        GOTO SKIP_COPY_COMP_COLS


DECLARE ms_comp_col_cursor CURSOR FOR
SELECT [object_id] , table_name , owner , file_group
FROM tempdb .dbo. [bad_tables]


open ms_comp_col_cursor
fetch next from ms_comp_col_cursor into @obj_id, @table_name, @owner, @obj_name


WHILE @@fetch_status >= 0
BEGIN


        PRINT '-- COPYING TABLE ' + @owner + '.' + @table_name + '....'
 
        SET @field_list = ''


        SELECT @field_list = '[' + [col_name] + '],' + @field_list
        FROM tempdb .dbo. bad_table_cols
        WHERE [object_id] = @obj_id --1781581385
               and is_computed = 0
        ORDER BY column_id


        SET @field_list = left(@field_list, len( @field_list)-1 )
 
        IF  EXISTS (SELECT * FROM tempdb .sys. objects
                            WHERE [object_id] = OBJECT_ID('tempdb.[' + @owner + '].[a1234___' + @table_name + ']' ) AND type in ( N'U'))
               BEGIN
                      SET @stmt = 'DROP TABLE tempdb.[' + @owner + '].[a1234___' + @table_name + ']'
                      PRINT @stmt
                     
                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                            BEGIN
                                   PRINT '-- DROP EXISTING TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
                                   RAISERROR('DROP EXISTING TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                                   RETURN
                            END


               END
        ELSE
               PRINT '-- TABLE tempdb.[' + @owner + '].[a1234___' + @table_name + '] does not exists'
 
        SET @stmt = 'SELECT ' + @field_list COLLATE $( desired_collation) + ' INTO tempdb.['
                                          + @owner COLLATE $ (desired_collation) + '].[a1234___'
                                          + @table_name COLLATE $(desired_collation) + '] FROM ['
                                          + @owner COLLATE $ (desired_collation) + '].['
                                          + @table_name COLLATE $(desired_collation) + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- COPY TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('COPY TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END
 
        SET @stmt = 'DROP TABLE [' + @owner + '].[' + @table_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- FAILED TO DROP TABLE FOR COMPUTED COLUMN. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('FAILED TO DROP TABLE FOR COMPUTED COLUMN. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_comp_col_cursor into @obj_id, @table_name, @owner, @obj_name
END


-- close cursor
CLOSE ms_comp_col_cursor
DEALLOCATE ms_comp_col_cursor


SKIP_COPY_COMP_COLS:


PRINT '-- COPY TABLES FOR COMPUTED COLUMNS passed'
 
CREATE TABLE tempdb. [dbo].[char_cols] (
        [col_id] [int] NOT NULL,
        [owner] [sysname] ,
        [table_name] [sysname] ,
        [col_name] [sysname] ,
        [length] [int] ,
        [type_name] [sysname] ,
        [nullable] [varchar] (8),
        [is_user_defined] [bit]
)


PRINT '-- GET CHAR COLUMNS INFO'


INSERT INTO tempdb. [dbo].[char_cols]
SELECT  col_id = c.[object_id]
               , [owner] = s.[name]
               , [table_name] = o.[name]
               , [col_name] = c.[name]
               , length = case when t. [name] like 'n%' then c .max_length / 2  else c.max_length end
               , type_name = t.[name]
               , nullable = case when c. is_nullable=0 then 'NOT ' else '' end + 'NULL'
               , t .is_user_defined
--            , c.*, o.*, t.*
FROM sys .columns c
        join sys .objects o on o. [object_id] = c. [object_id]
        join sys .types t on t. system_type_id = c. system_type_id and t. user_type_id = c .user_type_id
        join sys .schemas s on s. schema_id = o. schema_id
WHERE c .system_type_id in ( 35, 99 , 167, 175, 231 , 239, 256)
        and o .[type] = 'U'
        and c .is_computed = 0


IF @@error <> 0
BEGIN
        PRINT '-- CHAR COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('CHAR COLUMNS INFO FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


PRINT '--'
PRINT '--DROP section finish time: ' + cast(getdate () as varchar( 40))
PRINT '--'


PRINT '--'
PRINT '--Change collation section start time: ' + cast(getdate () as varchar( 40))
PRINT '--'
 
PRINT '-- ******************'
PRINT '-- TRY TO CHANGE COLLATION....'
 
PRINT 'ALTER DATABASE $(destdb) COLLATE $(desired_collation)'
ALTER DATABASE $(destdb ) COLLATE $(desired_collation )


IF @@error <> 0
BEGIN
        PRINT '-- ALTER DATABASE COLLATION FAILED, PLEASE CHECK THE REASON, ATTACH DB AND RUN AGAIN.'
        RAISERROR('ALTER DATABASE COLLATION FAILED, PLEASE CHECK THE REASON, ATTACH DB AND RUN AGAIN.', 16, 1)
        RETURN
END


PRINT '-- ***'
PRINT '-- DB COLLATION HAS BEEN CHANGED SUCCESSFULLY'


PRINT '-- ***'
PRINT '-- COLUMNS COLLATION IN PROGRESS...'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. char_cols)
        GOTO SKIP_DROP_CHAR_COLS


DECLARE ms_col_cursor CURSOR FOR
select owner , table_name , [col_name] , length , type_name , nullable , is_user_defined
from tempdb .dbo. char_cols


open ms_col_cursor
fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ALTER COLUMN [' + @col_name + '] '
               + @type_name


        -- length
        IF NOT (UPPER (@type_name COLLATE DATABASE_DEFAULT ) LIKE '%TEXT' OR
                      UPPER(@type_name COLLATE DATABASE_DEFAULT) = 'SYSNAME' OR
                      @flag = 1)
               SET @stmt = @stmt + '(' + cast (@length as varchar (4)) + ')'


        IF @flag = 0
               SET @stmt = @stmt + ' COLLATE DATABASE_DEFAULT '
 
        SET @stmt = @stmt + ' ' + @nullable
       
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('ALTER COLUMN FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
               RETURN
        END


        fetch next from ms_col_cursor into @owner, @table_name, @col_name, @length, @type_name, @nullable, @flag
END


-- close cursor
CLOSE ms_col_cursor
DEALLOCATE ms_col_cursor


SKIP_DROP_CHAR_COLS:


PRINT '-- ***'
PRINT '-- CHANGING COLUMNS COLLATION - done!'


PRINT '--'
PRINT '--Change collation section end time: ' + cast(getdate () as varchar( 40))
PRINT '--'


 
PRINT '--'
PRINT '--Restore bad tables section start time: ' + cast(getdate () as varchar( 40))
PRINT '--'




PRINT '-- ***'
PRINT '-- RESTORING BAD TABLES'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [bad_tables])
        GOTO SKIP_RESTORE_TABLES


DECLARE ms_comp_col_cursor CURSOR FOR
SELECT [object_id] , table_name , owner , file_group
FROM tempdb .dbo. [bad_tables]


open ms_comp_col_cursor
fetch next from ms_comp_col_cursor into @obj_id, @table_name, @owner, @obj_name


WHILE @@fetch_status >= 0
BEGIN


        PRINT '-- RESTORING TABLE ' + @owner + '.' + @table_name + '....'
 
        SET @field_list = ''


        SELECT @field_list = '[' + [col_name] + '],' + @field_list
        FROM tempdb .dbo. bad_table_cols
        WHERE [object_id] = @obj_id --1781581385
               and is_computed = 0
        ORDER BY column_id DESC -- for ordered scan


        SET @field_list = left(@field_list, len( @field_list)-1 )
 


        SET @stmt = 'CREATE TABLE [' + @owner + '].[' + @table_name + '] ('


        DECLARE ms_bad_comp_col_cursor CURSOR FOR
        SELECT [col_name] , length , [precision] , scale , type_name , nullable , is_user_defined, is_identity, is_computed
               , system_type_id , seed_value , increment_value , [definition]
        FROM tempdb .dbo. [bad_table_cols]
        WHERE [object_id] = @obj_id
        ORDER BY column_id ASC


        open ms_bad_comp_col_cursor
        fetch next from ms_bad_comp_col_cursor
        into @col_name , @length , @precision , @scale , @type_name , @nullable , @is_user_defined, @is_identity
               , @is_computed , @system_type_id , @seed , @increment , @definition


        WHILE @@fetch_status >= 0
        BEGIN
              
               SET @field = CHAR(10 ) /*+ CHAR(13) */ + @col_name + ' '
               SET @field = @field +
                      case
                      when @is_computed = 1 then 'AS ' + @definition COLLATE $(desired_collation)
                      when @is_user_defined = 1 then '[' + @type_name COLLATE $(desired_collation) + ']'
                      when @is_identity = 1 then '[' + @type_name COLLATE $(desired_collation) + '] IDENTITY ('
                            + cast (@seed as varchar (10)) COLLATE $(desired_collation) + ','
                            + cast (@increment as varchar (10)) COLLATE $(desired_collation) + ')'
                      -- text, ntext, image, sysname
                      when @system_type_id in (34, 35, 99 , 256) then '[' + @type_name COLLATE $(desired_collation ) + ']'
                      -- bigint, bit, datetime, int, smalldatetime, smallint, sql_variant, timestamp, tinyint, real
                      --     , uniqueidentifier, money, smallmoney
                      when @system_type_id in (127, 104, 61 , 56, 58, 52 , 98, 189, 48, 36, 60, 122 , 59)
                            then '[' + @type_name COLLATE $(desired_collation ) + ']'
                      -- binary & varbinary
                      when @system_type_id in (173, 165) then '[' + @type_name COLLATE $( desired_collation)
                            + '] (' + cast(@length as varchar(10 )) COLLATE $(desired_collation) + ')'
                      -- char, nchar, nvarchar, varchar
                      when @system_type_id in (175, 239, 231 , 167) then '[' + @type_name COLLATE $(desired_collation )
                            + '] (' + cast(@length as varchar(10 )) COLLATE $(desired_collation) + ')'
                      -- float
                      when @system_type_id in (62) then '[' + @type_name COLLATE $(desired_collation) + '] ('
                            + cast (@precision as varchar (10)) COLLATE $(desired_collation) + ')'
                      -- decimal & numeric
                      when @system_type_id in (106, 108) then '[' + @type_name COLLATE $( desired_collation) + '] ('
                            + cast (@precision as varchar (10)) COLLATE $(desired_collation)
                            + ',' + cast(@scale as varchar(10 )) COLLATE $(desired_collation) + ')'
                      end


               IF @is_computed <> 1
                      SET @field = @field COLLATE $(desired_collation ) + ' ' + @nullable COLLATE $(desired_collation )


               SET @field = @field COLLATE $(desired_collation ) + ',' COLLATE $(desired_collation)


               --PRINT @field


               SET @stmt = @stmt COLLATE $(desired_collation ) + @field COLLATE $(desired_collation)


               fetch next from ms_bad_comp_col_cursor
               into @col_name , @length , @precision , @scale , @type_name , @nullable , @is_user_defined, @is_identity
                      , @is_computed , @system_type_id , @seed , @increment , @definition
        END


        -- close cursor
        CLOSE ms_bad_comp_col_cursor
        DEALLOCATE ms_bad_comp_col_cursor


        -- create table section finished


        SET @stmt = left(@stmt, len( @stmt)-1 ) -- minus comma


        SET @stmt = @stmt + ') ON [' + @obj_name + ']'
        PRINT '---'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- FAILED TO CREATE TABLE TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('FAILED TO CREATE TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        PRINT '---'    


        -- is identity column here?
        IF EXISTS(     SELECT 1 FROM tempdb .dbo. bad_table_cols
                            WHERE [object_id] = @obj_id --1781581385
                                   and is_identity = 1 )
               SET @flag = 1
        ELSE
               SET @flag = 0


        SET @stmt = ''
        IF @flag = 1
               BEGIN
                      SET @stmt = 'SET IDENTITY_INSERT [' + @owner COLLATE $(desired_collation) + '].['
                            + @table_name COLLATE $ (desired_collation) + '] ON ' + CHAR(10 )
                      -- PRINT @stmt
                      -- IF $(script_only) = 0
                      -- EXEC(@stmt)
               END


        -- insert into
        SET @stmt = @stmt + 'INSERT INTO [' + @owner COLLATE $ (desired_collation) + '].['
                            + @table_name COLLATE $ (desired_collation) + '] ('
                            + @field_list COLLATE $ (desired_collation) + ') SELECT ' + @field_list COLLATE $(desired_collation )
                            + ' FROM tempdb.[' + @owner COLLATE $(desired_collation) + '].[a1234___'
                            + @table_name COLLATE $ (desired_collation) + ']'


        IF @flag = 1
               BEGIN
                      SET @stmt = @stmt + CHAR( 10) + 'SET IDENTITY_INSERT [' + @owner COLLATE $(desired_collation ) + '].['
                                   + @table_name COLLATE $ (desired_collation) + '] OFF'
                      -- PRINT @stmt
                      -- IF $(script_only) = 0
                      --       EXEC(@stmt)
               END


        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- RESTORING TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('RESTORING TABLE FOR COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        -- drop shadow table
        SET @stmt = 'DROP TABLE tempdb.[' + @owner COLLATE $( desired_collation) + '].[a1234___'
                            + @table_name COLLATE $ (desired_collation) + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- FAILED TO DROP SHADOW TABLE FOR COMPUTED COLUMN. SEE ERROR LOG FOR DETAILS.'
 
        END


        fetch next from ms_comp_col_cursor into @obj_id, @table_name, @owner, @obj_name
END


-- close cursor
CLOSE ms_comp_col_cursor
DEALLOCATE ms_comp_col_cursor


SKIP_RESTORE_TABLES:


PRINT '-- RESTORING TABLES FOR COMPUTED COLUMNS passed'


PRINT '--'
PRINT '--Restore bad tables section end time: ' + cast(getdate () as varchar( 40))
PRINT '--'
 
PRINT '-- ***'
PRINT '-- RE-CREATE COMPUTED COLUMNS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [comp_cols] WHERE drop_safe = 0)
        GOTO SKIP_CREATE_COMP_COLS
 
DECLARE ms_comp_col_cursor2 CURSOR FOR
SELECT table_name , owner , [col_name] , definition FROM tempdb.dbo .[comp_cols]
WHERE drop_safe = 0
order by owner, table_name, column_id


open ms_comp_col_cursor2
fetch next from ms_comp_col_cursor2 into @table_name, @owner, @col_name, @obj_def


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD [' + @col_name + '] AS ' + @obj_def
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- CREATE COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('CREATE COMPUTED COLUMN FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_comp_col_cursor2 into @table_name, @owner, @col_name, @obj_def
END


-- close cursor
CLOSE ms_comp_col_cursor2
DEALLOCATE ms_comp_col_cursor2


SKIP_CREATE_COMP_COLS:


PRINT '-- RE-CREATE COMPUTED COLUMNS passed'
 
PRINT '-- ***'
PRINT '-- CREATE PRIMARY KEY CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [pk_constr_cols])
        GOTO SKIP_CREATE_PK


DECLARE ms_pk_cursor2 CURSOR FOR
SELECT        [table_name], [owner], [idx_name], [idx_type], [idx_type_desc], [is_unique], [ignore_dup_key],
        [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [allow_row_locks], [allow_page_locks],
        [col_name], [key_ordinal], [is_descending_key], [is_included_column], [fg_name]
FROM tempdb .dbo. [pk_constr_cols]
order by [owner], [table_name], [idx_name], key_ordinal


open ms_pk_cursor2
fetch next from ms_pk_cursor2 into @table_name, @owner, @idx_name, @idx_type, @idx_type_desc, @is_unique, @ignore_dup_key,
        @is_unique_constraint, @fill_factor, @is_padded, @is_disabled, @allow_row_locks, @allow_page_locks,
        @col_name, @key_ordinal, @is_descending_key, @is_included_column, @fg_name


SELECT @col_list1 = '[' + @col_name + ']' + case @is_descending_key when 0 then ' ASC' else ' DESC' end


WHILE @@fetch_status >= 0
BEGIN


        fetch next from ms_pk_cursor2 into @table_name1, @owner1, @idx_name1, @idx_type1, @idx_type_desc1, @is_unique1
               , @ignore_dup_key1 , @is_unique_constraint1 , @fill_factor1 , @is_padded1, @is_disabled1, @allow_row_locks1
               , @allow_page_locks1 , @col_name1 , @key_ordinal1 , @is_descending_key1, @is_included_column1, @fg_name1


        IF @@fetch_status < 0 BREAK


        IF @table_name = @table_name1 AND @owner = @owner1 AND @idx_name = @idx_name1
         BEGIN
                      SET @col_list1 = @col_list1 COLLATE $(desired_collation )
                            + ',[' + @col_name1 COLLATE $(desired_collation ) + ']'
                            + case @is_descending_key when 0 then ' ASC' else ' DESC' end COLLATE $( desired_collation)
         END
        ELSE
         BEGIN
       
               SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD CONSTRAINT [' + @idx_name
                      + '] PRIMARY KEY ' + @idx_type_desc + ' (' + @col_list1 + ')'


               SET @stmt = @stmt + ' WITH ('
               SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


               IF @fill_factor <> 0
                      SET @stmt = @stmt + ', FILLFACTOR=' + cast (@fill_factor as varchar(3 ))


               SET @stmt = @stmt + ') ON [' + @fg_name + ']'


               PRINT @stmt
               IF $( script_only) = 0
                      EXEC(@stmt )


               IF @@error <> 0
               BEGIN
                      PRINT '-- CREATE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('CREATE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      RETURN
               END


               IF @is_disabled = 1
               BEGIN
                      SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @idx_name + ']'
                      PRINT @stmt
                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                      BEGIN
                            PRINT '-- DISABLE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('DISABLE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                            RETURN
                      END


               END


               SELECT @table_name = @table_name1, @owner = @owner1, @idx_name = @idx_name1, @idx_type = @idx_type1
                      , @idx_type_desc = @idx_type_desc1, @is_unique = @is_unique1, @ignore_dup_key = @ignore_dup_key1
                      , @is_unique_constraint = @is_unique_constraint1, @fill_factor = @fill_factor1, @is_padded = @is_padded1
                      , @is_disabled = @is_disabled1, @allow_row_locks = @allow_row_locks1, @allow_page_locks = @allow_page_locks1
                      , @col_name = @col_name1, @key_ordinal = @key_ordinal1, @is_descending_key = @is_descending_key1
                      , @is_included_column = @is_included_column1, @fg_name = @fg_name1


               SELECT @col_list1 = '[' + @col_name + ']' + case @is_descending_key when 0 then ' ASC' else ' DESC' end


         END


END


CLOSE ms_pk_cursor2
DEALLOCATE ms_pk_cursor2
       
SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD CONSTRAINT [' + @idx_name
        + '] PRIMARY KEY ' + @idx_type_desc + ' (' + @col_list1 + ')'


SET @stmt = @stmt + ' WITH ('
SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


IF @fill_factor <> 0
        SET @stmt = @stmt + ', FILLFACTOR=' + cast (@fill_factor as varchar (3))


SET @stmt = @stmt + ') ON [' + @fg_name + ']'


PRINT @stmt


IF $( script_only) = 0
        EXEC(@stmt )


IF @@error <> 0
BEGIN
        PRINT '-- CREATE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('CREATE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


IF @is_disabled = 1
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @idx_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DISABLE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DISABLE PK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


END


SKIP_CREATE_PK:


PRINT '-- CREATE PRIMARY KEY CONSTRAINTS passed'


PRINT '-- ***'
PRINT '-- CREATE DEFAULT CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [def_constr])
        GOTO SKIP_CREATE_DEFAULTS


DECLARE ms_dc_cursor2 CURSOR FOR
SELECT [table_name] , [owner] , default_name , [col_name] , definition FROM tempdb.[dbo] .[def_constr]


open ms_dc_cursor2
fetch next from ms_dc_cursor2 into @table_name, @owner, @obj_name, @col_name, @obj_def


WHILE @@fetch_status >= 0
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD CONSTRAINT [' +
                      @obj_name + '] DEFAULT ' + @obj_def + ' FOR ['  + @col_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- CREATE DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('CREATE DEFAULT CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_dc_cursor2 into @table_name, @owner, @obj_name, @col_name, @obj_def
END


CLOSE ms_dc_cursor2
DEALLOCATE ms_dc_cursor2


SKIP_CREATE_DEFAULTS:


PRINT '-- CREATE DEFAULT CONSTRAINTS passed'


PRINT '-- ***'
PRINT '-- CREATE CHECK CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [check_constr])
        GOTO SKIP_CREATE_CHECK_CONSTRAINTS


DECLARE ms_cc_cursor2 CURSOR FOR
SELECT [name] , [owner] , par_obj_name , definition , with_check FROM tempdb.dbo .[check_constr]


open ms_cc_cursor2
fetch next from ms_cc_cursor2 into @obj_name, @owner, @table_name, @obj_def, @with_check


WHILE @@fetch_status >= 0
BEGIN


        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ' + @with_check + ' ADD CONSTRAINT [' +
                      @obj_name + '] CHECK ' + @obj_def
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- CREATE CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('CREATE CHECK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


        fetch next from ms_cc_cursor2 into @obj_name, @owner, @table_name, @obj_def, @with_check
END


CLOSE ms_cc_cursor2
DEALLOCATE ms_cc_cursor2


SKIP_CREATE_CHECK_CONSTRAINTS:


PRINT '-- CREATE CHECK CONSTRAINTS passed'


PRINT '-- ***'
PRINT '-- CREATE INDEXES'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [ind_columns])
        GOTO SKIP_CREATE_INDEXES


DECLARE ms_ind_cursor2 CURSOR FOR
SELECT        [table_name], [owner], [idx_name], [idx_type], [idx_type_desc], [is_unique], [ignore_dup_key],
        [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [allow_row_locks], [allow_page_locks],
        [col_name], [key_ordinal], [is_descending_key], [is_included_column], [fg_name]
FROM tempdb .dbo. [ind_columns]
order by [owner], [table_name], [idx_name], key_ordinal


open ms_ind_cursor2
fetch next from ms_ind_cursor2 into @table_name, @owner, @idx_name, @idx_type, @idx_type_desc, @is_unique, @ignore_dup_key,
        @is_unique_constraint, @fill_factor, @is_padded, @is_disabled, @allow_row_locks, @allow_page_locks,
        @col_name, @key_ordinal, @is_descending_key, @is_included_column, @fg_name


SELECT @col_list1 = '[' + @col_name + ']' + case @is_descending_key when 0 then ' ASC' else ' DESC' end


WHILE @@fetch_status >= 0
BEGIN


        fetch next from ms_ind_cursor2 into @table_name1, @owner1, @idx_name1, @idx_type1, @idx_type_desc1, @is_unique1
               , @ignore_dup_key1 , @is_unique_constraint1 , @fill_factor1 , @is_padded1, @is_disabled1, @allow_row_locks1
               , @allow_page_locks1 , @col_name1 , @key_ordinal1 , @is_descending_key1, @is_included_column1, @fg_name1


        IF @@fetch_status < 0 BREAK


        IF @table_name = @table_name1 AND @owner = @owner1 AND @idx_name = @idx_name1
         BEGIN
                      SET @col_list1 = @col_list1 COLLATE $(desired_collation ) + ',['
                            + @col_name1 COLLATE $ (desired_collation) + ']'
                            + case @is_descending_key when 0 then ' ASC' else ' DESC' end COLLATE $( desired_collation)
         END
        ELSE
         BEGIN


               IF @is_unique_constraint = 1
                      BEGIN


                            SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD CONSTRAINT [' + @idx_name
                                   + '] UNIQUE ' + @idx_type_desc + ' (' + @col_list1 + ')'


                            SET @stmt = @stmt + ' WITH ('


                            SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


                            IF @fill_factor <> 0
                                   SET @stmt = @stmt + ', FILLFACTOR=' + cast(@fill_factor as varchar(3 ))


                            SET @stmt = @stmt + ') ON [' + @fg_name + ']'


                            PRINT @stmt


                            IF $( script_only) = 0
                                   EXEC(@stmt )


                            IF @@error <> 0
                            BEGIN
                                   PRINT '-- CREATE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                                   RAISERROR('CREATE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                   RETURN
                            END


                            IF @is_disabled = 1
                            BEGIN
                                   SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @idx_name + ']'
                                   PRINT @stmt


                                   IF $( script_only) = 0
                                          EXEC(@stmt )


                                   IF @@error <> 0
                                   BEGIN
                                          PRINT '-- DISABLE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                                          RAISERROR('DISABLE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                                          RETURN
                                   END


                            END


                      END
               ELSE
                      BEGIN
                            SET @stmt = 'CREATE ' +
                                   CASE @is_unique WHEN 1 THEN 'UNIQUE ' ELSE ' ' END + @idx_type_desc +
                                   ' INDEX [' + @idx_name + '] ON [' + @owner + '].[' + @table_name + '] (' + @col_list1 + ')'


                            SET @stmt = @stmt + ' WITH ('


                            SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
                            SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


                            IF @fill_factor <> 0
                                   SET @stmt = @stmt + ', FILLFACTOR=' + cast(@fill_factor as varchar(3 ))


                            SET @stmt = @stmt + ') ON [' + @fg_name + ']'


                            PRINT @stmt


                            IF $( script_only) = 0
                                   EXEC(@stmt )


                            IF @@error <> 0
                            BEGIN
                                   PRINT '-- CREATE INDEX FAILED. SEE ERROR LOG FOR DETAILS.'
                                   RAISERROR('CREATE INDEX FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                   RETURN
                            END


                            IF @is_disabled = 1
                            BEGIN
                                   SET @stmt = 'ALTER INDEX [' + @idx_name + ']' + ' ON [' + @owner + '].[' + @table_name + '] DISABLE'
                                   PRINT @stmt


                                   IF $( script_only) = 0
                                          EXEC(@stmt )


                                   IF @@error <> 0
                                   BEGIN
                                          PRINT '-- DISABLE INDEX FAILED. SEE ERROR LOG FOR DETAILS.'
                                          RAISERROR('DISABLE INDEX FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                          RETURN
                                   END


                            END


                      END




               SELECT @table_name = @table_name1, @owner = @owner1, @idx_name = @idx_name1, @idx_type = @idx_type1
                      , @idx_type_desc = @idx_type_desc1, @is_unique = @is_unique1, @ignore_dup_key = @ignore_dup_key1
                      , @is_unique_constraint = @is_unique_constraint1, @fill_factor = @fill_factor1, @is_padded = @is_padded1
                      , @is_disabled = @is_disabled1, @allow_row_locks = @allow_row_locks1, @allow_page_locks = @allow_page_locks1
                      , @col_name = @col_name1, @key_ordinal = @key_ordinal1, @is_descending_key = @is_descending_key1
                      , @is_included_column = @is_included_column1, @fg_name = @fg_name1


               SELECT @col_list1 = '[' + @col_name + ']' + case @is_descending_key when 0 then ' ASC' else ' DESC' end


         END


END


-- close cursor
CLOSE ms_ind_cursor2
DEALLOCATE ms_ind_cursor2


IF @is_unique_constraint = 1
        BEGIN
               SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ADD CONSTRAINT [' + @idx_name
                      + '] UNIQUE ' + @idx_type_desc + ' (' + @col_list1 + ')'


               SET @stmt = @stmt + ' WITH ('


               SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


               IF @fill_factor <> 0
                      SET @stmt = @stmt + ', FILLFACTOR=' + cast (@fill_factor as varchar(3 ))


               SET @stmt = @stmt + ') ON [' + @fg_name + ']'


               PRINT @stmt


               IF $( script_only) = 0
                      EXEC(@stmt )


               IF @@error <> 0
               BEGIN
                      PRINT '-- CREATE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('CREATE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      RETURN
               END


               IF @is_disabled = 1
               BEGIN
                      SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @idx_name + ']'
                      PRINT @stmt


                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                      BEGIN
                            PRINT '-- DISABLE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('DISABLE UNIQUE CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                            RETURN
                      END
               END


        END
ELSE
        BEGIN
               SET @stmt = 'CREATE ' +
                      CASE @is_unique WHEN 1 THEN 'UNIQUE ' ELSE ' ' END + @idx_type_desc +
                      ' INDEX [' + @idx_name + '] ON [' + @owner + '].[' + @table_name + '] (' + @col_list1 + ')'


               SET @stmt = @stmt + ' WITH ('


               SET @stmt = @stmt + 'PAD_INDEX=' + case @is_padded when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', IGNORE_DUP_KEY=' + case @ignore_dup_key when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_ROW_LOCKS=' + case @allow_row_locks when 0 then 'OFF' ELSE 'ON' END
               SET @stmt = @stmt + ', ALLOW_PAGE_LOCKS=' + case @allow_page_locks when 0 then 'OFF' ELSE 'ON' END


               IF @fill_factor <> 0
                      SET @stmt = @stmt + ', FILLFACTOR=' + cast (@fill_factor as varchar(3 ))


               SET @stmt = @stmt + ') ON [' + @fg_name + ']'


               PRINT @stmt


               IF $( script_only) = 0
                      EXEC(@stmt )


               IF @@error <> 0
               BEGIN
                      PRINT '-- CREATE INDEX FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('CREATE INDEX FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
                      RETURN
               END


               IF @is_disabled = 1
               BEGIN
                      SET @stmt = 'ALTER INDEX [' + @idx_name + ']' + ' ON [' + @owner + '].[' + @table_name + '] DISABLE'
                      PRINT @stmt


                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                      BEGIN
                            PRINT '-- DISABLE INDEX FAILED. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('DISABLE INDEX FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                            RETURN
                      END


               END
        END


SKIP_CREATE_INDEXES:


PRINT '-- CREATE INDEXES passed'




PRINT '-- ***'
PRINT '-- CREATE FK CONSTRAINTS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. fk_constr_cols)
        GOTO SKIP_CREATE_FK


DECLARE ms_fk_cursor2 CURSOR FOR
select foreign_key_name , table_name , owner , constraint_column_name , referenced_object, ref_owner, referenced_column_name
        , is_disabled , is_not_for_replication , delete_referential_action , update_referential_action, with_check
from tempdb .dbo. fk_constr_cols
order by foreign_key_name, constraint_column_id


open ms_fk_cursor2
fetch next from ms_fk_cursor2 into @fk_name, @table_name, @owner, @constraint_column_name
        , @referenced_object , @ref_owner , @referenced_column_name , @is_disabled
        , @is_not_for_replication , @delete_referential_action , @update_referential_action, @with_check


SELECT @col_list1 = '[' + @constraint_column_name + ']'
        , @col_list2 = '[' + @referenced_column_name + ']'


WHILE @@fetch_status >= 0
BEGIN


        fetch next from ms_fk_cursor2 into @fk_name1, @table_name1, @owner1, @constraint_column_name1
        , @referenced_object1 , @ref_owner1 , @referenced_column_name1 , @is_disabled1
        , @is_not_for_replication1 , @delete_referential_action1 , @update_referential_action1 , @with_check1


        IF @@fetch_status < 0 BREAK


        IF @fk_name <> @fk_name1
         BEGIN


               SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ' + @with_check + ' ADD CONSTRAINT [' +
                      @fk_name + '] FOREIGN KEY (' + @col_list1 + ') REFERENCES [' + @ref_owner + '].[' +
                      @referenced_object + '] (' + @col_list2 + ')'


               IF @update_referential_action = 1
                      SET @stmt = @stmt + ' ON UPDATE CASCADE'


               IF @is_not_for_replication = 1
                      SET @stmt = @stmt + ' NOT FOR REPLICATION'


               IF @delete_referential_action = 1
                      SET @stmt = @stmt + ' ON DELETE CASCADE'


               PRINT @stmt


               IF $( script_only) = 0
                      EXEC(@stmt )


               IF @@error <> 0
               BEGIN
                      PRINT '-- CREATE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('CREATE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      RETURN
               END


               IF @is_disabled = 1
               BEGIN
                      SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @fk_name + ']'
                      PRINT @stmt


                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                      BEGIN
                            PRINT '-- DISABLE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('DISABLE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                            RETURN
                      END


               END


               SELECT @fk_name = @fk_name1, @table_name = @table_name1, @owner = @owner1
                      , @constraint_column_name = @constraint_column_name1
                      , @referenced_object = @referenced_object1, @ref_owner = @ref_owner1
                      , @referenced_column_name = @referenced_column_name1, @is_disabled = @is_disabled1
                      , @is_not_for_replication = @is_not_for_replication1
                      , @delete_referential_action = @delete_referential_action1
                      , @update_referential_action = @update_referential_action1
                      , @with_check = @with_check1


               SELECT @col_list1 = '[' + @constraint_column_name + ']'
                      , @col_list2 = '[' + @referenced_column_name + ']'


         END
        ELSE
         BEGIN
                      SELECT @col_list1 = @col_list1 COLLATE $(desired_collation ) + ',['
                            + @constraint_column_name1 COLLATE $(desired_collation) + ']'
                            , @col_list2 = @col_list2 COLLATE $(desired_collation )
                            + ',[' + @referenced_column_name1 COLLATE $(desired_collation) + ']'
         END


END


SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] ' + @with_check + ' ADD CONSTRAINT [' +
        @fk_name + '] FOREIGN KEY (' + @col_list1 + ') REFERENCES [' + @ref_owner + '].[' +
        @referenced_object + '] (' + @col_list2 + ')'


IF @update_referential_action = 1
        SET @stmt = @stmt + ' ON UPDATE CASCADE'


IF @is_not_for_replication = 1
        SET @stmt = @stmt + ' NOT FOR REPLICATION'


IF @delete_referential_action = 1
        SET @stmt = @stmt + ' ON DELETE CASCADE'


PRINT @stmt


IF $( script_only) = 0
        EXEC(@stmt )


IF @@error <> 0
BEGIN
        PRINT '-- CREATE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('CREATE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


IF @is_disabled = 1
BEGIN
        SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] NOCHECK CONSTRAINT [' + @fk_name + ']'
        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- DISABLE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.'
               RAISERROR('DISABLE FK CONSTRAINT FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
               RETURN
        END


END


-- close cursor
CLOSE ms_fk_cursor2
DEALLOCATE ms_fk_cursor2


SKIP_CREATE_FK:


PRINT '-- CREATE FK CONSTRAINTS passed'


-- ****************************************************************
-- re-create statistics
-- ****************************************************************
PRINT '-- ***'
PRINT '-- RE-CREATE HAND-MADE STATISTICS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [user_stat_cols])
        GOTO SKIP_CREATE_USER_STAT


DECLARE ms_stat_cursor2 CURSOR FOR
SELECT owner , table_name , stat_name , column_name , no_recompute
FROM tempdb .dbo. [user_stat_cols]
ORDER BY owner, table_name, stat_name, col_order


open ms_stat_cursor2
fetch next from ms_stat_cursor2 into @owner, @table_name, @obj_name, @col_name, @flag


--PRINT @col_name
--PRINT '----fs ' + cast(@@fetch_status as varchar(20))


SET @col_list1 = '[' + @col_name + ']'


WHILE @@fetch_status >= 0
BEGIN


        fetch next from ms_stat_cursor2 into @owner1, @table_name1, @obj_name1, @col_name1, @flag1


        IF @@fetch_status < 0 BREAK


        --PRINT '----fs ' + cast(@@fetch_status as varchar(20))
        --PRINT '---' + @col_name1


        IF NOT (@owner = @owner1 AND @table_name = @table_name1 AND @obj_name = @obj_name1)
         BEGIN
               -- CREATE STATISTICS [Stat_certificates] ON [dbo].[CERTIFICATES]([Subject], [Issuer]) WITH NORECOMPUTE


               SET @stmt = 'CREATE STATISTICS [' + @obj_name + '] ON [' + @owner + '].[' + @table_name + '] (' +
                      @col_list1 + ')'


               IF @flag = 1
                      SET @stmt = @stmt + ' WITH NORECOMPUTE'


               PRINT @stmt


               IF $( script_only) = 0
                      EXEC(@stmt )


               IF @@error <> 0
               BEGIN
                      PRINT 'RE-CREATE STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('RE-CREATE STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      RETURN
               END


               SELECT @owner = @owner1, @table_name = @table_name1, @obj_name = @obj_name1, @col_name = @col_name1
                      , @flag = @flag1


               SET @col_list1 = '[' + @col_name COLLATE $( desired_collation) + ']'


         END
        ELSE
         BEGIN
                      SET @col_list1 = @col_list1 COLLATE $(desired_collation ) + ',[' + @col_name1 COLLATE $( desired_collation) + ']'
         END


END


-- close cursor
CLOSE ms_stat_cursor2
DEALLOCATE ms_stat_cursor2


SET @stmt = 'CREATE STATISTICS [' + @obj_name + '] ON [' + @owner + '].[' + @table_name + '] (' + @col_list1 + ')'


IF @flag = 1
        SET @stmt = @stmt + ' WITH NORECOMPUTE'


PRINT @stmt


IF $( script_only) = 0
        EXEC(@stmt )


IF @@error <> 0
BEGIN
        PRINT '-- RE-CREATE STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.'
        RAISERROR('RE-CREATE STATISTICS FAILED. SEE ERROR LOG FOR DETAILS.' , 16, 1)
        RETURN
END


SKIP_CREATE_USER_STAT:


PRINT '-- RE-CREATE HAND-MADE STATISTICS passed'




-- **************************************************
-- re-create triggers
-- **************************************************
PRINT '-- ***'
PRINT '-- RE-CREATE TRIGGERS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [triggers])
        GOTO SKIP_CREATE_TRIGGERS


DECLARE ms_trig_cursor2 CURSOR FOR
SELECT [trigger_name] , [table_name] , [owner] , is_disabled , definition , [ansi_nulls], [quoted_identifier]
FROM tempdb .dbo. [triggers]


-- open cursor
open ms_trig_cursor2
fetch next from ms_trig_cursor2 into @obj_name, @table_name, @owner, @flag, @obj_def, @ansi_nulls
        , @quoted_identifier


WHILE @@fetch_status >= 0
BEGIN


        SET @stmt = 'CREATE TRIGGER ' + @obj_name COLLATE $( desired_collation)
               + ' ON [' + @owner COLLATE $(desired_collation ) + '].[' + @table_name COLLATE $(desired_collation ) + ']'
        PRINT @stmt


        IF $( script_only) = 0
               BEGIN


                      IF @ansi_nulls = 0 AND @quoted_identifier = 0
                            EXEC dbo .usp_ANSINULLS_OFF_QUOTED_OFF @obj_def
                      ELSE
                            BEGIN
                                   IF @ansi_nulls = 0 AND @quoted_identifier = 1
                                          EXEC dbo .usp_ANSINULLS_OFF_QUOTED_ON @obj_def
                                   ELSE
                                          BEGIN
                                                 IF @ansi_nulls = 1 AND @quoted_identifier = 0
                                                        EXEC dbo.usp_ANSINULLS_ON_QUOTED_OFF @obj_def
                                                 ELSE -- ON ON
                                                        EXEC dbo.usp_ANSINULLS_ON_QUOTED_ON @obj_def
                                          END
                            END


               END
 
        ELSE
               BEGIN
                      PRINT @obj_def
               END


        IF @@error <> 0
               BEGIN
                      PRINT '-- ********************************************************************'
                      PRINT '-- CREATE TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.'
                      PRINT '-- ********************************************************************'
                      RAISERROR('CREATE TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      --RETURN
               END


        IF @flag = 1
               BEGIN
                      SET @stmt = 'ALTER TABLE [' + @owner + '].[' + @table_name + '] DISABLE TRIGGER [' + @obj_name + ']'
                      PRINT @stmt


                      IF $( script_only) = 0
                            EXEC(@stmt )


                      IF @@error <> 0
                            BEGIN
                                   PRINT '-- ********************************************************************'
                                   PRINT '-- DISABLE TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.'
                                   PRINT '-- ********************************************************************'
                                   RAISERROR('DISABLE TRIGGER FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                   --RETURN
                            END
               END


        fetch next from ms_trig_cursor2 into @obj_name, @table_name, @owner, @flag, @obj_def, @ansi_nulls
               , @quoted_identifier
END


-- close cursor
CLOSE ms_trig_cursor2
DEALLOCATE ms_trig_cursor2


SKIP_CREATE_TRIGGERS:


PRINT '-- ENABLE TRIGGERS passed'


-- **************************************************
-- set trigger order
-- **************************************************
PRINT '-- ***'
PRINT '-- SET TRIGGER ORDER'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [trigger_order])
        GOTO SKIP_SET_TRIGGER_ORDER


DECLARE ms_trig_cursor3 CURSOR FOR
SELECT [trigger_name] , [owner] , type_desc , [action]
FROM tempdb .dbo. [trigger_order]


-- open cursor
open ms_trig_cursor3
fetch next from ms_trig_cursor3 into @obj_name, @owner, @obj_name1, @obj_name2


WHILE @@fetch_status >= 0
BEGIN


        SET @new_name = @owner + '.' + @obj_name


        PRINT '-- SET TRIGGER ORDER ' + @obj_name + ' TO ' + @obj_name2 + ' FOR ' + @obj_name1
        EXEC sp_settriggerorder @triggername= @new_name, @order= @obj_name2, @stmttype = @obj_name1


        IF @@error <> 0
               BEGIN
                      PRINT '-- SET TRIGGER ORDER FAILED. SEE ERROR LOG FOR DETAILS.'
                      RAISERROR('SET TRIGGER ORDER FAILED. SEE ERROR LOG FOR DETAILS.', 16, 1)
                      RETURN
               END


        fetch next from ms_trig_cursor3 into @obj_name, @owner, @obj_name1, @obj_name2
END


-- close cursor
CLOSE ms_trig_cursor3
DEALLOCATE ms_trig_cursor3


SKIP_SET_TRIGGER_ORDER:


PRINT '-- SET TRIGGER ORDER passed'




-- **************************************************
-- recreate udf & udv
-- **************************************************
PRINT '-- ***'
PRINT '-- RE-CREATE UDF & UDV'


IF NOT EXISTS(SELECT TOP 1 [object_id] FROM tempdb. dbo.udf
                            UNION ALL
                            SELECT TOP 1 [object_id] FROM tempdb .dbo. udv )
        GOTO SKIP_CREATE_UDO


UPDATE tempdb .dbo. udf
        SET [dropped] = 0
UPDATE tempdb .dbo. udv
        SET [dropped] = 0


DECLARE @t1 int, @t2 int, @name sysname , @first varchar( max)


SET @num_cycles = 0


TRY_CREATE_AGAIN:


SELECT @has_error = 0


DECLARE ms_func_cursor2 CURSOR FOR
SELECT [name] = [udf_name], [owner], [definition], [object_id], [ansi_nulls], [quoted_identifier]
FROM tempdb .dbo. udf
WHERE [dropped] = 0
UNION ALL
SELECT [name] = [udv_name], [owner], [definition], [object_id], [ansi_nulls], [quoted_identifier]
FROM tempdb .dbo. udv
WHERE [dropped] = 0


open ms_func_cursor2
fetch next from ms_func_cursor2 into @obj_name, @owner, @obj_def, @object_id, @ansi_nulls, @quoted_identifier


WHILE @@fetch_status >= 0
BEGIN


        SELECT @t1 = 0 , @t2 = 0


        set @name = '[' + @obj_name + ']'
        --PRINT 'name = ' + @name


        -- look for function name with brackets
        set @t1 = charindex( @name, @obj_def )


        IF @t1 = 0
               BEGIN
                      set @name = @obj_name
                      --PRINT 'name = ' + @name
               END


        -- look for name without brackets
        SET @t1 = charindex( @name, @obj_def)


        IF @t1 = 0
               BEGIN
                      PRINT '-- Could not find object name!'
                      RAISERROR ('Could not find object name!' , 16, 1)
                      RETURN
               END


        select @t2 = charindex( '.', @obj_def COLLATE $(desired_collation ))




        IF @t2 > @t1
               BEGIN
                      SELECT @stmt = LEFT(@obj_def, @t1- 1) + @owner + '.' + SUBSTRING(@obj_def , @t1 , LEN (@obj_def))
                     
               END
        ELSE
               BEGIN
                      SELECT @first = LEFT(@obj_def, @t2- 1)


                      IF RIGHT( @first COLLATE $(desired_collation ), 1) = ']'
                            SET @flag = 1
                      ELSE
                            SET @flag = 0




                      WHILE RIGHT( @first COLLATE $(desired_collation ), 1) = ' ' OR RIGHT(@first COLLATE $( desired_collation), 1 ) = ']'
                            SET @first = LEFT(@first COLLATE $( desired_collation), len(@first COLLATE $ (desired_collation))- 1)
                     


                      WHILE NOT( RIGHT(@first COLLATE $ (desired_collation), 1) = ' ' OR RIGHT(@first COLLATE $( desired_collation), 1 ) = '[' )
                            SET @first = LEFT(@first COLLATE $( desired_collation), len(@first COLLATE $ (desired_collation))- 1)
                     
                      SELECT @stmt = @first + @owner + ( case @flag when 1 then ']' else '' end) + '.'
                            + SUBSTRING (@obj_def, @t1, LEN( @obj_def))


        END


        PRINT '-- ' + @owner + '.' + @obj_name
        PRINT '--'
        PRINT @stmt


        IF $( script_only) = 0


               BEGIN


                      IF @ansi_nulls = 0 AND @quoted_identifier = 0
                            EXEC dbo .usp_ANSINULLS_OFF_QUOTED_OFF @stmt
                      ELSE
                            BEGIN
                                   IF @ansi_nulls = 0 AND @quoted_identifier = 1
                                          EXEC dbo .usp_ANSINULLS_OFF_QUOTED_ON @stmt
                                   ELSE
                                          BEGIN
                                                 IF @ansi_nulls = 1 AND @quoted_identifier = 0
                                                        EXEC dbo.usp_ANSINULLS_ON_QUOTED_OFF @stmt
                                                 ELSE -- ON ON
                                                        EXEC dbo.usp_ANSINULLS_ON_QUOTED_ON @stmt
                                          END
                            END


               END


        IF @@error <> 0
               BEGIN
                      PRINT '-- FAILED to create function or view ' + @owner + '.' + @obj_name + '. Wait for next cycle.'
                      SET @has_error = 1
               END
        ELSE
               BEGIN
                      PRINT '--' + @owner + '.' + @obj_name + ' has been created.'


                      UPDATE tempdb .dbo. udf
                            SET [dropped] = 1
                      WHERE [object_id] = @object_id


                      IF @@error <> 0
                            BEGIN
                                   PRINT '--Failed to update temp table'
                                   RAISERROR('Failed to update temp table. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                   RETURN
                            END


                      UPDATE tempdb .dbo. udv
                            SET [dropped] = 1
                      WHERE [object_id] = @object_id


                      IF @@error <> 0
                            BEGIN
                                   PRINT '--Failed to update temp table'
                                   RAISERROR('Failed to update temp table. SEE ERROR LOG FOR DETAILS.', 16, 1)
                                   RETURN
                            END


               END


        PRINT '--'


        fetch next from ms_func_cursor2 into @obj_name, @owner, @obj_def, @object_id, @ansi_nulls, @quoted_identifier
END


CLOSE ms_func_cursor2
DEALLOCATE ms_func_cursor2


IF @has_error = 1
        BEGIN
               SET @num_cycles = @num_cycles + 1
               IF @num_cycles > @max_cycles
                      BEGIN
                            PRINT '-- maximum count of attempts for creating VIEW or FUNCTIUON is reached. SEE ERROR LOG FOR DETAILS.'
                            RAISERROR('maximum count of attempts for creating VIEW or FUNCTIUON is reached. SEE ERROR LOG FOR DETAILS.', 16, 1 )
                            RETURN
                      END
               ELSE
                      BEGIN
                            PRINT '-- trying to create VIEWS or FUNCTIONS again...(' + cast(@num_cycles as varchar(3 )) + ')'
                            GOTO TRY_CREATE_AGAIN
                      END
        END


SKIP_CREATE_UDO:


PRINT '-- CREATE UDF and UDV passes'


PRINT '-- ***'
PRINT '-- RE-CREATE PERMISSIONS'


IF NOT EXISTS(SELECT TOP 1 1 FROM tempdb .dbo. [permissions])
        GOTO SKIP_CREATE_PERMISSIONS


DECLARE ms_perm_cursor CURSOR FOR
SELECT [state] , [state_desc] , [permission_name] , [owner] , [obj_name] , [principal_name], [col_name]
FROM tempdb .dbo. [permissions]


-- open cursor
open ms_perm_cursor
fetch next from ms_perm_cursor into @mode, @obj_name, @obj_name2, @owner, @table_name, @obj_name3, @col_name


WHILE @@fetch_status >= 0
BEGIN
        IF @mode COLLATE $ (desired_collation) = 'W'
               SET @stmt = 'GRANT ' + @obj_name2 + ' ON [' + @owner + '].[' + @table_name + ']'
        ELSE
               SET @stmt = @obj_name + ' ' + @obj_name2 + ' ON [' + @owner + '].[' + @table_name + ']'


        IF LEN (LTRIM( @col_name)) > 0
               SET @stmt = @stmt + '([' + @col_name + '])'
              
        SET @stmt = @stmt + ' TO ' + @obj_name3
       
        IF @mode COLLATE $ (desired_collation) = 'W' -- WITH GRANT OPTION
               SET @stmt = @stmt + ' WITH GRANT OPTION'


        PRINT @stmt


        IF $( script_only) = 0
               EXEC(@stmt )


        IF @@error <> 0
        BEGIN
               PRINT '-- CREATE PERMISSION FAILED. SEE ERROR LOG FOR DETAILS.'
       
        END


        fetch next from ms_perm_cursor into @mode, @obj_name, @obj_name2, @owner, @table_name, @obj_name3, @col_name
END


CLOSE ms_perm_cursor
DEALLOCATE ms_perm_cursor


SKIP_CREATE_PERMISSIONS:


PRINT '-- RE-CREATE PERMISSIONS passed'


PRINT '-- *************************************'
PRINT '-- *************************************'
PRINT '-- *************************************'
PRINT '-- DONE. FINAL STEPS...'


PRINT '--'
PRINT '--Re-create object section finished at: ' + cast(getdate () as varchar( 40))
PRINT '--'




IF @RecoveryModel <> 3
        BEGIN
               PRINT '-- ******'
               PRINT '-- Waiting database $(destdb) to restore recovery mode...'
               SET @stmt = 'ALTER DATABASE $(destdb) SET RECOVERY ' + (case @RecoveryModel when 1 then 'FULL' else 'BULK_LOGGED' end)
               PRINT @stmt
               IF $( script_only) = 0             
                      EXEC (@stmt )
               SET @stmt = '-- Database $(destdb) RECOVERY MODE restored to ' + (case @RecoveryModel when 1 then 'FULL' else 'BULK_LOGGED' end)
               PRINT @stmt
        END


PRINT 'USE $(destdb)'
USE $( destdb)


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_ON_QUOTED_ON]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_ON]


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_OFF_QUOTED_ON]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_ON]


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_ON_QUOTED_OFF]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_ON_QUOTED_OFF]


IF  EXISTS (SELECT * FROM sys .objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ANSINULLS_OFF_QUOTED_OFF]' ) AND type in ( N'P', N'PC'))
DROP PROCEDURE [dbo]. [usp_ANSINULLS_OFF_QUOTED_OFF]


PRINT 'DBCC UPDATEUSAGE (0)'
IF $( script_only) = 0             
        DBCC UPDATEUSAGE (0 )




PRINT 'EXEC sp_updatestats @resample = ''RESAMPLE'''
IF $( script_only) = 0             
        exec sp_updatestats @resample = 'RESAMPLE'




PRINT 'ALTER DATABASE $(destdb) SET multi_user'
IF $( script_only) = 0             
        ALTER DATABASE $(destdb ) SET multi_user


PRINT '-- DATABASE IS IN MULTI_USER MODE.'
PRINT '-- JOB FINISHED AT ' + cast(getdate () as varchar( 40))
PRINT '-- '
PRINT 'Job passed success. Please pay attention on some errors raised during this process.'




PRINT '-- '
PRINT '-- '
PRINT '-- '
PRINT '-- '
PRINT '-- Start to check all views...'
PRINT CONVERT (varchar( 30), getdate(), 109 )


USE $( destdb)


DECLARE @view sysname


DECLARE cviews CURSOR FOR
SELECT DISTINCT schema_name( schema_id) + '.' + [name]
FROM sys .objects so
WHERE [type] = 'V'


OPEN cViews


FETCH cViews INTO @view


WHILE @@FETCH_STATUS = 0
BEGIN
       
        PRINT '-- Validation view name: ' + @view


        BEGIN TRY


               EXEC sp_refreshview @view


        END TRY


        BEGIN CATCH


               PRINT ERROR_MESSAGE ()


        END CATCH


        FETCH cViews INTO @view


END


CLOSE cViews
DEALLOCATE cViews


PRINT CONVERT (varchar( 30), getdate(), 109 )
PRINT '-- View validation passed...'
GO


use master
  

Leave a Reply

Your email address will not be published.