/*
	Helper functions and procs used during UPgrade adn needed for Hotfix as well

E:\Managed Workplace\Trunk\OMSolution.root\OMSolution\LPI.Database.OM\SQL\Upgrade\0090_ROUTINES_UsedByUpgrade.sql

*/


if object_id('udf_CHK_Name') is not null
	drop function dbo.udf_CHK_Name
go	
 
CREATE function [dbo].[udf_CHK_Name] 
( 
    @tablename sysname 
   ,@columnname sysname 
) 
returns sysname 
as 
/* 
alter table SnmpTrapVarBindingData_BulkLoad 
    add constraint SnmpTrapVarBindingData_BulkLoad_CreateDate_DF default(getDate()) for CreateDate 
 
    select dbo.udf_CHK_Name('SnmpTrapVarBindingData_BulkLoad','CreateDate') 
 
        exec dbo.DBM_Conv_drop_Column_DF 
								@tablename  = 'SnmpTrapVarBindingData_BulkLoad' 
							   ,@columnname = 'CreateDate' 
                               ,@debug = 1 
 
    select dbo.udf_CHK_Name('SnmpTrapVarBindingData_BulkLoad','CreateDate') 
*/ 
begin 
    return (    
            select top(1)  
                     C.name 
              from   sys.check_constraints        C with(nolock) 
              join  sys.columns   IC with(nolock) 
                                                                    on C.parent_column_id = IC.column_id 
                                                                   and C.parent_object_id = IC.object_id        
             where C.parent_object_id = object_id(@tablename) 
               and IC.name             = @columnname 
           ) 
 
end -- udf_CHK_Name 
GO


if object_id('DBM_Conv_drop_Column_CHK') is not null
	drop procedure dbo.DBM_Conv_drop_Column_CHK
go

 
create procedure [dbo].[DBM_Conv_drop_Column_CHK] 
( 
    @tablename   sysname 
   ,@columnname  sysname 
   ,@debug       int     = -1 
) 
as 
/* 
    exec [dbo].[DBM_Conv_drop_Column_CHK] 
 
    @tablename    = '' 
   ,@columnname   = '' 
   ,@debug        = 1 
    
*/ 
begin 
    declare @constrname sysname  
           ,@sql        nvarchar(1000)      
     
    set @constrname = [dbo].[udf_CHK_Name] (@tablename, @columnname) 
   
    while (len(@constrname) > 0) begin 
     
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname  
        if (@debug = 1) begin  
            print '[DBM_Conv_drop_Column_CHK][@tablename='  + @tablename  
                                        + '][@columnname= ' + @columnname 
                                        + '][@constrname= ' + isnull(@constrname,'null') 
                                        + ']' 
           print '@sql:'       
           print @sql                                        
                                              
        end 
             
        exec (@sql) 
         
        -- more then one check constraint? 
        set @constrname = [dbo].[udf_CHK_Name] (@tablename, @columnname) 
         
    end 
     
end -- DBM_Conv_drop_Column_DF 
GO

if object_id('DBM_Conv_drop_NCIX_AllForColumn') is not null
	drop procedure dbo.DBM_Conv_drop_NCIX_AllForColumn
go


create procedure [dbo].[DBM_Conv_drop_NCIX_AllForColumn] 
(
    @tablename     sysname
   ,@columnname    sysname
   ,@debug         int = -1
)
as
/*
    exec sp_help SnmpTrap_Old


    begin transaction
    
       exec  [dbo].[DBM_Conv_drop_NCIX_AllForColumn] 
                                                    @tablename     = 'SnmpTrap_Old'
                                                   ,@columnname    = 'DeviceID'
                                                   ,@debug         = 1       
    
    rollback
*/
begin

    declare @sql         nvarchar(1000)
           ,@indexname   sysname
    
    ---------------------
    if @debug = 1 begin 
    
          select 
                  o.name      as tablename
                 ,i.name      as indexname
                 ,i.index_id   
                 ,c.name      as columnname 
                 ,o.object_id
                 ,ic.key_ordinal 
            from   
                 sys.objects       o 
            join 
                 sys.indexes       i 
                                        on o.object_id = i.object_id 
            join sys.index_columns ic 
                                        on i.object_id = ic.object_id 
                                       and i.index_id = ic.index_id 
            join sys.columns       c 
                                        on ic.object_id = c.object_id 
                                       and ic.column_id = c.column_id 
            where  o.type       = 'U'  
              and i.index_id    > 1
              and o.name        = @tablename
              and c.name        = @columnname
    end
    ---------------------
       
    declare cr cursor local fast_forward
    for
        with IndexColumns as
        (
        
          select 
                  o.name      as tablename
                 ,i.name      as indexname
                 ,i.index_id   
                 ,c.name      as columnname 
                 ,o.object_id
                 ,ic.key_ordinal 
            from   
                 sys.objects       o 
            join 
                 sys.indexes       i 
                                        on o.object_id = i.object_id 
            join sys.index_columns ic 
                                        on i.object_id = ic.object_id 
                                       and i.index_id = ic.index_id 
            join sys.columns       c 
                                        on ic.object_id = c.object_id 
                                       and ic.column_id = c.column_id 
            where  o.type       = 'U'  
              and i.index_id    > 1
              and o.name        = @tablename
              and c.name        = @columnname
                    
          )
        SELECT distinct
               indexname
          from 
               IndexColumns     
        
    declare @ii int = 0    
    open cr
    while (1 = 1) begin
        
        
        
        fetch next from cr into @indexname
        
        if (@@Fetch_Status <> 0) break
        
        set @ii = @ii + 1
    
        set @sql = 'drop index ' + @indexname + ' on ' + @tablename
        
        if @debug = 1 print @sql
        
        exec(@sql)
    
    end -- while
    close cr
    deallocate cr        

    -- feedback
     -- if @debug = 1
     select 
             @tablename  as [@tablename]
            ,@columnname as [@columnname]
            ,@ii         as [Number of indexes found]
    

end -- DBM_Conv_drop_NCIX_AllForColumn
GO


if object_id('DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN') is not null
	drop procedure dbo.DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN
