Print ''
Print '----------------------------------------------------'
Print ' JFTSql_20_PublicProceduresTSQL-TOOLS-JF Scripts    '
Print ' FirstRevision: 19.03.2006'
Print ' CurrRevision : 13.02.2007'
Print ' Autor        : J.Feuerstein'
Print '----------------------------------------------------'
GO

use [#DB_N#]
GO

Print '-'
Print '-- [JF_ShowTimeDiffToNow] --------------------------------------'
If Exists (select Name from sysobjects   where Name = "JF_ShowTimeDiffToNow" and type = "P") Drop Procedure JF_ShowTimeDiffToNow
GO
Create Procedure JF_ShowTimeDiffToNow @Mess1_in nvarchar(max), @DateTime_in datetime
As
BEGIN
  PRINT @Mess1_in + ' duration:[' + convert(varchar(4),DateDiff(ss, @DateTime_in, GetDate())) + ' sec]'
END
GO

--------------------------------------------------------------------------------
Print '-'
Print '-- [JF_DropIndicesOfTable] --------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_DropIndicesOfTable" and type = "P") Drop Procedure JF_DropIndicesOfTable
GO
Create Procedure JF_DropIndicesOfTable @TableName nvarchar(max)
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)
 print('-----------------------------------------------------------------')
 print('delete all indices and constraint of table:[' + @TableName + ']')


 --- Step: löschen aller indices ----------------------------------------------
 set @ExecuteSQLstr=null
 select @ExecuteSQLstr=isnull(@ExecuteSQLstr + ';','')
                      + 'drop index ['+ name + '] on ' + object_name(object_id)
 from sys.indexes
 WHERE [object_id] = OBJECT_ID(@TableName) and ([is_unique]=0 and [is_primary_key]=0)
 if not @ExecuteSQLstr=''
  begin
    print (' - Delete indices via:')
    print (@ExecuteSQLstr)
    exec  (@ExecuteSQLstr)
  end
  else
  begin
    print (' - NO indices to delete')
  end

 --- Step: löschen aller CONSTRAINT's -----------------------------------------
 set @ExecuteSQLstr=null
 select @ExecuteSQLstr=isnull(@ExecuteSQLstr + ';','')
                       + 'ALTER TABLE [dbo].[' + @TableName + ']'
                       + ' DROP CONSTRAINT ['+ [name] + ']'
 from sys.indexes
 WHERE [object_id] = OBJECT_ID(@TableName) and ([is_unique]=1 or [is_primary_key]=1)



 if not @ExecuteSQLstr=''
  begin
    print (' - Delete CONSTRAINT via:')
    print (@ExecuteSQLstr)
    exec  (@ExecuteSQLstr)
  end
  else
  begin
    print (' - NO constains to delete')
  end

