Back to OIM Explorer

dbo.QBM_PCustomSQLFill

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 12.601 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVElementToObjectKey1 source text reference
references source dbo.QBM_FCVStringToGUID source text reference
references source dbo.QBM_FSQColumAdd source text reference
references source dbo.QBM_FSQIndexDef source text reference
references source dbo.QBM_FSQTableDef source text reference
references source dbo.QBM_FSQTableDefCond source text reference
references source dbo.QBM_PJournal source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

1CREATE PROCEDURE QBM_PCustomSQLFill(2  @GenProcID varchar(38) = NULL,3  @XUserExtern nvarchar(64) = NULL,4  @ModuleToSave varchar(3) = 'CCC'5)6AS7BEGIN8  DECLARE @Schema_Id int9  DECLARE @XUser nvarchar(64) = object_name(@@procid)10  DECLARE @Xdate datetime = getutcdate()11  DECLARE @DebugSwitch int = 012  DECLARE @DebugLevel varchar(1) = 'W'13  DECLARE @CountItems int14  DECLARE @Message nvarchar(1000)15  SET XACT_ABORT OFF16  BEGIN TRY17    IF @XUserExtern > ' '18    BEGIN19      SELECT @XUser = @XUserExtern20    END21    SELECT TOP 1 @Schema_Id = schema_id22    FROM sys.schemas23    WHERE24      name = N 'dbo'25    IF @GenProcID IS NULL26    BEGIN27      SELECT @GenProcID = NEWID()28    END29    IF @DebugSwitch > 030    BEGIN31      print 'Start ' + convert(varchar(64),32      getutcdate(),33      121)34    END35    SELECT @CountItems = 136    WHILE @CountItems > 037    BEGIN38      DELETE QBMCustomSQL39      WHERE40        uid_QBMCustomSQL IN(41      SELECT max(uid_QBMCustomSQL)42      FROM QBMCustomSQL43      GROUP BY scripttype,44      scriptname45      HAVING count(*) > 1)46      SELECT @CountItems = @@ROWCOUNT47    END48    drop TABLE49    IF EXISTS #QBMCustomSQL50    CREATE TABLE #QBMCustomSQL(51      UID_QBMCustomSQL varchar(38) collate database_default NOT NULL,52      ScriptType nvarchar(1) collate database_default NULL,53      XDateInserted datetime NULL,54      XDateupdated datetime NULL,55      ScriptName nvarchar(255) collate database_default NULL,56      ScriptCode nvarchar(max) collate database_default NULL,57      IsTooLong BIT DEFAULT 058    ) drop TABLE59    IF EXISTS #ModuleNameMitMinus60    CREATE TABLE #ModuleNameMitMinus(61      Modulename4 varchar(4) collate database_default62    )63    INSERT INTO #ModuleNameMitMinus(Modulename464  )65  SELECT md.ModuleName + '-'66  FROM QBMModuleDef md67    WITH(readpast68)69WHERE70  md.ModuleName <> 'CCC'71CREATE index #ModuleNameMitMinus172  ON #ModuleNameMitMinus(73  Modulename474)75INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,76ScriptCode,77scriptname,78scripttype,79Xdateinserted,80Xdateupdated,81IsTooLong82)83SELECT84  dbo.QBM_FCVStringToGUID(@ModuleToSave,85  x.Scripttype + x.Scriptname86  ),87  dbo.QBM_FSQTableDefCond(x.Scriptname88  ),89  x.Scriptname,90  x.Scripttype,91  x.Xdateinserted,92  x.Xdateupdated,93  x.IsTooLong94FROM(95SELECT96  convert(nvarchar(64),97  so.name)98  AS99  Scriptname,100  N 'U' AS ScriptType,101  so.create_date AS Xdateinserted,102  so.modify_date AS Xdateupdated,103CASE104  WHEN len(so.name) > 30 THEN1051106ELSE 0107END AS IsTooLong108FROM sys.tables so109  WITH(readpast)110JOIN DialogTable t111  WITH(readpast, forceseek)112  ON convert(varchar(30),113LEFT(so.name, 30)) = t.TableName114LEFT115OUTER116JOIN #ModuleNameMitMinus mm117  ON118LEFT(t.UID_DialogTable, 4) = mm.Modulename4119WHERE120  so.type = 'U' AND so.is_memory_optimized = 0 AND so.schema_id = @Schema_id AND(@ModuleToSave = 'CCC' AND mm.Modulename4 IS NULL OR @ModuleToSave121<> 'CCC' AND mm.Modulename4 > ' ')) AS x122INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,123ScriptCode,124scriptname,125scripttype,126Xdateinserted,127Xdateupdated,128IsTooLong)129SELECT130  dbo.QBM_FCVStringToGUID(@ModuleToSave,131  x.Scripttype + x.Scriptname),132  dbo.QBM_FSQColumAdd(x.ScriptName),133  x.Scriptname,134  x.Scripttype,135  x.Xdateinserted,136  x.Xdateupdated,137  x.IsTooLong138FROM(139SELECT140  convert(nvarchar(64), t.name) + N '.' + convert(nvarchar(64), c.name) AS ScriptName,141  N 'C' AS ScriptType,142  t.create_date AS Xdateinserted,143  t.modify_date AS Xdateupdated,144CASE145  WHEN len(t.name) > 30 OR len(c.name) > 30 THEN1461147ELSE 0148END AS IsTooLong149FROM sys.tables t150  WITH(readpast)151JOIN sys.columns c152  WITH(readpast)153  ON t.object_id = c.object_id AND t.type = N 'U' AND t.is_memory_optimized = 0154JOIN DialogTable dt155  WITH(readpast, forceseek)156  ON convert(varchar(30),157LEFT(t.name, 30)) = dt.TableName158JOIN DialogColumn dc159  WITH(readpast, forceseek)160  ON dt.UID_DialogTable = dc.UID_DialogTable AND convert(varchar(30),161LEFT(c.name, 30)) = dc.columnname162LEFT163OUTER164JOIN #ModuleNameMitMinus mm165  ON166LEFT(dc.UID_DialogColumn, 4) = mm.Modulename4167WHERE168  (@ModuleToSave = 'CCC' AND mm.Modulename4 IS NULL OR @ModuleToSave <> 'CCC' AND mm.Modulename4 > ' ') AND t.schema_id = @Schema_id) AS x169INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,170ScriptCode,171scriptname,172scripttype,173Xdateinserted,174Xdateupdated,175IsTooLong)176SELECT177  dbo.QBM_FCVStringToGUID(@ModuleToSave,178  x.Scripttype + x.Scriptname),179  x.IndexDef,180  x.Scriptname,181  x.Scripttype,182  x.Xdateinserted,183  x.Xdateupdated,184  x.IsTooLong185FROM(186SELECT187  convert(nvarchar(64), t.name) + N '.' + convert(nvarchar(64), i.name) AS ScriptName,188  N 'I' AS ScriptType,189  t.create_date AS Xdateinserted,190  t.modify_date AS XdateUpdated,191CASE192  WHEN len(t.name) > 30 OR len(i.name) > 30 THEN1931194ELSE 0195END AS IsTooLong,196dbo.QBM_FSQIndexDef(t.name, i.name) AS IndexDef197FROM sys.tables t198  WITH(readpast)199JOIN sys.indexes i200  WITH(readpast)201  ON t.Object_id = i.Object_id AND t.type = N 'U' AND t.is_memory_optimized = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,2024, 5, 6)203JOIN DialogTable dt204  WITH(readpast, forceseek)205  ON convert(varchar(30),206LEFT(t.name, 30)) = dt.TableName207LEFT208OUTER209JOIN QBM_VQBMModuleDef md210  ON convert(varchar(3),211LEFT(i.name, 3)) = md.ModuleName AND md.ModuleName <> 'CCC'212WHERE213  (@ModuleToSave = 'CCC' AND md.ModuleName IS NULL OR @ModuleToSave <> 'CCC' AND214LEFT(i.name, 3) = @ModuleToSave) AND i.is_primary_key = 0 AND i.is_hypothetical = 0 AND i.name NOT LIKE 'missing[_]index[_]%') AS x215INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,216ScriptCode,217scriptname,218scripttype,219Xdateinserted,220Xdateupdated,221IsTooLong)222SELECT223  dbo.QBM_FCVStringToGUID(@ModuleToSave,224  x.Scripttype + x.Scriptname),225  x.Scriptcode,226  x.Scriptname,227  x.Scripttype,228  x.Xdateinserted,229  x.Xdateupdated,230  x.IsTooLong231FROM(232SELECT233  convert(nvarchar(64), p.name) AS Scriptname,234  N 'P' AS ScriptType,235  p.create_date AS Xdateinserted,236  p.modify_date AS XdateUpdated,237  sc.definition AS Scriptcode,238CASE239  WHEN len(p.name) > 30 THEN2401241ELSE 0242END AS IsTooLong243FROM sys.objects p244  WITH(readpast)245JOIN sys.sql_modules sc246  WITH(readpast)247  ON p.Object_id = sc.Object_id AND sc.definition > ' '248WHERE249  p.type = N 'P' AND250LEFT(p.name, 3) NOT IN(N 'sp_', N 'ms_', N 'dt_') AND(@ModuleToSave = 'CCC' AND251LEFT(p.name, 4) NOT IN(252SELECT md.ModuleName + '_'253FROM QBM_VQBMModuleDef md254WHERE255  md.ModuleName <> 'CCC') OR @ModuleToSave <> 'CCC' AND256LEFT(p.name, 4) = @ModuleToSave + '_') AND p.schema_id = @Schema_id) AS x257INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,258ScriptCode,259scriptname,260scripttype,261Xdateinserted,262Xdateupdated,263IsTooLong)264SELECT265  dbo.QBM_FCVStringToGUID(@ModuleToSave,266  x.Scripttype + x.Scriptname),267  x.Scriptcode,268  x.Scriptname,269  x.Scripttype,270  x.Xdateinserted,271  x.Xdateupdated,272  x.IsTooLong273FROM(274SELECT275  p.name AS Scriptname,276  N 'V' AS ScriptType,277  p.create_date AS Xdateinserted,278  p.modify_date AS XdateUpdated,279  sc.definition AS Scriptcode,280CASE281  WHEN len(p.name) > 30 THEN2821283ELSE 0284END AS IsTooLong285FROM sys.objects p286  WITH(readpast)287JOIN sys.sql_modules sc288  WITH(readpast)289  ON p.Object_id = sc.Object_id AND sc.definition > ' '290WHERE291  p.type = N 'V' AND NOT EXISTS(292SELECT TOP 1 1293FROM DialogTable t294  WITH(readpast, forceseek)295WHERE296  t.tabletype IN('V', 'P', 'U', 'R') AND t.TableName = convert(varchar(30),297LEFT(p.name, 30))) AND p.name NOT LIKE 'sys%' AND p.name NOT LIKE 'ms%' AND p.name NOT LIKE 'IH%' AND p.name NOT LIKE 'conflict%' AND(@ModuleToSave298= 'CCC' AND299LEFT(p.name, 4) NOT IN(300SELECT md.ModuleName + '_'301FROM QBM_VQBMModuleDef md302WHERE303  md.ModuleName <> 'CCC') OR @ModuleToSave <> 'CCC' AND304LEFT(p.name, 4) = @ModuleToSave + '_') AND p.schema_id = @Schema_id AND p.name NOT IN('QBMBufferTransfer',305'AccProductInMirrorShop', 'MirrorShopHasPWODecisionMethod', 'QBMDBQueueOverview_fix', 'QBMDBQueueSlot',306'QBMDBQueueSlot_fix', 'QBMDBQueueTaskPerf_fix', 'QBMVBlobInternal')) AS x307INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,308ScriptCode,309scriptname,310scripttype,311Xdateinserted,312Xdateupdated,313IsTooLong)314SELECT315  dbo.QBM_FCVStringToGUID(@ModuleToSave,316  x.Scripttype + x.Scriptname),317  x.Scriptcode,318  x.Scriptname,319  x.Scripttype,320  x.Xdateinserted,321  x.Xdateupdated,322  x.IsTooLong323FROM(324SELECT325  p.name AS Scriptname,326  N 'F' AS ScriptType,327  p.create_date AS Xdateinserted,328  p.modify_date AS XdateUpdated,329  sc.definition AS Scriptcode,330CASE331  WHEN len(p.name) > 30 THEN3321333ELSE 0334END AS IsTooLong335FROM sys.objects p336  WITH(readpast)337JOIN sys.sql_modules sc338  WITH(readpast)339  ON p.Object_id = sc.Object_id AND sc.definition > ' '340WHERE341  p.type = N 'FN' AND(@ModuleToSave = 'CCC' AND342LEFT(p.name, 4) NOT IN(343SELECT md.ModuleName + '_'344FROM QBM_VQBMModuleDef md345WHERE346  md.ModuleName <> 'CCC') OR @ModuleToSave <> 'CCC' AND347LEFT(p.name, 4) = @ModuleToSave + '_') AND p.schema_id = @Schema_id AND p.name NOT IN('fn_diagramobjects',348'vid_ErwinC')) AS x349INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,350ScriptCode,351scriptname,352scripttype,353Xdateinserted,354Xdateupdated,355IsTooLong)356SELECT357  dbo.QBM_FCVStringToGUID(@ModuleToSave,358  x.Scripttype + x.Scriptname),359  x.Scriptcode,360  x.Scriptname,361  x.Scripttype,362  x.Xdateinserted,363  x.Xdateupdated,364  x.IsTooLong365FROM(366SELECT367  p.name AS Scriptname,368  N 'N' AS ScriptType,369  p.create_date AS Xdateinserted,370  p.modify_date AS XdateUpdated,371  sc.definition AS Scriptcode,372CASE373  WHEN len(p.name) > 30 THEN3741375ELSE 0376END AS IsTooLong377FROM sys.objects p378  WITH(readpast)379JOIN sys.sql_modules sc380  WITH(readpast)381  ON p.Object_id = sc.Object_id AND sc.definition > ' '382WHERE383  p.type IN(N 'TF', N 'IF') AND(@ModuleToSave = 'CCC' AND384LEFT(p.name, 4) NOT IN(385SELECT md.ModuleName + '_'386FROM QBM_VQBMModuleDef md387WHERE388  md.ModuleName <> 'CCC') OR @ModuleToSave <> 'CCC' AND389LEFT(p.name, 4) = @ModuleToSave + '_') AND p.schema_id = @Schema_id) AS x390INSERT INTO #QBMCustomSQL(UID_QBMCustomSQL,391ScriptCode,392scriptname,393scripttype,394Xdateinserted,395Xdateupdated,396IsTooLong)397SELECT398  dbo.QBM_FCVStringToGUID(@ModuleToSave,399  x.Scripttype + x.Scriptname),400  x.Scriptcode,401  x.Scriptname,402  x.Scripttype,403  x.Xdateinserted,404  x.Xdateupdated,405  x.IsTooLong406FROM(407SELECT408  p.name AS Scriptname,409  N 'T' AS ScriptType,410  p.create_date AS Xdateinserted,411  p.modify_date AS XdateUpdated,412  sc.definition AS Scriptcode,413CASE414  WHEN len(p.name) > 30 THEN4151416ELSE 0417END AS IsTooLong418FROM sys.objects p419  WITH(readpast)420JOIN sys.sql_modules sc421  WITH(readpast)422  ON p.Object_id = sc.Object_id AND sc.definition > ' '423WHERE424  p.type = 'TR' AND(@ModuleToSave = 'CCC' AND425LEFT(p.name, 4) NOT IN(426SELECT md.ModuleName + '_'427FROM QBM_VQBMModuleDef md428WHERE429  md.ModuleName <> 'CCC') OR @ModuleToSave <> 'CCC' AND430LEFT(p.name, 4) = @ModuleToSave + '_') AND p.schema_id = @Schema_id) AS x431CREATE index #QBMCustomSQL1432  ON #QBMCustomSQL(433  UID_QBMCustomSQL434)435CREATE index #QBMCustomSQL2436  ON #QBMCustomSQL(437  IsTooLong438)439IF EXISTS(440  SELECT TOP 1 1441  FROM #QBMCustomSQL c442  WHERE443    c.IsTooLong = 1444)445BEGIN446  SELECT447    TOP 1 @Message = 'Element name longer then 30 chars ' + c.ScriptName + '(' + c.ScriptType + ')'448  FROM #QBMCustomSQL c449  WHERE450    c.IsTooLong = 1451  EXEC QBM_PJournal @message,452    @@procid,453  'E',454    @DebugLevel455  DELETE #QBMCustomSQL456  WHERE457    IsTooLong = 1458END459UPDATE QBMCustomSQL460SET xdateupdated = n.XDateupdated,461XUserUpdated = @XUser462FROM QBMCustomSQL e463JOIN #QBMCustomSQL n464  ON e.UID_QBMCustomSQL = n.UID_QBMCustomSQL465WHERE466  e.xdateupdated <> n.XDateupdated467UPDATE QBMCustomSQL468SET XTouched = 'M',469xdateupdated = n.XDateupdated,470Scriptcode = rtrim(n.Scriptcode471),472XUserUpdated = @XUser473FROM QBMCustomSQL e474JOIN #QBMCustomSQL n475  ON e.UID_QBMCustomSQL = n.UID_QBMCustomSQL476WHERE477  rtrim(ISNULL(e.Scriptcode,478'')479) <> rtrim(ISNULL(n.Scriptcode,480'')481)482INSERT INTO QBMCustomSQL(UID_QBMCustomSQL,483ScriptType,484ScriptName,485XUserInserted,486XUserUpdated,487XDateInserted,488XDateUpdated,489Scriptcode,490XObjectKey,491XTouched492)493SELECT494  UID_QBMCustomSQL,495  x.ScriptType,496  x.ScriptName,497  @XUser,498  @XUser,499  x.XdateInserted,500  x.XDateUpdated,501  rtrim(x.ScriptCode502  ),503  dbo.QBM_FCVElementToObjectKey1('QBMCustomSQL',504  'UID_QBMCustomSQL',505  x.UID_QBMCustomSQL506  ),507  'I'508FROM #QBMCustomSQL x509WHERE510  NOT EXISTS(511SELECT TOP 1 1512FROM QBMCustomSQL y513WHERE514  y.UID_QBMCustomSQL = x.UID_QBMCustomSQL515)516SELECT @CountItems = @@ROWCOUNT517IF @DebugSwitch > 0518BEGIN519  print 'QBMCustomSQL fertig ' + convert(varchar(64),520  getutcdate(),521  121522) print convert(nvarchar(16),523@CountItems524) + ' neue Sätze'525END526UPDATE QBMCustomSQL527SET Scriptname = y.Scriptname,528XDateUpdated = @Xdate,529XUserUpdated = @XUser530FROM QBMCustomSQL e531JOIN #QBMCustomSQL y532  ON e.UID_QBMCustomSQL = y.UID_QBMCustomSQL533WHERE534  y.ScriptName <> e.ScriptName collate SQL_Latin1_General_CP1_CS_AS535DELETE QBMCustomSQL536FROM QBMCustomSQL e537LEFT538OUTER539JOIN #QBMCustomSQL y540  ON y.UID_QBMCustomSQL = e.UID_QBMCustomSQL541WHERE542  y.UID_QBMCustomSQL IS NULL AND543LEFT(e.UID_QBMCustomSQL,5443545) = @ModuleToSave546END TRY547BEGIN CATCH548  EXEC QBM_PSessionErrorAdd DEFAULT549  RAISERROR('',550  18,551  1552)553  WITH NOWAIT554END CATCH555ende: truncate TABLE #QBMCustomSQL556RETURN557END

