金蝶云星空批量修复种子

广告：

--查询种子表异常
DECLARE @tableName VARCHAR(255);
DECLARE @tableName1 VARCHAR(255);
DECLARE @primaryKey VARCHAR(255);
DECLARE @id bigINT;
DECLARE @id1 bigINT;
DECLARE @type VARCHAR(255);
DECLARE My_Cursor1 CURSOR --定义游标
FOR
 (SELECT NAME
 FROM sysobjects
 WHERE xtype = 'U'
  AND ( NAME LIKE 'Z_%' )) --查出需要的集合放到游标中
 
OPEN My_Cursor1; --打开游标
 
FETCH NEXT FROM My_Cursor1 INTO @tableName; --读取第一行数据
 
WHILE @@FETCH_STATUS = 0
 BEGIN 
 
 
 SET @id1 = 0;
 
 SELECT @tableName1 = 'T'
    + Substring(@tableName, 2, Len(@tableName)-1);
 
 IF Object_id(@tableName1, 'U') IS NOT NULL
 BEGIN
  SET @primaryKey =NULL;
 
  SELECT @primaryKey = COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_NAME = @tableName1
 
  IF @primaryKey IS NOT NULL
  BEGIN
   SELECT @type = b.NAME
   FROM syscolumns a,
    systypes b
   WHERE a.id = Object_id(@tableName1)
    AND a.xtype = b.xtype
    AND a.NAME = @primaryKey
 
   IF @type = 'int'
   OR @type = 'bigint'
   BEGIN
   DECLARE @sql NVARCHAR(1000);
 
   SET @sql = 'select @a = isnull(max(' + @primaryKey
     + '),0) from ' + @tablename1
 
   EXEC Sp_executesql
    @sql,
    N'@a bigint output',
    @id1 output--将exec的结果放入变量中的做法
						 SELECT @id = Ident_current(@tableName); 
   IF( @id < @id1 )
    PRINT @tablename + ':' + CONVERT(VARCHAR(8000), @id)
    + ',' + @tablename1 + ':'
    + CONVERT(VARCHAR(8000), @id1)
   END
  END
 END;
 
 SELECT @tableName1 = 'T'
    + Substring(@tableName, 2, Len(@tableName)-1)
    + '_H';
 
 IF Object_id(@tableName1, 'U') IS NOT NULL
 BEGIN
  SET @primaryKey =NULL;
 
  SELECT @primaryKey = COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_NAME = @tableName1
 
  IF @primaryKey IS NOT NULL
  BEGIN
   SELECT @type = b.NAME
   FROM syscolumns a,
    systypes b
   WHERE a.id = Object_id(@tableName1)
    AND a.xtype = b.xtype
    AND a.NAME = @primaryKey
 
   IF @type = 'int'
   OR @type = 'bigint'
   BEGIN
   DECLARE @sql1 NVARCHAR(1000);
 
   SET @sql1 = 'select @a = isnull(max(' + @primaryKey
     + '),0) from ' + @tablename1
 
   EXEC Sp_executesql
    @sql1,
    N'@a bigint output',
    @id1 output--将exec的结果放入变量中的做法
						 SELECT @id = Ident_current(@tableName); 
   IF( @id < @id1 )
    PRINT @tablename + ':' + CONVERT(VARCHAR(8000), @id)
    + ',' + @tablename1 + ':'
    + CONVERT(VARCHAR(8000), @id1)
   END
  END
 END;
 
 FETCH NEXT FROM My_Cursor1 INTO @tableName; --读取下一行数据
 END
 
CLOSE My_Cursor1; --关闭游标
 
DEALLOCATE My_Cursor1; --释放游标
 
 
 
--修复种子表
 
 
DECLARE @tableName varchar(255);
DECLARE @tableName1 varchar(255);
declare @primaryKey varchar(255);
declare @id int;
declare @id1 int;
DECLARE My_Cursor1 CURSOR --定义游标
FOR (select name from sysobjects where xtype='U' and (name like 'Z_%')) --查出需要的集合放到游标中
OPEN My_Cursor1; --打开游标
FETCH NEXT FROM My_Cursor1 into @tableName ; --读取第一行数据
 
