Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [ProcessGroups_tbl]'
Print ' Creation Date : 08.06.2008'
Print ' Autor         : A.Becker'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]


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

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

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[ProcessGroups_tbl] (
          [Id]                     [uniqueidentifier] NOT NULL,
          [ProcStateCol]           varchar(32)        NOT NULL,  -- legt fest für welche ProcState Spalte diese Definition gilt, muss eindeutig sein
          [SerializedConfigObject] [xml]                        -- speicher die komplette ProcStateConfig als XML-Serialisierung
  ) ON [PRIMARY]

  goto CheckCreateIndex


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

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

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

  -- Alle Updates hierher
  set @ColumeName='SerializedConfigObject'
  if (Select Coalesce(Col_length('ProcessGroups_tbl',@ColumeName),0)) = 0
    begin
      print ('Create Column:' + @ColumeName + ' [speicher die komplette ProcStateConfig als XML-Serialisierung 26.09.2008]')
      set @ExecuteStr='ALTER TABLE [dbo].[ProcessGroups_tbl] ADD [' + @ColumeName +'] [xml] NULL'
      execute (@ExecuteStr)
    end

 goto CheckCreateIndex

CheckCreateIndex:

  If NOT Exists (select Name from sysindexes where Name = 'ProcessGroups_tbl_pk')
  begin
     print (' --> create ProcessGroups_tbl_pk')
     ALTER TABLE [dbo].[ProcessGroups_tbl]
       ADD CONSTRAINT [ProcessGroups_tbl_pk]
       PRIMARY KEY NONCLUSTERED ([Id])
       WITH (
         FILLFACTOR = 80,
         PAD_INDEX = OFF,
         STATISTICS_NORECOMPUTE = OFF,
         ALLOW_ROW_LOCKS = ON,
         ALLOW_PAGE_LOCKS = ON)
  end

  If NOT Exists (select Name from sysindexes where Name = 'ProcessGroups_tbl_uq')
  begin
     print (' --> create ProcessGroups_tbl_uq')
     ALTER TABLE [dbo].[ProcessGroups_tbl]
        ADD CONSTRAINT [ProcessGroups_tbl_uq]
        UNIQUE NONCLUSTERED ([ProcStateCol])
        WITH (
          FILLFACTOR = 80,
          PAD_INDEX = OFF,
          IGNORE_DUP_KEY = OFF,
          STATISTICS_NORECOMPUTE = OFF,
          ALLOW_ROW_LOCKS = ON,
          ALLOW_PAGE_LOCKS = ON)
  end




--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print (@MyActionMsg1)
 Print ('   |-> Done on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
