dbo.QBM_ZCheckDefaultZero

1     create   procedure QBM_ZCheckDefaultZero ( @SlotNumberDummy int , @TablePattern varchar(38) , @Dummy2 varchar(38) , @DummyGenProcID varchar
2(38) ) as begin declare @SQLcmd nvarchar(max) declare @DebugSwitch int = 0 declare @ElementLast int declare @ElementCount int declare @ElementIndex int
3 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 DialogTable
5 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_NAME
6 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 @TablePattern
7 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_definition
9 bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLcmd , @LockTimeout_ms
10 = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex += 1 end   declare @ElementBuffer_value
11 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('' , '	--34101
13								, 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.tables
17 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 @ElementCount
21 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top
22 1 @SQLcmd = bu.ContentFull from @ElementBuffer_value bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print @SQLcmd end exec QBM_PExecuteSQLWithRetry_LLP
23 @SQLStatement = @SQLcmd , @LockTimeout_ms = default , @MaxWaitTimeForLock_s = default , @ProcIDForJournal = @@procid , @HandleErrorSilent = 0 select @ElementIndex
24 += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
25