Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Table         : [DrawingIndexingSearch_tbl]'
Print ' Creation Date : 15.11.2018 S.Gueth'
Print ' Last Update   : 05.03.2023 J.Fes'
Print ' '
Print '-------------------------------------------------------------------------'

use [#DB_N#]

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

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


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

  -- Info: bei Struktur anpassungen GitterVar_Cls.ReplaceTextString beachten
  CREATE TABLE [dbo].[DrawingIndexingSearch_tbl] (
         [Id]                   [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
	        [ObjIdRef]             [uniqueidentifier] NOT NULL,
         [EntHandel]            [nvarchar](256),
	        [Value]                [nvarchar](max) NOT NULL, -- Colume Value length from 2000 -> max (21.07.2022 S.Gueth)
	        [Type]                 [varchar](5) ,
	        [inSheet]              [nvarchar](128) ,
         [inView]               [nvarchar](128) ,
         [CoordinateX]          [float](24),
         [CoordinateY]          [float](24),
         [CoordinateZ]          [float](24)
  ) ON [PRIMARY]

  goto TableCheck

--------------------------------------------------------------------------------
TableCheck:

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

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

  Declare @CollateToSet nvarchar (256)
  set @CollateToSet='Latin1_General_CI_AS'
  -- set @CollateToSet='Latin1_General_BIN2'
                         
  --  Colume Value length from 2000 -> max (21.07.2022 S.Gueth)
  --  ALTER TABLE [dbo].[DrawingIndexingSearch_tbl] ALTER COLUMN [Value]     nvarchar(max) COLLATE Latin1_General_CI_AS  WITH (ONLINE = ON)
  set @ColumeName='Value'
  set @ColumeSize = 'nvarchar(max)'
  if (SELECT c.collation_name  FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = @MyTableName and c.name = @ColumeName ) <> @CollateToSet
   or
     (Select Col_length(@MyTableName,@ColumeName)) <> -1
  begin
     print ('Column:Value upsize to max char')
     set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ALTER COLUMN [' + @ColumeName + '] ' + @ColumeSize + ' COLLATE ' +  @CollateToSet + ' WITH (ONLINE = ON)'
     execute sp_executesql @ExecuteSQLstr
  end

  --   ALTER TABLE [dbo].[DrawingIndexingSearch_tbl] ALTER COLUMN [EntHandel] nvarchar(256) COLLATE Latin1_General_CI_AS  WITH (ONLINE = ON)
 set @ColumeName = 'EntHandel'
 set @ColumeSize = 'nvarchar(256)'
 if (SELECT c.collation_name  FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = @MyTableName and c.name = @ColumeName ) <> @CollateToSet
 begin
   Print (' Update Collation at #Table:''' + @MyTableName + ''' #Colume''' + @ColumeName +  ''' to collation:''' + @CollateToSet + '''')
   set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ALTER COLUMN [' + @ColumeName + '] ' + @ColumeSize + ' COLLATE ' +  @CollateToSet + ' WITH (ONLINE = ON)'
   execute sp_executesql @ExecuteSQLstr
 end

--   ALTER TABLE [dbo].[DrawingIndexingSearch_tbl] ALTER COLUMN [Type]      varchar(5)    COLLATE Latin1_General_CI_AS  WITH (ONLINE = ON)
 set @ColumeName = 'Type'
 set @ColumeSize = 'varchar(5)'
 if (SELECT c.collation_name  FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = @MyTableName and c.name = @ColumeName ) <> @CollateToSet
 begin
   Print (' Update Collation at #Table:''' + @MyTableName + ''' #Colume''' + @ColumeName +  ''' to collation:''' + @CollateToSet + '''')
   set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ALTER COLUMN [' + @ColumeName + '] ' + @ColumeSize + ' COLLATE ' +  @CollateToSet + ' WITH (ONLINE = ON)'
   execute sp_executesql @ExecuteSQLstr
 end


 --   ALTER TABLE [dbo].[DrawingIndexingSearch_tbl] ALTER COLUMN [inSheet]   nvarchar(128) COLLATE Latin1_General_CI_AS  WITH (ONLINE = ON)
 set @ColumeName = 'inSheet'
 set @ColumeSize = 'nvarchar(128)'
 if (SELECT c.collation_name  FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = @MyTableName and c.name = @ColumeName ) <> @CollateToSet
 begin
   Print (' Update Collation at #Table:''' + @MyTableName + ''' #Colume''' + @ColumeName +  ''' to collation:''' + @CollateToSet + '''')
   DROP INDEX DrawIdx_IDX1 ON dbo.DrawingIndexingSearch_tbl
   set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ALTER COLUMN [' + @ColumeName + '] ' + @ColumeSize + ' COLLATE ' +  @CollateToSet + ' WITH (ONLINE = ON)'
   execute sp_executesql @ExecuteSQLstr
 end

 --   ALTER TABLE [dbo].[DrawingIndexingSearch_tbl] ALTER COLUMN [inView]    nvarchar(128) COLLATE Latin1_General_CI_AS  WITH (ONLINE = ON)
 set @ColumeName = 'inView'
 set @ColumeSize = 'nvarchar(128)'
 if (SELECT c.collation_name  FROM SYS.COLUMNS c JOIN SYS.TABLES t ON t.object_id = c.object_id WHERE t.name = @MyTableName and c.name = @ColumeName ) <> @CollateToSet
 begin
   Print (' Update Collation at #Table:''' + @MyTableName + ''' #Colume''' + @ColumeName +  ''' to collation:''' + @CollateToSet + '''')
   set @ExecuteSQLstr='ALTER TABLE [dbo].[' + @MyTableName + '] ALTER COLUMN [' + @ColumeName + '] ' + @ColumeSize + ' COLLATE ' +  @CollateToSet + ' WITH (ONLINE = ON)'
   execute sp_executesql @ExecuteSQLstr
 end


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

  EXECUTE JF_PrimKeyCheckCreateDef 'DrawIdx_pk',   @MyTableName, 'Id'
  EXECUTE JF_IndexCheckCreateDef   'DrawIdx_IDX1', @MyTableName, '[ObjIdRef], [inSheet]'

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

GO
