存货核算，产品成本核算种子表批量修复
使用场景：数据中毒，搬迁，损坏等操作导致系统种子表被破坏，成本核算结账时出现报错提示表的主键重复的提示，可使用脚本批量修复种子表
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_HS%' or NAME LIKE 'Z_CB%')) --查出需要的集合放到游标中
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 )
begin dbcc checkident (@tablename,reseed,@id1);
PRINT @tablename + ':' + CONVERT(VARCHAR(8000), @id)
+ ',' + @tablename1 + ':'
+ CONVERT(VARCHAR(8000), @id1);
end;
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 )
begin dbcc checkident (@tablename,reseed,@id1);
PRINT @tablename + ':' + CONVERT(VARCHAR(8000), @id)
+ ',' + @tablename1 + ':'
+ CONVERT(VARCHAR(8000), @id1)
end;
END
END
END;
FETCH NEXT FROM My_Cursor1 INTO @tableName; --读取下一行数据
END
CLOSE My_Cursor1; --关闭游标
DEALLOCATE My_Cursor1; --释放游标
存货核算，产品成本核算种子表批量修复
使用场景：数据中毒，搬迁，损坏等操作导致系统种子表被破坏，成本核算结账时出现报错提示表的主键重复的提示，可使用脚本批量修复种子表DE...
点击下载文档
本文2024-09-16 17:13:39发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-14349.html
相关文章
您需要登录后才可以发表评论， 登录登录 或者 注册
最新文档
热门文章