go

 
CREATE PROCEDURE [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN]
(
    @tablename   sysname
   ,@columnname  sysname
   ,@debug       INT     = -1
)
AS 
/*
/*
    exec sp_help SnmpTrap_Old


    begin transaction
    
       exec  [dbo].[DBM_Conv_drop_Column_DF_FK_CHECK_NCIX_COLUMN] 
                                                    @tablename     = 'SnmpTrap_Old'
                                                   ,@columnname    = 'DeviceID'
                                                   ,@debug         = 1       
    
    rollback
*/
    
*/
BEGIN
    
    DECLARE @sql NVARCHAR(4000)
    
    EXEC dbo.DBM_Conv_drop_Column_DF 
                                         @tablename    = @tablename
                                        ,@columnname   = @columnname
                                        ,@debug        = @debug
                                      
    EXEC dbo.DBM_Conv_drop_Column_FK  
                                         @tablename       = @tablename
                                        ,@firstfkcolumn   = @columnname
                                        ,@reftablename    = ''
           
   EXEC [dbo].[DBM_Conv_drop_Column_CHK]
                                         @tablename    = @tablename
                                        ,@columnname   = @columnname
                                        ,@debug        = @debug
    

   EXEC  [dbo].[DBM_Conv_drop_NCIX_AllForColumn] 
                                                     @tablename    = @tablename
                                                    ,@columnname   = @columnname
                                                    ,@debug        = @debug

    -- drop column
    SET @sql = 
    'if exists
        (
	        select * from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME  = ''' + @tablename  + '''
               and COLUMN_NAME = ''' + @columnname + '''
        )
	        ALTER TABLE [dbo].' + @tablename + ' drop column ' + @columnname
	        
   IF (@debug = 1) BEGIN 
        PRINT @sql                                    
   END    
   EXEC (@sql)


END -- [DBM_Conv_drop_Column_DF_FK_COLUMN]

GO


if object_id('fn_FormatTime_days_HHMMSS') is not null
	drop function dbo.fn_FormatTime_days_HHMMSS
go


create function [dbo].[fn_FormatTime_days_HHMMSS] 
( 
    @sec bigint 
) 
returns varchar(50) 
as 
/* 
AI: 
this function has to be created before view 'vw_DB_BulkLoadLog 
*/ 
begin 
    declare @frmt       varchar(50) 
           ,@days       int 
           ,@secInDay   int 
     
    set @secInDay = 86400 
     
    if (@sec is null) set @sec = 0         
     
    set @days = @sec / @secInDay 
     
    if (@days > 0)  begin 
     
        set @sec = @sec - @days * @secInDay 
         
        set @frmt = convert(varchar(10),@days) + ' days' 
         
    end 
     
    set @frmt = ltrim(isnull(@frmt,'') + ' ' + convert(varchar(8),dateadd(ss,@sec,0),108)) 
     
    return @frmt 
 
end -- fn_FormatTime_days_HHMMSS 
GO


if object_id('udf_Conv_DoesColumnExist_wSchema') is not null
	drop function dbo.udf_Conv_DoesColumnExist_wSchema
go
 
create function [dbo].[udf_Conv_DoesColumnExist_wSchema] 
( 
    @tablename      sysname 
   ,@colname        sysname 
   ,@schemaname     sysname 
) 
returns bit 
as 
/* 
    select dbo.udf_Conv_DoesColumnExist_wSchema('WindowsEvent','EventID','[dbo]') 
    select dbo.udf_Conv_DoesColumnExist_wSchema('HyperV_VirtualMachine','DeviceGuid','[com.avg.microsoft.hyperv]') 
     
    select dbo.udf_Conv_DoesColumnExist_wSchema('HyperV_VirtualMachine','DeviceGuid','com.avg.microsoft.hyperv') 
 
    select dbo.udf_Conv_DoesColumnExist_wSchema('WindowsEvent','EventID',null) 
 
*/ 
begin 
 
    return  
    (Case 
        when exists 
            ( 
                select * from INFORMATION_SCHEMA.COLUMNS C 
                 where TABLE_NAME     = replace(replace(@tablename  ,'[',''),']','') 
                   and COLUMN_NAME    = replace(replace(@colname    ,'[',''),']','')		                        
                   and C.TABLE_SCHEMA = isnull(replace(replace(@schemaname ,'[',''),']',''),'dbo')		                        
            ) then 1 
         else 0 
    end) 
end -- udf_Conv_DoesColumnExist 
GO


if object_id('udf_Conv_DoesTableExist') is not null
	drop function dbo.udf_Conv_DoesTableExist
go
 
create function [dbo].[udf_Conv_DoesTableExist] 
( 
    @tablename   sysname 
) 
returns bit 
as 
/* 
    select dbo.[udf_Conv_DoesTableExist]('SyslogData') 
*/ 
begin 
 
    return (Case 
                when exists 
	                     ( 
                            SELECT * 
                            FROM   
                                 sys.tables  AS t WITH (NOLOCK)    
                            where  
                                  t.[name]   = @tablename 
                          ) then 1 
	             else            0 
	        end) 
end -- udf_Conv_DoesTableExist 
GO

if object_id('udf_Conv_DoesViewExist') is not null
	drop function dbo.udf_Conv_DoesViewExist
go
 
 
CREATE function [dbo].[udf_Conv_DoesViewExist] 
( 
    @name   sysname 
) 
returns bit 
as 
/* 
    select dbo.[udf_Conv_DoesViewExist]('SyslogData') 
*/ 
begin 
 
    return (Case 
                when exists 
	                     ( 
                            SELECT * 
                            FROM   
                                 sys.views  AS t WITH (NOLOCK)    
                            where  
                                  t.[name]   = @name 
                          ) then 1 
	             else            0 
	        end) 
end -- udf_Conv_DoesViewExist 
GO


if object_id('udf_Conv_IsUpgrade') is not null
	drop function dbo.udf_Conv_IsUpgrade
go

create function [dbo].[udf_Conv_IsUpgrade]() 
returns bit as 
begin 
    return case 
            when exists ( select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'IsUpgrade')  then cast (1 as bit) 
            else cast (0 as bit) 
           end 
end -- dbo.udf_Conv_IsUpgrade 
GO

if object_id('udf_Conv_WeekDaysTable') is not null
	drop function dbo.udf_Conv_WeekDaysTable
GO


