dbo.QBM_PCustomSQLFill

1     create   procedure QBM_PCustomSQLFill (@GenProcID varchar(38) = null , @XUserExtern nvarchar(64) = null , @ModuleToSave varchar(3) = 'CCC' 
2) as begin declare @Schema_Id int declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @DebugSwitch int =
3 0 declare @DebugLevel varchar(1) = 'W' declare @CountItems int declare @Message nvarchar(1000)       SET XACT_ABORT OFF BEGIN TRY if @XUserExtern > ' '
4 begin select @XUser = @XUserExtern end select top 1 @Schema_Id = schema_id from sys.schemas where name = N'dbo' if @GenProcID is null begin select @GenProcID
5 = NEWID() end  if @DebugSwitch > 0 begin print 'Start ' + convert(varchar(64), getutcdate(), 121) end select @CountItems = 1 while @CountItems > 0 begin
6 delete QBMCustomSQL where uid_QBMCustomSQL in (select max(uid_QBMCustomSQL)  from QBMCustomSQL group by scripttype, scriptname having count(*) > 1 ) select
7 @CountItems = @@ROWCOUNT end  drop table if exists #QBMCustomSQL create table #QBMCustomSQL ( UID_QBMCustomSQL varchar(38) collate database_default not
8 null, ScriptType nvarchar(1) collate database_default NULL , XDateInserted datetime NULL, XDateupdated datetime NULL, ScriptName nvarchar(255)collate 
9database_default NULL , ScriptCode nvarchar(max) collate database_default NULL , IsTooLong bit default 0 ) drop table if exists #ModuleNameMitMinus create
10 table #ModuleNameMitMinus (Modulename4 varchar(4) collate database_default) insert into #ModuleNameMitMinus(Modulename4) Select md.ModuleName + '-' from
11 QBMModuleDef md with (readpast) where md.ModuleName <> 'CCC' create index #ModuleNameMitMinus1 on #ModuleNameMitMinus (Modulename4)  insert into #QBMCustomSQL
12 (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype
13 + x.Scriptname) , dbo.QBM_FSQTableDefCond(x.Scriptname) , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert
14(nvarchar(64), so.name) as Scriptname , N'U' as ScriptType, so.create_date as Xdateinserted, so.modify_date as Xdateupdated , case when len(so.name) > 
1530 then 1 else 0 end as IsTooLong from sys.tables so with (readpast)  join DialogTable t with (readpast, forceseek) on convert(varchar(30), left(so.name
16,30)) = t.TableName left outer join #ModuleNameMitMinus mm on left(t.UID_DialogTable, 4) = mm.Modulename4 where so.type = 'U' and so.is_memory_optimized
17 = 0 and so.schema_id = @Schema_id and ( @ModuleToSave = 'CCC' and mm.Modulename4 is null or @ModuleToSave <> 'CCC' and mm.Modulename4 > ' ' )         
18 ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID
19(@ModuleToSave, x.Scripttype + x.Scriptname) , dbo.QBM_FSQColumAdd(x.ScriptName) , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong
20 from ( select convert(nvarchar(64), t.name) + N'.' + convert(nvarchar(64), c.name) as ScriptName , N'C' as ScriptType , t.create_date as Xdateinserted
21, t.modify_date as Xdateupdated , case when len(t.name) > 30 or len(c.name) > 30 then 1 else 0 end as IsTooLong from sys.tables t with (readpast) join 
22sys.columns c with (readpast) on t.object_id = c.object_id and t.type = N'U' and t.is_memory_optimized = 0  join DialogTable dt with (readpast, forceseek
23) on convert(varchar(30), left(t.name, 30)) = dt.TableName  join DialogColumn dc with (readpast, forceseek) on dt.UID_DialogTable = dc.UID_DialogTable 
24and convert(varchar(30), left(c.name,30)) = dc.columnname left outer join #ModuleNameMitMinus mm on left(dc.UID_DialogColumn, 4) = mm.Modulename4 where
25          ( @ModuleToSave = 'CCC' and mm.Modulename4 is null or @ModuleToSave <> 'CCC' and mm.Modulename4 > ' ' ) and t.schema_id = @Schema_id ) as x insert
26 into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave
27, x.Scripttype + x.Scriptname) , x.IndexDef , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert(nvarchar(64
28), t.name) + N'.' + convert(nvarchar(64), i.name) as ScriptName , N'I' as ScriptType , t.create_date as Xdateinserted, t.modify_date as XdateUpdated , 
29case when len(t.name) > 30 or len(i.name) > 30 then 1 else 0 end as IsTooLong , dbo.QBM_FSQIndexDef(t.name, i.name) as IndexDef from sys.tables t with 
30(readpast) join sys.indexes i with (readpast) on t.Object_id = i.Object_id and t.type = N'U' and t.is_memory_optimized = 0  and i.is_hypothetical = 0  
31and i.type not in (3,4,5,6) join DialogTable dt with (readpast, forceseek) on convert(varchar(30), left(t.name, 30)) = dt.TableName  left outer join QBM_VQBMModuleDef
32 md on convert(varchar(3), left(i.name, 3)) = md.ModuleName and md.ModuleName <> 'CCC' where (  @ModuleToSave = 'CCC' and md.ModuleName is null     or 
33@ModuleToSave <> 'CCC' and left(i.name, 3) = @ModuleToSave )    and i.is_primary_key = 0 and i.is_hypothetical = 0       and i.name not like 'missing[_]index[_]%'
34  ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID
35(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert
36(nvarchar(64), p.name) as Scriptname, N'P' as ScriptType , p.create_date as Xdateinserted, p.modify_date as XdateUpdated , sc.definition as Scriptcode 
37, case when len(p.name) > 30 then 1 else 0 end as IsTooLong from sys.objects p with (readpast) join sys.sql_modules sc with (readpast) on p.Object_id =
38 sc.Object_id and sc.definition > ' '  where p.type = N'P' and left(p.name, 3) not in (N'sp_', N'ms_', N'dt_') and (  @ModuleToSave = 'CCC' and LEFT(p.name
39,4) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave
40 + '_' ) and p.schema_id = @Schema_id ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated
41, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted, 
42x.Xdateupdated , x.IsTooLong from ( select p.name as Scriptname , N'V' as ScriptType, p.create_date as Xdateinserted, p.modify_date as XdateUpdated , sc.definition
43 as Scriptcode , case when len(p.name) > 30 then 1 else 0 end as IsTooLong from sys.objects p with (readpast) join sys.sql_modules sc with (readpast) on
44 p.Object_id = sc.Object_id and sc.definition > ' '  where p.type = N'V' and not exists (select top 1 1 from DialogTable t with (readpast, forceseek) where
45 t.tabletype in ( 'V', 'P', 'U', 'R') and t.TableName = convert(varchar(30), left(p.name, 30)) ) and p.name not like 'sys%' and p.name not like 'ms%' and
46 p.name not like 'IH%' and p.name not like 'conflict%' and (  @ModuleToSave = 'CCC' and LEFT(p.name,4) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef
47 md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave + '_' ) and p.schema_id = @Schema_id  and p.name not in
48 ( 'QBMBufferTransfer' , 'AccProductInMirrorShop' , 'MirrorShopHasPWODecisionMethod' , 'QBMDBQueueOverview_fix' , 'QBMDBQueueSlot' , 'QBMDBQueueSlot_fix'
49 , 'QBMDBQueueTaskPerf_fix' , 'QBMVBlobInternal' ) ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted
50, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted
51, x.Xdateupdated , x.IsTooLong from ( select p.name as Scriptname , N'F' as ScriptType , p.create_date as Xdateinserted, p.modify_date as XdateUpdated 
52, sc.definition as Scriptcode , case when len(p.name) > 30 then 1 else 0 end as IsTooLong from sys.objects p with (readpast) join sys.sql_modules sc with
53 (readpast) on p.Object_id = sc.Object_id and sc.definition > ' '  where p.type = N'FN' and (  @ModuleToSave = 'CCC' and LEFT(p.name,4) not in (Select 
54md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave + '_' ) and 
55p.schema_id = @Schema_id and p.name not in ( 'fn_diagramobjects' , 'vid_ErwinC'   ) ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname
56, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname
57, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select p.name as Scriptname, N'N' as ScriptType, p.create_date as Xdateinserted, p.modify_date
58 as XdateUpdated , sc.definition as Scriptcode , case when len(p.name) > 30 then 1 else 0 end as IsTooLong from sys.objects p with (readpast) join sys.sql_modules
59 sc with (readpast) on p.Object_id = sc.Object_id and sc.definition > ' '  where p.type in( N'TF', N'IF') and (  @ModuleToSave = 'CCC' and LEFT(p.name,4
60) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave
61 + '_' ) and p.schema_id = @Schema_id ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated
62, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted, 
63x.Xdateupdated , x.IsTooLong from ( select p.name as Scriptname, N'T' as ScriptType, p.create_date as Xdateinserted, p.modify_date as XdateUpdated , sc.definition
64 as Scriptcode , case when len(p.name) > 30 then 1 else 0 end as IsTooLong from sys.objects p with (readpast) join sys.sql_modules sc with (readpast) on
65 p.Object_id = sc.Object_id and sc.definition > ' '  where p.type = 'TR' and (  @ModuleToSave = 'CCC' and LEFT(p.name,4) not in (Select md.ModuleName +
66 '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave + '_' ) and p.schema_id = @Schema_id
67 ) as x create index #QBMCustomSQL1 on #QBMCustomSQL(UID_QBMCustomSQL) create index #QBMCustomSQL2 on #QBMCustomSQL(IsTooLong)  if exists (select top 1
68 1 from #QBMCustomSQL c where c.IsTooLong = 1 ) begin select top 1 @Message = 'Element name longer then 30 chars ' + c.ScriptName + '(' + c.ScriptType 
69+ ')' from #QBMCustomSQL c where c.IsTooLong = 1 exec QBM_PJournal @message, @@procid, 'E', @DebugLevel delete #QBMCustomSQL where IsTooLong = 1 end   
70  update QBMCustomSQL set xdateupdated = n.XDateupdated , XUserUpdated = @XUser from QBMCustomSQL e join #QBMCustomSQL n on e.UID_QBMCustomSQL = n.UID_QBMCustomSQL
71 where e.xdateupdated <> n.XDateupdated  update QBMCustomSQL set XTouched = 'M' , xdateupdated = n.XDateupdated  , Scriptcode = rtrim(n.Scriptcode) , XUserUpdated
72 = @XUser from QBMCustomSQL e join #QBMCustomSQL n on e.UID_QBMCustomSQL = n.UID_QBMCustomSQL where rtrim(ISNULL(e.Scriptcode, '')) <> rtrim(ISNULL(n.Scriptcode
73, ''))  insert into QBMCustomSQL (UID_QBMCustomSQL, ScriptType, ScriptName, XUserInserted , XUserUpdated , XDateInserted , XDateUpdated , Scriptcode, XObjectKey
74, XTouched) select UID_QBMCustomSQL , x.ScriptType, x.ScriptName, @XUser, @XUser, x.XdateInserted, x.XDateUpdated  , rtrim(x.ScriptCode), dbo.QBM_FCVElementToObjectKey1
75('QBMCustomSQL', 'UID_QBMCustomSQL', x.UID_QBMCustomSQL) , 'I' from #QBMCustomSQL x where not exists (select top 1 1 from QBMCustomSQL y where y.UID_QBMCustomSQL
76 = x.UID_QBMCustomSQL ) select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'QBMCustomSQL fertig ' + convert(varchar(64), getutcdate(), 121
77) print convert(nvarchar(16), @CountItems) + ' neue Sätze' end  update QBMCustomSQL set Scriptname = y.Scriptname , XDateUpdated = @Xdate , XUserUpdated
78 = @XUser from QBMCustomSQL e join #QBMCustomSQL y on e.UID_QBMCustomSQL = y.UID_QBMCustomSQL where y.ScriptName <> e.ScriptName collate SQL_Latin1_General_CP1_CS_AS
79   delete QBMCustomSQL  from QBMCustomSQL e left outer join #QBMCustomSQL y on y.UID_QBMCustomSQL = e.UID_QBMCustomSQL where y.UID_QBMCustomSQL is null
80 and left(e.UID_QBMCustomSQL, 3) = @ModuleToSave      END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH