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

use [#DB_N#]

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

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


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

  CREATE TABLE [dbo].[SolidEdge_ExternalData_tbl] (
  
          [JobMgrObjectTbl_ID]    [uniqueidentifier] NOT NULL ,
          [File_DPNE]             [nvarchar]  (256),  
          [File_NE]               [nvarchar]  (256),
          [File_DP]               [nvarchar]  (256),
          [File_E]                [nvarchar]  (10),
          [DocumentNumber]        [nvarchar]  (256),  
          [RevisionID]            [nvarchar]  (256),  
          [IRRevID]               [nvarchar]  (256),
          [VersionSequence]       [nvarchar]  (256),  
          [VersionType]           [nvarchar]  (256),  
          [ProjectName]           [nvarchar]  (256),  
          [PartVolume]            [float] (24),  
          [Finishing_Top]         [nvarchar]  (256),  
          [Finishing_Base]        [nvarchar]  (256),  
          [Material]              [nvarchar]  (256),  
          [Mass]                  [float] (24),  
          [ReferencePart]         [nvarchar]  (256),  
          [Subzerotreatment]      [nvarchar]  (256),  
          [SurfaceArea]           [float] (24),  
          [Treatment]             [nvarchar]  (256),  
          [X_LEN]                 [float] (24),
          [Y_LEN]                 [float] (24),
          [Z_LEN]                 [float] (24),
          [CreationDate]          [datetime],
          [OwningUser]            [nvarchar]  (256),
          [OwningUserOrg]         [nvarchar]  (256),
          [OwningGroup]           [nvarchar]  (256),
          [ProjectID]             [nvarchar]  (256),
          [ProjectIDComputed]     [nvarchar]  (max),
          [PartNature]            [nvarchar]  (256),
          [Subject]               [nvarchar]  (256),
          [Category]              [nvarchar]  (256),
          [Keywords]              [nvarchar]  (256),
          [Title]                 [nvarchar]  (256),
          [Author]                [nvarchar]  (256),
          [ItemPUID]              [nvarchar]  (50),
          [ItemRevPUID]           [nvarchar]  (50),
          [DatasetPUID]           [nvarchar]  (50),
          [NamedRefPUID]          [nvarchar]  (50),
          [ReleaseDate]           [datetime],
          [ECONumber]             [nvarchar]  (50),
          [UnitOfMeasure]         [nvarchar]  (50),
          [SpecialType]           [nvarchar]  (128),
          [AdditionalStatus]      [nvarchar]  (128),
          [Product]               [nvarchar]  (128),
          [ProductGroup]          [nvarchar]  (128),
          [ModelRevision]         [nvarchar]  (128),
          [DraftRevision]         [nvarchar]  (128),
          [CheckedBy]             [nvarchar]  (128),
          [CheckDate]             [nvarchar]  (128),
          [FrozenDate]            [datetime],
          [ExpiredDate]           [datetime],
          [RevOrder]              [int],
          [isLayout]              [int],
          [AlreadyInTC]           [nvarchar]  (50)

  ) ON [PRIMARY]

  goto CheckCreateIndex


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

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

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


--   set @ColumeName='ComputerFQDN'
--   if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
--     begin
--       print ('Create Column:' + @ColumeName + ' [(new 09.07.2010)]')
--       set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (256) NULL'
--       execute (@ExecuteSQLstr)
--     end



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

   EXECUTE JF_PrimKeyCheckCreateDef 'JobMgrObjectTblID_pk',   @MyTableName, 'JobMgrObjectTbl_ID'
  
--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'

GO