create function [dbo].[udf_Conv_WeekDaysTable]() 
returns table  
/* 
    select * from dbo.udf_Conv_WeekDaysTable() 
*/ 
as 
    return  
	 ( 
					select 1    as DayNo ,'Monday' as [DayOfWeek] --?? 6.1 was "Monday is FirstDayOfWeek" 
        union all 	select 2,  'Tuesday' 
        union all 	select 3,  'Wednesday' 
        union all 	select 4,  'Thursday' 
        union all 	select 5,  'Friday' 
        union all 	select 6,  'Saturday' 
        union all 	select 7,  'Sunday'  
	 ) 
GO


if object_id('SC_DB_ERROR_HANDLER') is not null
	drop procedure dbo.SC_DB_ERROR_HANDLER
go

 
CREATE  PROCEDURE [dbo].[SC_DB_ERROR_HANDLER] 
( 
    @ErrorNumber         int             output 
   ,@ErrorMessage        nvarchar(3000)  output 
   ,@ErrorInfo           nvarchar(1000)  output 
   ,@debug               int             = 0 
)  
as 
begin 
 
    set @ErrorNumber  = ERROR_NUMBER(); 
     
    set @ErrorMessage = ERROR_MESSAGE();  
     
    set @ErrorInfo =      
                      '[PROCEDURE=' +                     ERROR_PROCEDURE()     + ']'     
                    + '[LINE='      + convert(varchar(10),ERROR_LINE     ())    + ']'     
                    + '[SEVERITY='  + convert(varchar(10),ERROR_SEVERITY ())    + ']'     
                    + '[STATE='     + convert(varchar(10),ERROR_STATE    ())    + ']';  
     
    set @ErrorInfo = ISNULL(@ErrorInfo,'') 
                     
    if (@debug = 1) begin 
        print convert(varchar(30),getDate(),121) + ' -- [SC_DB_ERROR_HANDLER]' 
        print @ErrorMessage 
        print @ErrorNumber 
        print @ErrorInfo 
        select  
               @ErrorMessage      as ERROR_MESSAGE 
              ,@ErrorNumber       as ERROR_NUMBER 
              ,@ErrorInfo         as ERROR_INFO 
    end 
     
end -- SC_DB_ERROR_HANDLER 
GO


if object_id('SC_DB_Raise_Error') is not null
	drop procedure dbo.SC_DB_Raise_Error
go

 