WHILE @@FETCH_STATUS = 0
 BEGIN
	 print '-----------------------------------------------------------------------------------------------------------------------------------------------------';
	 print '----------------------------------------------------------start handler :'+@tableName +'-------------------------------------------------------------';
	 print '-----------------------------------------------------------------------------------------------------------------------------------------------------';
	 print '种子表:'+@tableName;
 select @id = IDENT_CURRENT(@tableName); -- 获取种子表的当前主键值
	 print '种子表的当前主键值:'
	 print @id;
	 set @id1 = 0;
	 select @tableName1 = 'T'+ substring(@tableName,2,len(@tableName)-1);
	 print '业务表对象及ID值:'+@tableName1
	 print OBJECT_ID(@tableName1, 'U'); -- 返回业务表对象的ID值
	 IF OBJECT_ID(@tableName1, 'U') IS NOT NULL --说明这个表存在
	 print '--------------------------------------------处理业务表:'+@tableName1+'-----------------------------------------------------------';
		BEGIN
		 set @primaryKey =null;
		 SELECT @primaryKey = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName1 --获取业务表的主键名称
		 print '业务表的主键名称:'+ @primaryKey;
		 if @primaryKey is not null
			 begin
				 declare @sql NVARCHAR(1000);
				 set @sql = 'select @a = isnull(max('+@primaryKey+'),0) from ' + @tablename1 --获取业务表的主键值，构建sql
				 print '构建获取业务表的主键值sql:'+@sql;
				 BEGIN TRY
					 exec sp_executesql @sql,N'@a int output',@id1 output--将主键值放入id1中
					 print '业务表的最新主键值:'
					 print @id1
					 if(@id<@id1) --
						 begin
						 print @tablename +':'+Convert(varchar(8000),@id) +','+@tablename1 +':'+Convert(varchar(8000),@id1);
						 dbcc checkident(@tablename,reseed,@id1) --更新种子表主键值为最新
						 print '更新种子表主键值为最新业务表的主键值'
						 end
 
				 END TRY
				 BEGIN CATCH
				 print 'error is:'+ERROR_MESSAGE()
					 --SELECT 'There was an error! ' + ERROR_MESSAGE()
					 ---ProcErrorLog --调用上面的存储过程,保存错误日志
 
				 END CATCH;
  end
		END;
	 select @tableName1 = 'T'+ substring(@tableName,2,len(@tableName)-1)+'_H';
	 print '业务表对象及ID值_H:'+@tableName1;
 print OBJECT_ID(@tableName1, 'U'); -- 返回业务表对象的ID值
 IF OBJECT_ID(@tableName1, 'U') IS NOT NULL
	 print '--------------------------------------------处理H业务表:'+@tableName1+'-----------------------------------------------------------';
		BEGIN
		 set @primaryKey =null;
		 SELECT @primaryKey = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName1
		 print '_H业务表的主键名称::'+ @primaryKey;
		 if @primaryKey is not null
			 begin
				 declare @sql1 NVARCHAR(1000);
		 		 set @sql1 = 'select @a = isnull(max('+@primaryKey+'),0) from ' + @tablename1
				 print '构建获取_H业务表的主键值sql::'+@sql;
				 BEGIN TRY
					 exec sp_executesql @sql1,N'@a int output',@id1 output--将exec的结果放入变量中的做法
					 print '业务表_H的最新主键值:'
					 print @id1
					 if(@id<@id1)
						 begin
						 print @tablename +':'+Convert(varchar(8000),@id) +','+@tablename1 +':'+Convert(varchar(8000),@id1);
						 dbcc checkident(@tablename,reseed,@id1) --更新种子表主键值为最新
						 print '更新种子表主键值为最新业务表的主键值'
						 end
				 END TRY
				 BEGIN CATCH
				 print 'error is:'+ERROR_MESSAGE()
					 --SELECT 'There was an error! ' + ERROR_MESSAGE()
					 ---ProcErrorLog --调用上面的存储过程,保存错误日志
				 END CATCH;
			 end
		END;
 
	 FETCH NEXT FROM My_Cursor1 INTO @tableName; --读取下一行数据
 END
	CLOSE My_Cursor1; --关闭游标
DEALLOCATE My_Cursor1; --释放游标
 
 

广告：