金蝶云星空批量修復(fù)種子
發(fā)布時間：2025/3/3 9:52:13 作者：Admin 閱讀：9
廣告：
--查詢種子表異常
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; --讀取第一行數(shù)據(jù)
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的結(jié)果放入變量中的做法
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的結(jié)果放入變量中的做法
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; --讀取下一行數(shù)據(jù)
END
CLOSE My_Cursor1; --關(guān)閉游標
DEALLOCATE My_Cursor1; --釋放游標
--修復(fù)種子表
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 ; --讀取第一行數(shù)據(jù)
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 '業(yè)務(wù)表對象及ID值:'+@tableName1
print OBJECT_ID(@tableName1, 'U'); -- 返回業(yè)務(wù)表對象的ID值
IF OBJECT_ID(@tableName1, 'U') IS NOT NULL --說明這個表存在
print '--------------------------------------------處理業(yè)務(wù)表:'+@tableName1+'-----------------------------------------------------------';
BEGIN
set @primaryKey =null;
SELECT @primaryKey = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName1 --獲取業(yè)務(wù)表的主鍵名稱
print '業(yè)務(wù)表的主鍵名稱:'+ @primaryKey;
if @primaryKey is not null
begin
declare @sql NVARCHAR(1000);
set @sql = 'select @a = isnull(max('+@primaryKey+'),0) from ' + @tablename1 --獲取業(yè)務(wù)表的主鍵值，構(gòu)建sql
print '構(gòu)建獲取業(yè)務(wù)表的主鍵值sql:'+@sql;
BEGIN TRY
exec sp_executesql @sql,N'@a int output',@id1 output--將主鍵值放入id1中
print '業(yè)務(wù)表的最新主鍵值:'
print @id1
if(@id<@id1) --
begin
print @tablename +':'+Convert(varchar(8000),@id) +','+@tablename1 +':'+Convert(varchar(8000),@id1);
dbcc checkident(@tablename,reseed,@id1) --更新種子表主鍵值為最新
print '更新種子表主鍵值為最新業(yè)務(wù)表的主鍵值'
end
END TRY
BEGIN CATCH
print 'error is:'+ERROR_MESSAGE()
--SELECT 'There was an error! ' + ERROR_MESSAGE()
---ProcErrorLog --調(diào)用上面的存儲過程,保存錯誤日志
END CATCH;
end
END;
select @tableName1 = 'T'+ substring(@tableName,2,len(@tableName)-1)+'_H';
print '業(yè)務(wù)表對象及ID值_H:'+@tableName1;
print OBJECT_ID(@tableName1, 'U'); -- 返回業(yè)務(wù)表對象的ID值
IF OBJECT_ID(@tableName1, 'U') IS NOT NULL
print '--------------------------------------------處理H業(yè)務(wù)表:'+@tableName1+'-----------------------------------------------------------';
BEGIN
set @primaryKey =null;
SELECT @primaryKey = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=@tableName1
print '_H業(yè)務(wù)表的主鍵名稱::'+ @primaryKey;
if @primaryKey is not null
begin
declare @sql1 NVARCHAR(1000);
set @sql1 = 'select @a = isnull(max('+@primaryKey+'),0) from ' + @tablename1
print '構(gòu)建獲取_H業(yè)務(wù)表的主鍵值sql::'+@sql;
BEGIN TRY
exec sp_executesql @sql1,N'@a int output',@id1 output--將exec的結(jié)果放入變量中的做法
print '業(yè)務(wù)表_H的最新主鍵值:'
print @id1
if(@id<@id1)
begin
print @tablename +':'+Convert(varchar(8000),@id) +','+@tablename1 +':'+Convert(varchar(8000),@id1);
dbcc checkident(@tablename,reseed,@id1) --更新種子表主鍵值為最新
print '更新種子表主鍵值為最新業(yè)務(wù)表的主鍵值'
end
END TRY
BEGIN CATCH
print 'error is:'+ERROR_MESSAGE()
--SELECT 'There was an error! ' + ERROR_MESSAGE()
---ProcErrorLog --調(diào)用上面的存儲過程,保存錯誤日志
END CATCH;
end
END;
FETCH NEXT FROM My_Cursor1 INTO @tableName; --讀取下一行數(shù)據(jù)
END
CLOSE My_Cursor1; --關(guān)閉游標
DEALLOCATE My_Cursor1; --釋放游標
廣告：
相關(guān)文章