IF OBJECT_ID('[dbo].[fn_GetIPAddressNumeric]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[fn_GetIPAddressNumeric]
    PRINT '<<< DROPPED FUNCTION [dbo].[fn_GetIPAddressNumeric] >>>'
END
go



CREATE FUNCTION [dbo].[fn_GetIPAddressNumeric] 
(
	-- Add the parameters for the function here
	@address nvarchar(15)
)
RETURNS bigint
AS
BEGIN
	DECLARE @index int
	SET @index = 1
	Declare @char char
	SET @char = substring(@address,@index, 1)
	while(@char <> '.')
	BEGIN
		SET @index = @index + 1
		SET @char = substring(@address,@index, 1)
	END
	DECLARE @value1Index tinyint
	DECLARE @value1 tinyint
	SET @value1Index = @index
	SET @value1 = convert(tinyint, substring(@address, 1, @value1Index-1))

	SET @char = ''
	while(@char <> '.')
	BEGIN
		SET @index = @index + 1
		SET @char = substring(@address,@index, 1)
	END
	DECLARE @value2Index tinyint
	DECLARE @value2 tinyint
	SET @value2Index = @index
	SET @value2 = convert(tinyint, substring(@address, @value1Index+1, @value2Index - @value1Index - 1))

	SET @char = ''
	while(@char <> '.')
	BEGIN
		SET @index = @index + 1
		SET @char = substring(@address,@index, 1)
	END
	DECLARE @value3Index tinyint
	DECLARE @value3 tinyint
	SET @value3Index = @index
	SET @value3 = convert(tinyint, substring(@address, @value2Index + 1, @value3Index - @value2Index - 1))

	SET @char = '.'
	while(@char <> '')
	BEGIN
		SET @index = @index + 1
		SET @char = substring(@address,@index, 1)
	END
	DECLARE @value4Index tinyint
	DECLARE @value4 tinyint
	SET @value4Index = @index
	SET @value4 = convert(tinyint, substring(@address, @value3Index+1, @value4Index-@value3Index-1))

	return     cast(@value1 as bigint)*256*256*256 + (@value2*256*256) + (@value3*256) + (@value4)

END
go

IF OBJECT_ID('[dbo].[udf_DBM_PKName_FromTablename]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_DBM_PKName_FromTablename]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_DBM_PKName_FromTablename] >>>'
END
go


create function [dbo].[udf_DBM_PKName_FromTablename]
(
    @Tablename sysname
)
returns sysname
as
begin

     declare @pkname sysname
            ,@sql    nvarchar(1000)
    
     set @pkname = (
                    select constraint_name
                      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                     where TABLE_NAME = @Tablename
                       and CONSTRAINT_TYPE = 'PRIMARY KEY'
                   )

    return isnull(@pkname,N'')

end -- udf_DBM_PKName_FromTableName
go

IF OBJECT_ID('[dbo].[udf_DF_Name]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_DF_Name]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_DF_Name] >>>'
END
go


CREATE function [dbo].[udf_DF_Name]
(
    @tablename sysname
   ,@columnname sysname
)
returns sysname
as
/*
alter table SnmpTrapVarBindingData_BulkLoad
    add constraint SnmpTrapVarBindingData_BulkLoad_CreateDate_DF default(getDate()) for CreateDate

    select dbo.udf_DF_Name('SnmpTrapVarBindingData_BulkLoad','CreateDate')

        exec dbo.DBM_Conv_drop_Column_DF
								@tablename  = 'SnmpTrapVarBindingData_BulkLoad'
							   ,@columnname = 'CreateDate'
                               ,@debug = 1

    select dbo.udf_DF_Name('SnmpTrapVarBindingData_BulkLoad','CreateDate')
*/
begin
    return (   
            select DF.name
                 -- ,DF.*
                 -- ,IC.* 
              from   sys.default_constraints        DF with(nolock)
              join     sys.columns   IC with(nolock)
                                                                    on DF.parent_column_id = IC.column_id
                                                                   and DF.parent_object_id = IC.object_id       
             where DF.parent_object_id = object_id(@tablename)
               and IC.name             = @columnname
           )

end -- udf_DF_Name
go

IF OBJECT_ID('[dbo].[udf_GetConfigurationValue]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_GetConfigurationValue]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_GetConfigurationValue] >>>'
END
go


create function [dbo].[udf_GetConfigurationValue]
(
    @ConfigurationKey nvarchar(255)
)
returns nvarchar(max)
as

begin

    declare @ConfigurationValue nvarchar(max)

    SELECT @ConfigurationValue = Value
     FROM  dbo.MWConfig
    WHERE  [Key] = @ConfigurationKey

    return @ConfigurationValue

end -- udf_GetConfigurationValue
go

IF OBJECT_ID('[dbo].[udf_OneColumnFK_Name]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_OneColumnFK_Name]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_OneColumnFK_Name] >>>'
END
go


create function [dbo].[udf_OneColumnFK_Name]
(
    @tablename          sysname
   ,@firstfkcolumn     sysname
   ,@reftablename       sysname
  -- ,@firstrefcolumn     sysname
)
returns sysname
as
/*

alter table PatchDeviceLink add constraint 
            PatchDeviceLink_DeviceID_Device_DeviceID_FK foreign key (DeviceID) references dbo.Device(DeviceID)

    select dbo.udf_OneColumnFK_Name('PatchDeviceLink','DeviceID','')
*/
begin
/*
declare 
    @tablename          sysname
   ,@firstfkcolumn    sysname
   ,@reftablename       sysname

    set @tablename       = 'PatchDeviceLink'
    set @firstfkcolumn  = 'DeviceID'
    set @reftablename    = 'Device'
    ;
 */
    return (   
                SELECT
                        FK.CONSTRAINT_NAME 
--                        ,FK_Table = FK.TABLE_NAME
--                        ,FK_Column = CU.COLUMN_NAME
                FROM       
                          INFORMATION_SCHEMA.TABLE_CONSTRAINTS  FK
                INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE  CU 
                                                                    ON CU.CONSTRAINT_NAME        = FK.CONSTRAINT_NAME
                where 
                     FK.TABLE_NAME        = @tablename      
                 and FK.CONSTRAINT_TYPE   = 'FOREIGN KEY'
                 and CU.COLUMN_NAME       = @firstfkcolumn
                 and CU.ORDINAL_POSITION  = 1
--                  and PK.TABLE_NAME     = @reftablename
           )
end -- udf_OneColumnFK_Name
go

IF OBJECT_ID('[dbo].[udf_PK_Name]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_PK_Name]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_PK_Name] >>>'
END
go


create function [dbo].[udf_PK_Name]
(
    @tablename sysname
)
returns sysname
as
/*
    select dbo.udf_PK_Name('CounterData')
*/
begin
    return (   
            select constraint_name
              from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
             where TABLE_NAME = @tablename
               and CONSTRAINT_TYPE = 'PRIMARY KEY'
           )

end -- udf_PK_Name
go

IF OBJECT_ID('[dbo].[udf_TimestampSuffixLocalTime]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_TimestampSuffixLocalTime]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_TimestampSuffixLocalTime] >>>'
END
go


CREATE function [dbo].[udf_TimestampSuffixLocalTime]()
returns nvarchar(20)
as
/*
    select  convert(varchar(23),getdate(),121)
    
    select dbo.udf_TimestampSuffixLocalTime()
*/
begin
    return  '_' + replace(
                            + replace(
                                       replace(
                                                replace(
                                                        convert(varchar(23),getdate(),121)
                                                        ,' ','_')
                                                ,'-','')
                                       ,':','')
                               ,'.','_')
                                       
end -- udf_TimestampCharForRename
go

IF OBJECT_ID('[dbo].[udf_VarDomainName_fromDbName]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_VarDomainName_fromDbName]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_VarDomainName_fromDbName] >>>'
END
go


create function [dbo].[udf_VarDomainName_fromDbName]()
returns sysname
as
/*
    select dbo.udf_VarDomainName_fromDbName()
*/
begin
    return substring(db_name(),len('SCData_') + 1, len(db_name()) - len('SCData_'))
end -- udf_VarDomainName_fromDbName
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_DF]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_DF]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_DF] >>>'
END
go



CREATE procedure [dbo].[DBM_Conv_drop_Column_DF]
(
    @tablename   sysname
   ,@columnname  sysname
   ,@debug       int     = -1
)
as 
begin
    declare @constrname sysname 
           ,@sql        nvarchar(1000)     
    
    set @constrname = [dbo].[udf_DF_Name] (@tablename, @columnname)
  
    if (@debug = 1) begin 
        print '[DBM_Conv_drop_Column_DF][@tablename='   + @tablename 
                                    + '][@columnname= ' + @columnname
                                    + '][@constrname= ' + isnull(@constrname,'null')
                                    + ']'
    end
        
    set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
    exec (@sql)
    
end -- DBM_Conv_drop_Column_DF
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_Column_FK]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_Column_FK]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_Column_FK] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_drop_Column_FK]
(
    @tablename          sysname
   ,@firstfkcolumn     sysname
   ,@reftablename       sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)     
    
    set @constrname = dbo.udf_OneColumnFK_Name
                                                (
                                                    @tablename          
                                                   ,@firstfkcolumn     
                                                   ,@reftablename      
                                                )
    set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
    exec (@sql)
    
end -- DBM_Conv_drop_Column_FK
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_DF_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_DF_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_DF_AllForTable] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_drop_DF_AllForTable]
(
    @tablename sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)     
       

    declare cr cursor local fast_forward
    for
    select name 
      from   sys.default_constraints  with(nolock)
     where parent_object_id = object_id(@tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_DF_AllForTable
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_FK_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_FK_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_FK_AllForTable] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_drop_FK_AllForTable]
(
    @tablename sysname
)
as 
begin
    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)  
   
    declare cr cursor local fast_forward

    for
    select name 
      from sys.foreign_keys  with(nolock)
     where parent_object_id = object_id(@tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_FK_AllForTable
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_IX_AllForTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_IX_AllForTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_IX_AllForTable] >>>'
END
go


