/*
 * ER/Studio Data Architect 11.0 SQL Code Generation
 * Company :      LPI
 * Project :      MWData_TRUNK_Express.dm1
 * Author :       LPI
 *
 * Date Created : Tuesday, February 07, 2017 14:23:44
 * Target DBMS : Microsoft SQL Server 2008
 */

/* 
 * FUNCTION: [dbo].[fn_CSVToTable] 
 */

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



CREATE FUNCTION [dbo].[fn_CSVToTable] ( @StringInput VARCHAR(MAX) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN
    DECLARE @String    VARCHAR(10)
    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))
        INSERT INTO @OutputTable ( [String] )
        VALUES ( RTRIM(LTRIM(@String)) )
    END
    RETURN
END


go

/* 
 * FUNCTION: [dbo].[fn_FormatTime_days_HHMMSS] 
 */

IF OBJECT_ID('[dbo].[fn_FormatTime_days_HHMMSS]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[fn_FormatTime_days_HHMMSS]
    PRINT '<<< DROPPED FUNCTION [dbo].[fn_FormatTime_days_HHMMSS] >>>'
END
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

/* 
 * FUNCTION: [dbo].[fn_GetIPAddressNumeric] 
 */

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

/* 
 * FUNCTION: [dbo].[fn_nums] 
 */

IF OBJECT_ID('[dbo].[fn_nums]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[fn_nums]
    PRINT '<<< DROPPED FUNCTION [dbo].[fn_nums] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_CHK_Name] 
 */

IF OBJECT_ID('[dbo].[udf_CHK_Name]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_CHK_Name]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_CHK_Name] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_CompareVersion] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_Conv_DoesColumnExist] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_Conv_DoesColumnExist_wSchema] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_DoesColumnExist_wSchema]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_DoesColumnExist_wSchema]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_DoesColumnExist_wSchema] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_DoesIndexExist] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_DoesIndexExist]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_DoesIndexExist]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_DoesIndexExist] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_DoesTableExist] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_DoesTableExist]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_DoesTableExist]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_DoesTableExist] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_DoesViewExist] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_DoesViewExist]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_DoesViewExist]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_DoesViewExist] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_IsUpgrade] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_IsUpgrade]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_IsUpgrade]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_IsUpgrade] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_PowerOf2Table] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_PowerOf2Table]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_PowerOf2Table]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_PowerOf2Table] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_SplitIntBitwise] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_SplitIntBitwise]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_SplitIntBitwise]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_SplitIntBitwise] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_Conv_WeekDaysTable] 
 */

IF OBJECT_ID('[dbo].[udf_Conv_WeekDaysTable]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_Conv_WeekDaysTable]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_Conv_WeekDaysTable] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_DatePart] 
 */

IF OBJECT_ID('[dbo].[udf_DatePart]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_DatePart]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_DatePart] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_DBM_PKName_FromTablename] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_DBM_VersionNumber] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_DF_Name] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_ErrorInfo] 
 */

IF OBJECT_ID('[dbo].[udf_ErrorInfo]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_ErrorInfo]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_ErrorInfo] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_ErrorInfo_Script] 
 */

IF OBJECT_ID('[dbo].[udf_ErrorInfo_Script]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_ErrorInfo_Script]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_ErrorInfo_Script] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_GetConfigurationValue] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_OneColumnFK_Name] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_PK_Name] 
 */

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

/* 
 * FUNCTION: [dbo].[udf_SecondsOfDate] 
 */

IF OBJECT_ID('[dbo].[udf_SecondsOfDate]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_SecondsOfDate]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_SecondsOfDate] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_TicksToDatetime] 
 */

IF OBJECT_ID('[dbo].[udf_TicksToDatetime]') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[udf_TicksToDatetime]
    PRINT '<<< DROPPED FUNCTION [dbo].[udf_TicksToDatetime] >>>'
END
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

/* 
 * FUNCTION: [dbo].[udf_TimestampSuffixLocalTime] 
 */

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(16)
as
/*
    select dbo.udf_TimestampSuffixLocalTime()
*/
begin
    return  '_' + replace(
                           replace(
                                    replace(
                                            convert(varchar(19),getdate(),120)
                                            ,' ','_')
                                    ,'-','')
                           ,':','')
end -- udf_TimestampCharForRename


go

/* 
 * FUNCTION: [dbo].[udf_VarDomainName_fromDbName] 
 */

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

