Back to OIM Explorer

dbo.QBM_ZCheckDefaultZero

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 3.375 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_FGIColumnExists source text reference
references source dbo.QBM_FGIColumnExistsInSchema source text reference
references source dbo.QBM_PExecuteSQLWithRetry_LLP source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QBM_ZCheckDefaultZero(2  @SlotNumberDummy int,3  @TablePattern varchar(38),4  @Dummy2 varchar(38),5  @DummyGenProcID varchar(38)6)7AS8BEGIN9  DECLARE @SQLcmd nvarchar(max)10  DECLARE @DebugSwitch int = 011  DECLARE @ElementLast int12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  BEGIN TRY15    DECLARE @ElementBuffer_definition QBM_YCursorBuffer16    INSERT INTO @ElementBuffer_definition(ContentFull)17    SELECT18      CONCAT('sp_bindefault ''DezimalNULL'', ''',19      c.table_name,20      '.',21      c.column_name,22      '''')23    FROM information_schema.columns c24    JOIN information_schema.tables t25      ON t.table_name = c.table_name26    JOIN DialogTable tt27      ON t.TABLE_NAME = tt.TableName28    JOIN sys.tables st29      ON t.TABLE_NAME = st.name30    JOIN sys.columns sc31      ON st.object_id = sc.object_id AND sc.name = c.COLUMN_NAME32    WHERE33      t.table_type = 'BASE TABLE' AND c.data_type IN('bit',34    'int',35    'real',36    'float',37    'bigint') AND c.COLUMN_DEFAULT IS NULL AND t.TABLE_NAME LIKE @TablePattern AND t.TABLE_NAME NOT IN(38    SELECT TableName39    FROM QBM_VHeavyLoadTables) AND sc.is_identity = 040    SELECT @ElementCount = @@ROWCOUNT41    SELECT @ElementIndex = @@IDENTITY - @ElementCount +142    SELECT @ElementLast = @@IDENTITY43    WHILE @ElementIndex <= @ElementLast44    BEGIN45      SELECT TOP 1 @SQLcmd = bu.ContentFull46      FROM @ElementBuffer_definition bu47      WHERE48        bu.ElementIndex = @ElementIndex49      IF @DebugSwitch > 050      BEGIN51        print @SQLcmd52      END53      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,54        @LockTimeout_ms = DEFAULT,55        @MaxWaitTimeForLock_s = DEFAULT,56        @ProcIDForJournal = @@procid,57        @HandleErrorSilent = 058      SELECT @ElementIndex += 159    END60    DECLARE @ElementBuffer_value QBM_YCursorBuffer61    INSERT INTO @ElementBuffer_value(ContentFull)62    SELECT63      CONCAT('updat',64      'e ',65      c.table_name,66      ' set ',67      c.column_name,68      ' = 0 ',69    CASE70      WHEN dbo.QBM_FGIColumnExistsInSchema(t.TABLE_NAME, 'XDateUpdated') = 1 AND c.COLUMN_NAME NOT LIKE 'X[d,u][a,s][t,e][e,r]updated' THEN71    CONCAT('', '	--3410172								, XDateUpdated = getutcdate()73								, XUserUpdated = ''', object_name(@@procid),74    '''75								')76    ELSE ''77    END,78    ' where ',79    c.column_name,80    ' is null ')81    FROM information_schema.columns c82    JOIN information_schema.tables t83      ON t.table_name = c.table_name84    JOIN DialogTable tt85      ON t.TABLE_NAME = tt.TableName86    JOIN sys.tables st87      ON t.TABLE_NAME = st.name88    JOIN sys.columns sc89      ON st.object_id = sc.object_id AND sc.name = c.COLUMN_NAME90    WHERE91      t.table_type = 'BASE TABLE' AND c.data_type IN('bit',92    'int',93    'real',94    'float',95    'bigint') AND(c.COLUMN_DEFAULT = N '0' OR c.COLUMN_DEFAULT = N '(0)' OR c.COLUMN_DEFAULT = N '((0))' OR c.COLUMN_DEFAULT LIKE N 'create default DezimalNULL as 0%'96    ) AND t.TABLE_NAME LIKE @TablePattern AND t.TABLE_NAME NOT IN(97    SELECT TableName98    FROM QBM_VHeavyLoadTables) AND sc.is_identity = 099    SELECT @ElementCount = @@ROWCOUNT100    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1101    SELECT @ElementLast = @@IDENTITY102    WHILE @ElementIndex <= @ElementLast103    BEGIN104      SELECT TOP 1 @SQLcmd = bu.ContentFull105      FROM @ElementBuffer_value bu106      WHERE107        bu.ElementIndex = @ElementIndex108      IF @DebugSwitch > 0109      BEGIN110        print @SQLcmd111      END112      EXEC QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd,113        @LockTimeout_ms = DEFAULT,114        @MaxWaitTimeForLock_s = DEFAULT,115        @ProcIDForJournal = @@procid,116        @HandleErrorSilent = 0117      SELECT @ElementIndex += 1118    END119  END TRY120  BEGIN CATCH121    EXEC QBM_PSessionErrorAdd DEFAULT122    RAISERROR('',123    18,124    1)125      WITH NOWAIT126  END CATCH127END

Open raw exported source

1     create   procedure QBM_ZCheckDefaultZero ( @SlotNumberDummy int , @TablePattern varchar(38) , @Dummy2 varchar(38) , @DummyGenProcID varchar2(38) ) as begin declare @SQLcmd nvarchar(max) declare @DebugSwitch int = 0 declare @ElementLast int declare @ElementCount int declare @ElementIndex int3 BEGIN TRY declare @ElementBuffer_definition QBM_YCursorBuffer insert into @ElementBuffer_definition (ContentFull) select concat( 'sp_bindefault ''DezimalNULL'', '''4 , c.table_name , '.' , c.column_name , '''')  from information_schema.columns c join information_schema.tables t on t.table_name = c.table_name join DialogTable5 tt on t.TABLE_NAME = tt.TableName  join sys.tables st on t.TABLE_NAME = st.name join sys.columns sc on st.object_id = sc.object_id and sc.name = c.COLUMN_NAME6 where t.table_type = 'BASE TABLE' and c.data_type in ('bit', 'int', 'real', 'float', 'bigint') and c.COLUMN_DEFAULT is null and t.TABLE_NAME like @TablePattern7 and t.TABLE_NAME not in (select TableName from QBM_VHeavyLoadTables  ) and sc.is_identity = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex =8 @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @SQLcmd = bu.ContentFull from @ElementBuffer_definition9 bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms10 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex += 1 end   declare @ElementBuffer_value11 QBM_YCursorBuffer insert into @ElementBuffer_value (ContentFull) select concat('updat','e ' , c.table_name , ' set ' , c.column_name , ' = 0 ' , case 12when dbo.QBM_FGIColumnExistsInSchema(t.TABLE_NAME, 'XDateUpdated') = 1 and c.COLUMN_NAME not like 'X[d,u][a,s][t,e][e,r]updated' then concat('' , '	--3410113								, XDateUpdated = getutcdate()14								, XUserUpdated = '''15, object_name(@@procid) , '''16								') else '' end ,' where ' , c.column_name , ' is null ' ) from information_schema.columns c join information_schema.tables17 t on t.table_name = c.table_name join DialogTable tt on t.TABLE_NAME = tt.TableName  join sys.tables st on t.TABLE_NAME = st.name join sys.columns sc 18on st.object_id = sc.object_id and sc.name = c.COLUMN_NAME where t.table_type = 'BASE TABLE' and c.data_type in ('bit', 'int', 'real', 'float', 'bigint'19) and (  c.COLUMN_DEFAULT = N'0' or c.COLUMN_DEFAULT = N'(0)'  or c.COLUMN_DEFAULT = N'((0))' or c.COLUMN_DEFAULT like N'create default DezimalNULL as 0%'20 ) and t.TABLE_NAME like @TablePattern and t.TABLE_NAME not in (select TableName from QBM_VHeavyLoadTables  ) and sc.is_identity = 0 select @ElementCount21 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top22 1 @SQLcmd = bu.ContentFull from @ElementBuffer_value bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP23 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex24 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 25