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

use [#DB_N#]

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

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


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

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[DrawingIndexingSearchFullText_tbl] (
         [Id]                   [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	       [ObjIdRef]             [uniqueidentifier] NOT NULL,
         [inSheet]              [nvarchar](128),
	       [AllValues]            [nvarchar](max) NULL,
	       [AllText]              [nvarchar](max) NULL,
	       [AllNonText]           [nvarchar](max) NULL
  ) ON [PRIMARY]

  -- creaate primary key
  EXECUTE JF_PrimKeyCheckCreateDef 'DrawIdxFullText_pk',   @MyTableName, 'Id'
  -- create fulltext catalog
  CREATE FULLTEXT CATALOG DISFullTextCatalog AS DEFAULT
  -- create full text index
  CREATE FULLTEXT INDEX ON [dbo].[DrawingIndexingSearchFullText_tbl]
  ( 
      [AllValues]  Language 1033,
      [AllText]    Language 1033,
      [AllNonText] Language 1033     
  ) 
  KEY INDEX DrawIdxFullText_pk
  -- deactivate fulltext stop list
  ALTER FULLTEXT INDEX ON [dbo].[DrawingIndexingSearchFullText_tbl] SET STOPLIST = OFF 
  -- enable advanced options
  exec sp_configure 'show advanced options', 1 
  RECONFIGURE 
  -- allow nois words in fulltext search string
  exec sp_configure 'transform noise words', 1 
  RECONFIGURE

  goto CheckCreateIndex


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

  set @MyActionMsg1=
       '-- Table: [DrawingIndexingSearchFullText_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 31.10.2018 S.Gueth
  --set @ColumeName='JobControlActivInfo'
  --if (Select Coalesce(Col_length(@MyTableName,@ColumeName),0)) = 0
  --  begin
  --    print ('Create Column: [' + @ColumeName + '] stores last JobControlActivStatus Rule information [(new 31.10.2018/S.Gueth)]')
  --    set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ADD [' + @ColumeName +'] [nvarchar] (max)'
  --    execute (@ExecuteSQLstr)
  --  end

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


  EXECUTE JF_PrimKeyCheckCreateDef 'DrawIdxFullText_pk',   @MyTableName, 'Id'
  EXECUTE JF_IndexCheckCreateDef   'DrawIdxFullText_IDX1', @MyTableName, '[ObjIdRef], [inSheet]'
  
--------------------------------------------------------------------------------
EndeExit:
 Print '------------------------------------------------------------------------'
 Print 'Done on Database: #DB_N#'
 Print '------------------------------------------------------------------------'

GO