Open raw exported source

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 @GenProcID5 = NEWID() end  if @DebugSwitch > 0 begin print 'Start ' + convert(varchar(64), getutcdate(), 121) end select @CountItems = 1 while @CountItems > 0 begin6 delete QBMCustomSQL where uid_QBMCustomSQL in (select max(uid_QBMCustomSQL)  from QBMCustomSQL group by scripttype, scriptname having count(*) > 1 ) select7 @CountItems = @@ROWCOUNT end  drop table if exists #QBMCustomSQL create table #QBMCustomSQL ( UID_QBMCustomSQL varchar(38) collate database_default not8 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 create10 table #ModuleNameMitMinus (Modulename4 varchar(4) collate database_default) insert into #ModuleNameMitMinus(Modulename4) Select md.ModuleName + '-' from11 QBMModuleDef md with (readpast) where md.ModuleName <> 'CCC' create index #ModuleNameMitMinus1 on #ModuleNameMitMinus (Modulename4)  insert into #QBMCustomSQL12 (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype13 + x.Scriptname) , dbo.QBM_FSQTableDefCond(x.Scriptname) , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert14(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.name16,30)) = t.TableName left outer join #ModuleNameMitMinus mm on left(t.UID_DialogTable, 4) = mm.Modulename4 where so.type = 'U' and so.is_memory_optimized17 = 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_FCVStringToGUID19(@ModuleToSave, x.Scripttype + x.Scriptname) , dbo.QBM_FSQColumAdd(x.ScriptName) , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong20 from ( select convert(nvarchar(64), t.name) + N'.' + convert(nvarchar(64), c.name) as ScriptName , N'C' as ScriptType , t.create_date as Xdateinserted21, 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, forceseek23) 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 where25          ( @ModuleToSave = 'CCC' and mm.Modulename4 is null or @ModuleToSave <> 'CCC' and mm.Modulename4 > ' ' ) and t.schema_id = @Schema_id ) as x insert26 into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave27, x.Scripttype + x.Scriptname) , x.IndexDef , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert(nvarchar(6428), 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_VQBMModuleDef32 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_FCVStringToGUID35(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted, x.Xdateupdated , x.IsTooLong from ( select convert36(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.name39,4) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave40 + '_' ) and p.schema_id = @Schema_id ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated41, 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.definition43 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) on44 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) where45 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%' and46 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_VQBMModuleDef47 md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave + '_' ) and p.schema_id = @Schema_id  and p.name not in48 ( 'QBMBufferTransfer' , 'AccProductInMirrorShop' , 'MirrorShopHasPWODecisionMethod' , 'QBMDBQueueOverview_fix' , 'QBMDBQueueSlot' , 'QBMDBQueueSlot_fix'49 , 'QBMDBQueueTaskPerf_fix' , 'QBMVBlobInternal' ) ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted50, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname, x.Scripttype, x.Xdateinserted51, 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 with53 (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, scriptname56, scripttype, Xdateinserted, Xdateupdated, IsTooLong ) select dbo.QBM_FCVStringToGUID(@ModuleToSave, x.Scripttype + x.Scriptname) , x.Scriptcode , x.Scriptname57, 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_date58 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_modules59 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,460) not in (Select md.ModuleName + '_' from QBM_VQBMModuleDef md where md.ModuleName <> 'CCC' ) or @ModuleToSave <> 'CCC' and left(p.name, 4) = @ModuleToSave61 + '_' ) and p.schema_id = @Schema_id ) as x insert into #QBMCustomSQL (UID_QBMCustomSQL, ScriptCode, scriptname, scripttype, Xdateinserted, Xdateupdated62, 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.definition64 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) on65 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_id67 ) as x create index #QBMCustomSQL1 on #QBMCustomSQL(UID_QBMCustomSQL) create index #QBMCustomSQL2 on #QBMCustomSQL(IsTooLong)  if exists (select top 168 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_QBMCustomSQL71 where e.xdateupdated <> n.XDateupdated  update QBMCustomSQL set XTouched = 'M' , xdateupdated = n.XDateupdated  , Scriptcode = rtrim(n.Scriptcode) , XUserUpdated72 = @XUser from QBMCustomSQL e join #QBMCustomSQL n on e.UID_QBMCustomSQL = n.UID_QBMCustomSQL where rtrim(ISNULL(e.Scriptcode, '')) <> rtrim(ISNULL(n.Scriptcode73, ''))  insert into QBMCustomSQL (UID_QBMCustomSQL, ScriptType, ScriptName, XUserInserted , XUserUpdated , XDateInserted , XDateUpdated , Scriptcode, XObjectKey74, XTouched) select UID_QBMCustomSQL , x.ScriptType, x.ScriptName, @XUser, @XUser, x.XdateInserted, x.XDateUpdated  , rtrim(x.ScriptCode), dbo.QBM_FCVElementToObjectKey175('QBMCustomSQL', 'UID_QBMCustomSQL', x.UID_QBMCustomSQL) , 'I' from #QBMCustomSQL x where not exists (select top 1 1 from QBMCustomSQL y where y.UID_QBMCustomSQL76 = x.UID_QBMCustomSQL ) select @CountItems = @@ROWCOUNT if @DebugSwitch > 0 begin print 'QBMCustomSQL fertig ' + convert(varchar(64), getutcdate(), 12177) print convert(nvarchar(16), @CountItems) + ' neue Sätze' end  update QBMCustomSQL set Scriptname = y.Scriptname , XDateUpdated = @Xdate , XUserUpdated78 = @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_AS79   delete QBMCustomSQL  from QBMCustomSQL e left outer join #QBMCustomSQL y on y.UID_QBMCustomSQL = e.UID_QBMCustomSQL where y.UID_QBMCustomSQL is null80 and left(e.UID_QBMCustomSQL, 3) = @ModuleToSave      END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH81 ende: truncate table #QBMCustomSQL return end 82