END
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [JF_IndexCheckCreateDef] --------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_IndexCheckCreateDef" and type = "P") Drop Procedure JF_IndexCheckCreateDef
GO
Create Procedure JF_IndexCheckCreateDef (
      @IndexName    nvarchar(max) ,
      @TableName    nvarchar(max) ,
      @ColName  nvarchar(max)
     )
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)
 -- print('-----------------------------------------------------------------')

  If NOT Exists (select Name from sysindexes where Name = @IndexName)
  begin
     set @ExecuteSQLstr='CREATE NONCLUSTERED INDEX [' + @IndexName  + '] ON [dbo].[' + @TableName + '] (' + @ColName + ')' +
                        ' WITH (FILLFACTOR = 80) ON [PRIMARY]'
     Print ('Index.Create:[New] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @IndexName + "]" )
     Print ('|-> [via:' + @ExecuteSQLstr + ']')
     exec (@ExecuteSQLstr)
  end
  else
  begin
     Print ('Index.Check:[OK] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @IndexName + "]" )
  end

END
GO
--------------------------------------------------------------------------------
Print '-'
Print '-- [JF_PrimKeyCheckCreateDef] --------------------------------------'
If Exists (select Name  from sysobjects  where Name = "JF_PrimKeyCheckCreateDef" and type = "P") Drop Procedure JF_PrimKeyCheckCreateDef
GO
Create Procedure JF_PrimKeyCheckCreateDef (
      @PrimKeyName  nvarchar(max) ,
      @TableName    nvarchar(max) ,
      @ColName  nvarchar(max)
     )
As
BEGIN
 declare @ExecuteSQLstr nvarchar(max)
 -- print('-----------------------------------------------------------------')

--  If NOT Exists (select Name from sysindexes where Name = @IndexName)
  If NOT Exists (select Name from sysindexes where Name = @PrimKeyName)
  begin
     set @ExecuteSQLstr='ALTER TABLE [dbo].' + @TableName +
                         ' ADD CONSTRAINT [' + @PrimKeyName  + ']' +
                         ' PRIMARY KEY CLUSTERED (' + @ColName + ' ASC)' +
                        ' WITH (' +
                        ' FILLFACTOR = 80,' +
                        ' PAD_INDEX = OFF,' +
                        ' STATISTICS_NORECOMPUTE = OFF,' +
                        ' ALLOW_ROW_LOCKS = ON,' +
                        ' ALLOW_PAGE_LOCKS = ON)'
     Print ('PrimK.Create:[New] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @PrimKeyName + "]" )
     Print ('|-> [via:' + @ExecuteSQLstr + ']')
     exec (@ExecuteSQLstr)
  end
  else
  begin
     Print ('PrimK.Check:[OK] tbl:[' + @TableName + '] Col:[' + @ColName +   '] Index:[' + @PrimKeyName + "]" )
  end

END
GO


--      ALTER TABLE [dbo].[Objects_tbl]
--        ADD CONSTRAINT [Objects_tbl_pk]
--        -- PRIMARY KEY NONCLUSTERED ([Id])
--        -- PRIMARY KEY CLUSTERED ([Id])  angepasst 01.02.2011 07:41
--        PRIMARY KEY CLUSTERED (Id ASC)
--        WITH (
--          FILLFACTOR = 80,
--          PAD_INDEX = OFF,
--          STATISTICS_NORECOMPUTE = OFF,
--          ALLOW_ROW_LOCKS = ON,
--          ALLOW_PAGE_LOCKS = ON)

--------------------------------------------------------------------------------
Print '-'
Print '-- [zzzzzDebug_tbl] --------------------------------------'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzzzzDebug_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[zzzzzDebug_tbl]
GO

-- erstellt die Tabelle: zzzzzDebug_tbl
CREATE TABLE [dbo].[zzzzzDebug_tbl] (
       [ProcGroup]         nvarchar(max)  , -- = Gruppenname
       [Name]              nvarchar(max) , -- = Name Debug wert
       [Value]             nvarchar(max) , -- = Wert
       [DateTimeStamp]     datetime NULL
)
ON [PRIMARY]
GO

Print '-'
Print '-- [zzzzzDebugTbl_AddDataSet] --------------------------------------'
If Exists (select Name from sysobjects where Name = "zzzzzDebugTbl_AddDataSet" and type = "P") Drop Procedure zzzzzDebugTbl_AddDataSet
GO

Create Procedure zzzzzDebugTbl_AddDataSet @ProcGroup   nvarchar(max),
                                          @Name        nvarchar(max),
                                          @Value       nvarchar(max)
/* Lastet die Debug infos in die in die Datenbank ein */
As
BEGIN
     -- set @Value=@Value + N'<END>'
     INSERT INTO zzzzzDebug_tbl (ProcGroup , Name ,   Value  , DateTimeStamp )
                         VALUES (@ProcGroup , @Name , @Value , GetDate() )
     Return 1
END
GO


Print '-'
Print '-- [zzzzzDebugTbl_Empty] --------------------------------------'
If Exists (select Name from sysobjects where Name = "zzzzzDebugTbl_Empty" and type = "P") Drop Procedure zzzzzDebugTbl_Empty
GO

Create Procedure zzzzzDebugTbl_Empty
/* Lastet die Debug infos in die in die Datenbank ein   */
As
BEGIN
     Delete from dbo.zzzzzDebug_tbl
     Return 1
END
GO


-- Beispiel für erstellung eines Datensatzes
-- EXECUTE zzzzzDebugTbl_AddDataSet 'TestProcGroup' , 'TestName' , 'TestValue'

Print '-'
Print '-- [JF_DateResetCheck] -------------------------------------------'
If Exists (select Name from sysobjects  where Name = "JF_DateResetCheck" and type = "P") Drop Procedure JF_DateResetCheck
GO

Create Procedure JF_DateResetCheck @DateResetCheckIn datetime Output
-- new 9.12.2008 J.Fes
-- check for JF_DateReset
-- Funktion: @DateResetCheckIn wird = NULL wenn @DateResetCheckIn das Datum = 01.01.1900 hat
As
BEGIN
 if @DateResetCheckIn = '19000101'
  BEGIN
   set @DateResetCheckIn = NULL
  END
END
GO




--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Print '-'
Print '-- [zzzzzDbMaintain_tbl] --------------------------------------'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[zzzzzDbMaintain_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto zzzzzDbMaintain_tbl_TableUpdate
  END
ELSE
  BEGIN
   goto zzzzzDbMaintain_tbl_TableCreate
  END

zzzzzDbMaintain_tbl_TableCreate:

-- erstellt die Tabelle: zzzzzDbMaintain_tbl
CREATE TABLE [dbo].[zzzzzDbMaintain_tbl] (
       [Id]                uniqueidentifier NOT NULL,
       [Name]              nvarchar(64) , -- Name
       [Value]             nvarchar(max) , -- Wert
       [DateTimeStamp]     datetime NULL   -- Zeitstempel an dem der Eintrag vorgenommen wurde
)
ON [PRIMARY]

goto zzzzzDbMaintain_tbl_TableUpdate

zzzzzDbMaintain_tbl_TableUpdate:

/*
  If NOT Exists (select Name from sysindexes where Name = 'zzzzzDbMaintain_tbl_pk')
  begin
     -- CONSTRAINT [Objects_tbl_pk] PRIMARY KEY NONCLUSTERED ([Id])
     Print(' --> create index: zzzzzDbMaintain_tbl_pk')
     ALTER TABLE [dbo].[zzzzzDbMaintain_tbl]
       ADD CONSTRAINT [zzzzzDbMaintain_tbl_pk]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end
  */
  EXECUTE JF_PrimKeyCheckCreateDef 'zzzzzDbMaintain_tbl_pk', 'zzzzzDbMaintain_tbl', 'Id'

  EXECUTE JF_IndexCheckCreateDef 'zzzzzDbMaintain_tbl_Idx01', 'zzzzzDbMaintain_tbl', 'Name'
  /*
  If NOT Exists (select Name from sysindexes where Name = 'zzzzzDbMaintain_tbl_IDX01')
  begin
     CREATE NONCLUSTERED INDEX [zzzzzDbMaintain_tbl_IDX01] ON [dbo].[zzzzzDbMaintain_tbl]
     ([Name])
     WITH (
       PAD_INDEX = OFF,
       DROP_EXISTING = OFF,
       STATISTICS_NORECOMPUTE = OFF,
       SORT_IN_TEMPDB = OFF,
       ONLINE = OFF,
       ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON)
  end
  */
GO


Print '-'
Print '-- [zzzzzDbMaintain_valueSave] -------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "zzzzzDbMaintain_valueSave" and type = "P")
Drop Procedure zzzzzDbMaintain_valueSave
GO


Create Procedure zzzzzDbMaintain_valueSave @Name             nvarchar(64) ,
                                           @Value            nvarchar(max)
-- sichert Name mit Value
As
BEGIN
  Declare @Id uniqueidentifier
  set @Id=Null
  select @Id=Id from zzzzzDbMaintain_tbl
         where Name = @Name

  if @Id IS NULL
     BEGIN
       SET @Id = NEWID()
       INSERT INTO zzzzzDbMaintain_tbl (ID ,  Name ,   Value , DateTimeStamp)
                               VALUES  (@Id,  @Name , @Value , GetDate())
     END
    ELSE
     BEGIN
      UPDATE zzzzzDbMaintain_tbl
            SET Name = @Name ,
                Value = @Value ,
                DateTimeStamp = GetDate()
            Where ID = @Id
     END
END
GO

Print '-'
Print '-- [zzzzzDbMaintain_valueGet] -------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "zzzzzDbMaintain_valueGet" and type = "P")
Drop Procedure zzzzzDbMaintain_valueGet
GO


Create Procedure zzzzzDbMaintain_valueGet
                                           @Name  nvarchar(64) ,
                                           @Value nvarchar(max) Output ,
                                           @ValueCompare nvarchar(max),
                                           @ValueCompareStat int Output
-- gibt das Value zu einem Namen zurück
As
BEGIN
  Declare @Id uniqueidentifier
  set @Value=Null
  set @Id=Null

  select @Id=Id, @Value=Value from zzzzzDbMaintain_tbl  where Name = @Name

  if @Id IS Not NULL
     BEGIN
       if @Value = @ValueCompare
          begin
           -- Value Exisiert und ist gleich
           set @ValueCompareStat = 1
          end
       ELSE
          begin
           -- Value Exisiert und ist ungleich
           set @ValueCompareStat = 2
          end
     END
    ELSE
     BEGIN
       -- Value Exisiert nicht
       set @ValueCompareStat = 3
     END
END
GO




/*

Print ''
Print '---- TEST ----'
DECLARE @Id uniqueidentifier
DECLARE @Name nvarchar(64)
DECLARE @Value nvarchar(max)
DECLARE @ValueCompareStat int

-- Example wert gespeichert !!
EXECUTE zzzzzDbMaintain_valueSave 'TestNameXY' , 'TestValue'

set @Name='TestName'
EXECUTE zzzzzDbMaintain_valueGet @Name , @Value Output , 'TestValue' , @ValueCompareStat Output
PRINT '@Name=' + @Name + ' @Value:' + @Value + ' @Id:' + CONVERT(varchar(100), @Id) + ' @ValueCompareStat:' + CONVERT(varchar(100),@ValueCompareStat)


set @Name='TestName not found'
EXECUTE zzzzzDbMaintain_valueGet @Name , @Value Output , 'TestValue' , @ValueCompareStat Output

if @Value IS NULL
 begin
  PRINT '>@Name=' + @Name + '<@Value:NULL' + ' @ValueCompareStat:' + CONVERT(varchar(100),@ValueCompareStat)
 end
 else
 begin
  PRINT '>@Name=' + @Name + '< @Value:' + CONVERT(varchar(100), @Value) +  ' @Id:' + CONVERT(varchar(100), @Id)
 end

 -- PRINT '>@Name=' + @Name + '< @Value:' + CONVERT(varchar(100), @Value) +  ' @Id:' + CONVERT(varchar(100), @Id)
 */


Print ''
Print '---------------------------------------------------------'
Print ' JFTSql_20_PublicProceduresTSQL-TOOLS-JF Scripts Imported'
Print '---------------------------------------------------------'
Print ''
GO

-- select Name,type from sysobjects where name like 'Friend%'
-- go
