Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [GenericValues_tbl]'
Print ' Creation Date : 09.09.2019'
Print ' Autor         : S.Gueth'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Declare @MyActionMsg1 nvarchar (max)
Declare @MyTableName nvarchar (max)
    set @MyTableName='GenericValues_tbl'

--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GenericValues_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END
  


--------------------------------------------------------------------------------
TableCreate:
  set @MyActionMsg1=
      '-- Table: [GenericValues_tbl] dose not exist and will be created new'
  Print (@MyActionMsg1)

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[GenericValues_tbl] (
        [Id]              [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
        [ReffId]          [uniqueidentifier],                                     -- added 23.03.2021 S.Gueth
        [Name]            [nvarchar](256) NOT NULL,
	       [Value]           [nvarchar](max) ,
	       [Type]            [nvarchar](256),
	       [DateTime]        datetime,
        [ObjectValue_xml] [xml] NULL                                              -- added 23.03.2021 S.Gueth
  ) ON [PRIMARY]

  goto CheckCreateIndex


--------------------------------------------------------------------------------
TableUpdate:

  set @MyActionMsg1=
       '-- Table: [GenericValues_tbl] exist and will be updated'
  Print (@MyActionMsg1)

  Declare @ColumeName nvarchar (256)
  Declare @ColumeNameOld nvarchar (256)
  Declare @ColumeNameNew nvarchar (256)
  Declare @ExecuteSQLstr nvarchar (max)
                         

  -- New 23.03.2021 S.Gueth
  set @ColumeName='ReffId'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] stores refference id information[(new 21.03.2021/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [uniqueidentifier]'
      execute (@ExecuteSQLstr)
    end

  -- New 23.03.2021 S.Gueth
  set @ColumeName='ObjectValue_xml'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column: [' + @ColumeName + '] stores object xml [(new 21.03.2021/S.Gueth)]')
      set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [xml]'
      execute (@ExecuteSQLstr)
    end

goto CheckCreateIndex
--------------------------------------------------------------------------------
  CheckCreateIndex:

  EXECUTE JF_PrimKeyCheckCreateDef 'GenericIdx_pk',   @MyTableName, 'Id'
  
--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'

GO