create procedure [dbo].[DBM_Conv_drop_IX_AllForTable]
(
    @tablename     sysname
   ,@excludeUnique bit = 0
)
as 
/*
-- was SC_NonclusterdIndex_dropAll
*/
begin
    declare @sql        nvarchar(1000)
           ,@IndexName  sysname
       

    declare cr cursor local fast_forward
    for
        SELECT 
              i.[name] as IndexName
        FROM
                sys.indexes AS i WITH (NOLOCK)
        JOIN 
                sys.tables  AS t WITH (NOLOCK)
        ON 
                i.[object_id] = t.[object_id]
        where 
            t.[name]   = @tablename
        and i.index_id > 1 -- nonclustered
        and is_unique = case
                             when @excludeUnique = 1 then 0
                             else is_unique
                        end                             
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @IndexName
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'drop index ' + @IndexName + ' on ' + @tablename
        
        exec(@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- DBM_Conv_drop_IX_AllForTable
go

IF OBJECT_ID('[dbo].[DBM_Conv_drop_PK]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_PK]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_PK] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_drop_PK]
(
    @tablename sysname
)
as
begin

    declare 
           @constrname       sysname
           ,@sql             nvarchar(1000)  

     set @constrname = dbo.udf_PK_Name(@tablename)

        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        exec (@sql)

end -- DBM_Conv_drop_PK
go

IF OBJECT_ID('[dbo].[SC_BLSB_Dialog_cleanupAll]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[SC_BLSB_Dialog_cleanupAll]
    PRINT '<<< DROPPED PROCEDURE [dbo].[SC_BLSB_Dialog_cleanupAll] >>>'
END
go


create procedure [dbo].[SC_BLSB_Dialog_cleanupAll]
as
begin


    DECLARE @handle AS UNIQUEIDENTIFIER;
    DECLARE CONV CURSOR FOR 
      SELECT conversation_handle 
      FROM sys.conversation_endpoints      
      --WHERE far_service = 'WindowsEvent_service';
     order by is_initiator asc -- clean up recepient first
      
    OPEN CONV;
    FETCH NEXT FROM CONV INTO @handle;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      END Conversation @handle WITH CLEANUP;
      FETCH NEXT FROM CONV INTO @handle;
    END
    CLOSE CONV;
    DEALLOCATE CONV;
    
    
    exec('
     begin try
        truncate table dbo.DBM_ServiceBrokerDialog
     end try 
     begin catch
     end catch')        
    
end -- SC_BLSB_Dialog_cleanupAll
go



IF OBJECT_ID('[dbo].[DBM_Conv_rename_ObjectRcsv]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_rename_ObjectRcsv]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_rename_ObjectRcsv] >>>'
END
go


CREATE procedure [dbo].[DBM_Conv_rename_ObjectRcsv]
(
	@objname            sysname
   ,@newname            sysname
   ,@objtype            sysname = NULL -- null for table
   ,@debug              BIT  = 0
)
as
begin
    if object_id(@objname) is null  RETURN
    ---------------------------------------
	if object_id(@newname) is NOT null begin -- the new name is already in use --> rename it first

        declare @TempName sysname
        
        waitfor delay '00:00:01';

		set @TempName = @newname + dbo.udf_TimestampSuffixLocalTime()

        IF @debug = 1 BEGIN
            PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname = ' + ISNULL(@newname,'null')
                                                    +  ',@newname = ' + ISNULL(@TempName,'null') 
                                                    +  ',@objtype = ' + ISNULL(@objtype,'null/TABLE') 
                                                    +  '  
                @@NESTLEVEL =  ' + CAST (@@NESTLEVEL AS NVARCHAR(10))
        END


        EXEC dbo.DBM_Conv_rename_ObjectRcsv 
                                                @objname = @newname
                                               ,@newname = @TempName
                                               ,@objtype = @objtype
                                               ,@debug   = @debug

        
	end
 
        IF @debug = 1 BEGIN
        PRINT 'exec dbo.sp_rename    @objname = ' + ISNULL(@objname,'null')
                                +  ',@newname = ' + ISNULL(@newname,'null') 
                                +  ',@objtype = ' + ISNULL(@objtype,'null/TABLE') 
                                +  '  
            @@NESTLEVEL =  ' + CAST (@@NESTLEVEL AS NVARCHAR(10))
    END
   ---------------------
   
    IF @objtype IS NOT NULL begin

         exec sp_rename 
                          @objname = @objname
                         ,@newname = @newname
                         ,@objtype = @objtype
    END
    ELSE BEGIN
         exec sp_rename 
                          @objname = @objname
                         ,@newname = @newname
                       --  ,@objtype = @objtype
    
    END


end -- DBM_Conv_rename_ObjectRcsv
go

IF OBJECT_ID('[dbo].[DBM_Conv_rename_Table]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_rename_Table]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_rename_Table] >>>'
END
go

CREATE procedure [dbo].[DBM_Conv_rename_Table]
(
	@tablename          sysname
   ,@newname            sysname      = null
   ,@suffix             nvarchar(20) = '_2009R2'  -- never '', use NULL
   ,@RenamePK           bit = 1
   ,@DropDF             bit = 1
   ,@DropFK             bit = 1
   ,@DropPK             bit = 0
   ,@DropNCIndex        bit = 0
   ,@OnlyIfTable        bit = 1
   ,@NotIfNewNameExists bit = 0
   ,@debug              BIT  = 0
)
AS
/*

                select * from INFORMATION_SCHEMA.TABLES
              where TABLE_NAME = 'MwusDevicePatchStatus'

*/

begin
    if (@OnlyIfTable = 1) begin
      -- check it is not a view:
        if not exists
        (
            select * from sys.tables t
              where t.name = @tablename
              --  and TABLE_TYPE = 'BASE TABLE'
        ) 
        BEGIN
            PRINT @tablename + ' not found. Exit [DBM_Conv_rename_Table] '
            RETURN
        end
    end        
    ----------------------------------

    declare @curname    sysname
           ,@nextname   sysname
           ,@sql        nvarchar(1000)

    ------------------------------
    if (@newname is null) set @newname = @tablename + @suffix
    ------------------------------

     if (@NotIfNewNameExists = 1) begin
      -- check if @newname table/view exists
        if   exists
        (
            select * from sys.tables t
              where t.name = @newname
        ) 
        return
    end        
    ----------------------------------
    -------------------------------------

    if (@DropNCIndex = 1)   exec dbo.DBM_Conv_drop_IX_AllForTable   @tablename = @tablename
    if (@DropDF      = 1)   exec dbo.DBM_Conv_drop_DF_AllForTable   @tablename = @tablename
    if (@DropFK      = 1)   exec dbo.DBM_Conv_drop_FK_AllForTable   @tablename = @tablename

    if (@RenamePK = 1 )   begin

        set @curname = dbo.udf_PK_Name(@tablename)
        if (@curname is not null) begin
            set @nextname = @curname + isnull(@suffix,dbo.udf_TimestampSuffixLocalTime())
            
            IF @debug = 1 BEGIN
                PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname = ' + ISNULL(@curname,'null')
                                                       +  ',@newname = ' + ISNULL(@nextname,'null')
                                                       +  ',@objtype = OBJECT'  
            END
            
            exec dbo.DBM_Conv_rename_ObjectRcsv 
                                                 @objname = @curname
                                                ,@newname = @nextname
                                                ,@objtype = 'OBJECT'  -- for constraint
                                                
        end
    end
    else if (@DropPK = 1 )   begin
        exec dbo.DBM_Conv_drop_PK @tablename = @tablename
    end

    -- FINALLY:
    IF @debug = 1 BEGIN
        PRINT 'exec dbo.DBM_Conv_rename_ObjectRcsv  @objname='    + ISNULL(@tablename,'null')
                                                + ' ,@newname = ' + ISNULL( @newname,'null')
                                                +  ',@objtype = null/TABLE' 
        
    END
    exec dbo.DBM_Conv_rename_ObjectRcsv 
                                         @objname = @tablename
                                        ,@newname = @newname
                                        ,@objtype = NULL -- for table
                                        ,@debug   = @debug


end -- DBM_Conv_rename_Table

GO


IF OBJECT_ID('[dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]
    PRINT '<<< DROPPED PROCEDURE [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable] >>>'
END
go

CREATE procedure [dbo].[DBM_Conv_drop_FK_AllTo_ReferencedTable]
(
    @referenced_tablename sysname
   ,@debug                int = 0
)
as 
/*
	exec dbo.DBM_Conv_drop_FK_AllTo_ReferencedTable 
					@referenced_tablename = 'AlertLog'
				   ,@debug = 1
*/
begin
    declare 
            @constrname      sysname
           ,@sql             nvarchar(1000) 
           ,@tablename       sysname 
   
    declare cr cursor local fast_forward

    for
    select 
         name
		,object_name(parent_object_id) as TableName
	--	,object_name(referenced_object_id) as ParentTableName
		 from sys.foreign_keys 
		 where type = 'F'
		 --and object_id = object_id('AlertLog')
		 and referenced_object_id = object_id(@referenced_tablename)
    
    open cr
    
    while (1 = 1) begin
    
        fetch next from cr into @constrname,@tablename 
        
        if (@@Fetch_Status <> 0) break
    
        set @sql = 'alter table ' + @tablename + ' drop constraint ' + @constrname 
        if (@debug = 1) 
			print @sql
        else
			exec (@sql)
    
    end -- while
    
    close cr
    deallocate cr
    
    
end -- [DBM_Conv_drop_FK_AllTo_ReferencedTable
go


if object_id('udf_TicksToDatetime') is not null
    drop function dbo.udf_TicksToDatetime
go    
CREATE function [dbo].[udf_TicksToDatetime]( @ticks bigint, @UseIfOutOfRange datetime)
returns datetime
as
/*
    select *, dbo.udf_TicksToDatetime( cast(DateModified as bigint), getUtcdate()) from Script61
    select dbo.udf_TicksToDatetime( cast(3155378975999999999 as bigint) , getUtcdate())-- DateTime.MaxValue.Ticks
    select dbo.udf_TicksToDatetime( cast(7155378975999999999 as bigint), null)-- > DateTime.MaxValue.Ticks
    select dbo.udf_TicksToDatetime( cast(7155378975999999999 as bigint), getUtcdate())-- > DateTime.MaxValue.Ticks
    select dbo.udf_TicksToDatetime( cast(632796932400000000 as bigint), getUtcdate())-- 
    
*/
begin
  
    declare @ticks_Jan_01_2000          bigint 
    declare @ticks_MaxDateTime          bigint 
    declare @ticks_PastJan_01_2000      bigint 
    declare @seconds_PastJan_01_2000    bigint 
    declare @dt                         datetime
    
     declare @days           int
            ,@day_seconds    int
     set @dt = convert(datetime,'2000-01-01',121)
     set @day_seconds = 60*60*24
    
     set @ticks_Jan_01_2000 = 630822816000000000
     set @ticks_MaxDateTime = 3155378975999999999 -- DateTime.MaxValue.Ticks
     
     if (@ticks >= @ticks_MaxDateTime) 
        if @UseIfOutOfRange is not null return @UseIfOutOfRange
        else                            return convert(datetime,'9999-12-31 23:59:59',121)
     ---------------------------------------------------------------
    
    set @ticks_PastJan_01_2000 = @ticks - @ticks_Jan_01_2000
    set @seconds_PastJan_01_2000 = @ticks_PastJan_01_2000 / 10000000 --  Ticks per second  10,000,000
    
    /*
        select @day_seconds                 as [@day_seconds]
              ,@seconds_PastJan_01_2000     as [@seconds_PastJan_01_2000]
              ,@ticks                       as [@ticks]
              ,@ticks_Jan_01_2000           as [@ticks_Jan_01_2000]
              ,@ticks_PastJan_01_2000       as [@ticks_PastJan_01_2000]
    */  
      
    --set @dt = dateadd(second,@seconds_PastJan_01_2000,'2000-01-01')
    while @seconds_PastJan_01_2000 > @day_seconds begin
    
        set @dt = dateadd(day,1,@dt) 
        set @seconds_PastJan_01_2000 = @seconds_PastJan_01_2000 - @day_seconds
    end
    set @dt = dateadd(second,@seconds_PastJan_01_2000,@dt)


    return @dt 
end -- [udf_TicksToDatetime]
GO

IF   OBJECT_ID(N'[dbo].[fn_nums]')is not null
    DROP FUNCTION [dbo].[fn_nums]
GO
CREATE function [dbo].[fn_nums](@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n

--end -- [fn_nums]
GO

if object_id('udf_Conv_PowerOf2Table') is not null
	drop function dbo.udf_Conv_PowerOf2Table
go	
create function dbo.udf_Conv_PowerOf2Table
(
    @MaxPO2   int
)
returns  table
as
/*
    select * from dbo.udf_Conv_PowerOf2Table(31)
*/


    return 
    (
        select  0 as [Power]
               ,1 as Result
       union
       all               
        select n                             as [Power]
               ,cast(POWER(2.0,n) as bigint) as Result
          from 
               dbo.fn_nums( @MaxPO2)
    )
    
--end -- udf_Conv_PowerOf2Table
go

if object_id('udf_Conv_SplitIntBitwise') is not null
	drop function dbo.udf_Conv_SplitIntBitwise
go	
create function dbo.udf_Conv_SplitIntBitwise
(
	@Container int
   ,@MaxNumber   int
)
returns table
as
/*                                                day   1   2   5   31
                                                  power 0   1   4   30
    select * from dbo.udf_Conv_SplitIntBitwise((select  1 | 2 |16 | 1073741824 ) ,30)
    select * from dbo.udf_Conv_SplitIntBitwise(22 ,7)
    select * from dbo.udf_Conv_SplitIntBitwise(23 ,7)
*/
--begin
  return
  (
	select 
	       PO2.[Power] + 1 as [Day]
		  ,PO2.Result      as MaskContribution
	  from dbo.udf_Conv_PowerOf2Table(@MaxNumber) PO2
	 where 
	       @Container & PO2.Result =  PO2.Result
  )	       
--end -- udf_Conv_SplitIntBitwise
go



IF  OBJECT_ID('udf_SecondsOfDate') is not null
    DROP FUNCTION [dbo].[udf_SecondsOfDate]
GO
create function [dbo].[udf_SecondsOfDate]
(	
	@date datetime
)
returns int	
as
/*
	select dbo.udf_SecondsOfDate('2010-10-10 01:10:33.028')

*/
begin

	return DATEPART(second,@date) +  60*DATEPART(minute,@date) + 60*60*DATEPART(hour,@date)

end -- dbo.udf_SecondsOfDate

GO




IF  OBJECT_ID('udf_DatePart') is not null
    DROP FUNCTION [dbo].udf_DatePart
GO
create function [dbo].udf_DatePart
(	
	@date datetime
)
returns datetime	
as
/*
    declare @date datetime
    set @date = '2010-12-31 01:10:33.028' 
    select convert(varchar(10),@date,121)
	select dbo.udf_DatePart(@date)

*/
begin

	return convert(datetime, convert(varchar(10),@date,121) , 121)

end -- dbo.udf_DatePart
GO




if OBJECT_ID('udf_Conv_DoesIndexExist') is not null
    drop function udf_Conv_DoesIndexExist
go    
create function dbo.udf_Conv_DoesIndexExist
(
    @tablename   sysname
   ,@indexname   sysname
)
returns bit
as
/*
    select dbo.udf_Conv_DoesIndexExist('AMTEvents','AMTEvents_Timestamp_IX')
*/
begin

    return (Case
                when exists
	                     (
                            SELECT *
                            FROM  sys.indexes AS i WITH (NOLOCK)
                            JOIN  sys.tables  AS t WITH (NOLOCK)    ON i.[object_id] = t.[object_id]
                            where 
                                  t.[name]   = @tablename
                              and i.[name]   = @indexname               
                             -- and i.index_id > 1 -- nonclustered
                          ) then 1
	             else            0
	        end)
end -- udf_Conv_DoesIndexExist
go

if OBJECT_ID('udf_Conv_DoesIndexExist') is not null
    drop function udf_Conv_DoesIndexExist
go    
create function dbo.udf_Conv_DoesIndexExist
(
    @tablename   sysname
   ,@indexname   sysname
)
returns bit
as
/*
    select dbo.udf_Conv_DoesIndexExist('AMTEvents','AMTEvents_Timestamp_IX')
*/
begin

    return (Case
                when exists
	                     (
                            SELECT *
                            FROM  sys.indexes AS i WITH (NOLOCK)
                            JOIN  sys.tables  AS t WITH (NOLOCK)    ON i.[object_id] = t.[object_id]
                            where 
                                  t.[name]   = @tablename
                              and i.[name]   = @indexname               
                             -- and i.index_id > 1 -- nonclustered
                          ) then 1
	             else            0
	        end)
end -- udf_Conv_DoesIndexExist
go


IF OBJECT_ID('[dbo].[udf_Conv_DoesColumnExist]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_DoesColumnExist]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_DoesColumnExist] >>>'
END
go

create function [dbo].[udf_Conv_DoesColumnExist]
(
    @tablename sysname
   ,@colname   sysname
)
returns bit
as
/*
    select dbo.udf_Conv_DoesColumnExist('WindowsEvent','EventID')
*/
begin

    return (Case
                when exists
	                    (
		                    select * from INFORMATION_SCHEMA.COLUMNS
		                     where TABLE_NAME = @tablename
		                       and COLUMN_NAME = @colname		                       
	                    ) then 1
	             else 0
	        end)
end -- udf_Conv_DoesColumnExist
go

if object_id('DBM_Conv_drop_Column_DF_FK_COLUMN') is not null
	drop procedure dbo.DBM_Conv_drop_Column_DF_FK_COLUMN
go
CREATE procedure [dbo].[DBM_Conv_drop_Column_DF_FK_COLUMN]
(
    @tablename   sysname
   ,@columnname  sysname
   ,@debug       int     = -1
)
as 
/*
    exec [DBM_Conv_drop_Column_DF_FK_COLUMN]
                                                @tablename    = 'TX'
                                               ,@columnname   = 'CY'
                                               ,@debug        = 1
    
*/
begin
    
    declare @sql nvarchar(4000)
    
    exec dbo.DBM_Conv_drop_Column_DF 
                                         @tablename    = @tablename
                                        ,@columnname   = @columnname
                                        ,@debug        = @debug
                                      
    exec dbo.DBM_Conv_drop_Column_FK  
                                         @tablename       = @tablename
                                        ,@firstfkcolumn   = @columnname
                                        ,@reftablename    = ''
           
   exec [dbo].[DBM_Conv_drop_Column_CHK]
                                         @tablename    = @tablename
                                        ,@columnname   = @columnname
                                        ,@debug        = @debug
    


    -- drop column
    set @sql = 
    'if exists
        (
	        select * from INFORMATION_SCHEMA.COLUMNS
             where TABLE_NAME  = ''' + @tablename  + '''
               and COLUMN_NAME = ''' + @columnname + '''
        )
	        ALTER TABLE [dbo].' + @tablename + ' drop column ' + @columnname
	        
   if (@debug = 1) begin 
        print @sql                                    
   end    
   exec (@sql)


end -- [DBM_Conv_drop_Column_DF_FK_COLUMN]
go

IF  OBJECT_ID(N'[dbo].[udf_ErrorInfo]') is not null
DROP FUNCTION [dbo].udf_ErrorInfo
GO

CREATE function [dbo].[udf_ErrorInfo]()
returns nvarchar(max)
as
begin
 
    declare @ErrorMessage nvarchar(max)
           ,@ErrorInfo    nvarchar(max)
    
    set @ErrorMessage = ERROR_MESSAGE()
                    + '[ErrorNumber='  + convert(varchar(10),ERROR_NUMBER())     + ']' 
                    
    set @ErrorInfo  =                     
                      '[PROCEDURE=' +                     ERROR_PROCEDURE()     + ']'    
                    + '[LINE='      + convert(varchar(10),ERROR_LINE     ())    + ']'    
                    + '[SEVERITY='  + convert(varchar(10),ERROR_SEVERITY ())    + ']'    
                    + '[STATE='     + convert(varchar(10),ERROR_STATE    ())    + ']'; 
    
                    
    set @ErrorMessage = @ErrorMessage + ISNULL(@ErrorInfo,'')
    
    return @ErrorMessage
    
end -- udf_ErrorInfo
go

if object_id('udf_ErrorInfo_Script') is not null
    drop function dbo.udf_ErrorInfo_Script
go
CREATE FUNCTION [dbo].[udf_ErrorInfo_Script]
(
	 @Script nvarchar(200)
	,@Step   nvarchar(200)
)
RETURNS nvarchar(max)
AS
BEGIN
		declare @ErrorMessage   nvarchar(max) 

		set @ErrorMessage =   '[Error in: Script=' + isnull(@Script,'null') 
		                  + '] [@Step='            + isnull(@Step     ,'null') 
		                  + '] [@Nest='            + isnull(CAST(@@NESTLEVEL -1 AS nvarchar(10)),'null') 
				          + '] ' + [dbo].[udf_ErrorInfo]()

	return @ErrorMessage

END -- udf_ErrorInfo_Script
go


IF OBJECT_ID('[dbo].[udf_DBM_VersionNumber]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_DBM_VersionNumber]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_DBM_VersionNumber] >>>'
END
go


create function [dbo].[udf_DBM_VersionNumber]()
returns nvarchar(max)
as
/*
    select dbo.udf_DBM_VersionNumber()
*/
begin
    declare @version nvarchar(max)

    set @version = 
    (
        select C.Value
         from dbo.MWConfig C
         where C.[Key] = N'VersionNumber'
     )
    return @version
end


go




IF OBJECT_ID('[dbo].[udf_Conv_CompareVersion]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_CompareVersion]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_CompareVersion] >>>'
END
go


CREATE FUNCTION [dbo].[udf_Conv_CompareVersion]
(
     @StringVersion         VARCHAR(8000)
    ,@MajorVersion           int
    ,@MinorVersion           int
    ,@Build                  int
    ,@Revision               int
)
RETURNS int
/*
     POSITIVE INT   - if @StringVersion is HIGHER
    
     0              - for equal
     
     NEGATIVE INT   - if @StringVersion is LOWER 

*/

AS
/*

   declare @FistVersion   varchar(max) = '1.11.2.1111'
           ,@SecondVersion varchar(max) = '1.11.111.1111' -- '2.22.222.222' 
     -- select * from [dbo].[udf_Conv_FetchVersionMembers] (@FistVersion)
 ;  with FirstMemmbers as 
   (
     select * from [dbo].[udf_Conv_FetchVersionMembers] (@FistVersion)
   )  
   select           
           [dbo].[udf_Conv_CompareVersion] 
                (
                    @SecondVersion
                    ,FirstMemmbers.MajorVersion
                    ,FirstMemmbers.MinorVersion
                    ,FirstMemmbers.Build
                    ,FirstMemmbers.Revision
                )
      from FirstMemmbers
*/
BEGIN

/*
   declare
     @StringVersion         VARCHAR(8000) = '1.2.1'
    ,@MajorVersion           int = 1
    ,@MinorVersion           int = 2
    ,@Build                  int = 1
    ,@Revision               int = 1
*/

 -- just in case :-)
 set @StringVersion = replace(@StringVersion,',','.')
 
 declare 
            @compare int

    DECLARE @pos        INT
    DECLARE @piece      VARCHAR(100)
    declare @pattern    varchar(20)
           ,@ii         int
           ,@pieceInt   int
           ,@prevPos    int
           
    set @pattern = '.'

    -- Need to tack a delimiter onto the end of the input string if one doesn't exist
    IF RIGHT(RTRIM(@StringVersion),1)     <> @pattern
    SET @StringVersion = @StringVersion  + @pattern

    SET @pos =  0 -- charindex(@pattern, @StringVersion)
    set @prevPos = 1
    
    set   @ii = 0
    WHILE @ii < 4
    BEGIN
    
     set @ii    = @ii + 1
     
     SET @pos   = charindex(@pattern, @StringVersion, @prevPos)
     if @pos = 0 begin
        set @StringVersion = @StringVersion + '0.'
        SET @pos   = charindex(@pattern, @StringVersion, @prevPos)
        --set @pos = @prevPos + 1
     end
     
     SET @piece = substring(@StringVersion, @prevPos, @pos - @prevPos) 
     
     if isnumeric(@piece) = 1
        set @pieceInt = cast(@piece as int)
     else
        return null
     
     set @prevPos = @pos + 1     
     
     
     if      @ii = 1
        set @compare = @pieceInt - @MajorVersion
     else 
          if @ii = 2
        set @compare = @pieceInt - @MinorVersion
     else 
          if @ii = 3
        set @compare = @pieceInt - @Build
     else 
          if @ii = 4
        set @compare = @pieceInt - @Revision
     
     /*  
        
     SELECT 
       @ii          as [@ii]
     , @piece       as [@piece]
     , @pos         as [@pos]
     , @prevPos     as [@prevPos]
     , @compare     as [@compare]
     
     */
     
     if @compare <> 0
        break
     

    END -- WHILE



    RETURN @compare -- 0
    
END -- [udf_Conv_CompareVersion]


go

