Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [ObjectGroups_tbl]'
Print ' Creation Date : 20.08.2015'
Print ' Last Update.. : 05.11.2018/S.Gueth'
Print ' Autor         : A.Becker'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


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

Declare @ColumeName nvarchar (256)
Declare @ColumeNameOld nvarchar (256)
Declare @ColumeNameNew nvarchar (256)
Declare @ExecuteStr nvarchar (max)
--------------------------------------------------------------------------------
-- verzweigen auf TableCreate: oder TableUpdate:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ObjectGroups_tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  BEGIN
   goto TableUpdate
  END
ELSE
  BEGIN
   goto TableCreate
  END

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

  CREATE TABLE [dbo].[ObjectGroups_tbl] (
	[Id] [uniqueidentifier] NOT NULL,
	[GroupKey] [nvarchar](128) NOT NULL,
	[GroupName] [nvarchar](255) NULL,
	[GroupDescription] [nvarchar](max) NULL,
 [User] [nvarchar](128) NULL,
 [CreationDate] [datetime] NULL,
 [LastModDate] [datetime] NULL,
	[ObjectReferenceCount_ITEM] INT NOT NULL CONSTRAINT [DF_ObjectGroups_tbl_ObjectReferenceCount_ITEM] DEFAULT (0),
	[ObjectReferenceCount_IR]   INT NOT NULL CONSTRAINT [DF_ObjectGroups_tbl_ObjectReferenceCount_IR]   DEFAULT (0),
	[ObjectReferenceCount_DS]   INT NOT NULL CONSTRAINT [DF_ObjectGroups_tbl_ObjectReferenceCount_DS]   DEFAULT (0)
    )
    ON [PRIMARY]

  INSERT INTO [dbo].[ObjectGroups_tbl] (Id, GroupKey, GroupName)
    SELECT NEWID(), N'Gr01', 'not named yet' UNION ALL
    SELECT NEWID(), N'Gr02', 'not named yet' UNION ALL
    SELECT NEWID(), N'Gr03', 'not named yet'

goto CheckCreateIndex

--------------------------------------------------------------------------------
TableUpdate:
  set @MyActionMsg1=
       '-- Table: [' + @MyTableName + '] exist and will be updated'
  Print (@MyActionMsg1)
  
   -- Colume GroupKey increase length from 32 -> 128  (05.11.2018 S.Gueth)
  set @ColumeName='GroupKey'
  if (Select Col_length(@MyTableName, @ColumeName)) <> 128*2
  begin
     print ('Column:' + @ColumeName + ' upsize to 128 char 05.11.2018 ')
     ALTER TABLE [dbo].[ObjectGroups_tbl]
     ALTER COLUMN [GroupKey] nvarchar(128) COLLATE Latin1_General_CI_AS
  end
  
  -- add Column User (04.04.2022 S.Gueth)
  set @ColumeName='User'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. User (new 04.04.2022 S.Gueth)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] nvarchar(128) NULL'
      execute (@ExecuteStr)
    end

  -- add Column CreationDate (04.04.2022 S.Gueth)
  set @ColumeName='CreationDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. CreationDate (new 04.04.2022 S.Gueth)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime NULL'
      execute (@ExecuteStr)
    end

  -- add Column LastModDate (04.04.2022 S.Gueth)
  set @ColumeName='LastModDate'
  if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName +  ' .. LastModDate (new 04.04.2022 S.Gueth)]')
      set @ExecuteStr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] datetime NULL'
      execute (@ExecuteStr)
    end

  goto CheckCreateIndex


--------------------------------------------------------------------------------
CheckCreateIndex:      


  EXECUTE JF_PrimKeyCheckCreateDef 'PK_ObjectGroups_tbl',           @MyTableName, 'Id'


EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'


GO
