用友U8 用户的部门编码长度设置太短了，不够用，修改部门编码方式用友U8 用户的部门编码长度设置太短了，不够用，修改部门编码方式
问题原因:用户的部门编码长度设置太短了，不够用，修改部门编码方式，数据库中有多处外键约束，一个一个修改太慢了 解决方法:执行以下语句：-- =============================================---- 外键约束修改为级联更新及级联删除语句-- 一次执行，修改所有相关表的外键为级联------宁夏用友 彭桂峰----2006-3-2-- =============================================-------------------------生成主键、外键对照表-------------------------IF EXISTS(SELECT name FROM sysobjects WHERE name = N'PK_F_REALATION' AND type = 'U') DROP TABLE PK_F_REALATIONselect fc_obj.name as CONSTRAINT_NAME ,i.name as UNIQUE_CONSTRAINT_NAMEinto PK_F_REALATION --生成的目标主键、外键对照表from sysobjects fc_obj ,sysreferences r ,sysindexes i ,sysobjects pc_objwhere permissions(fc_obj.parent_obj) != 0 and fc_obj.xtype = 'F' and r.constid = fc_obj.id and r.rkeyid = i.id and r.rkeyindid = i.indid and r.rkeyid = pc_obj.idand i.name ='aaaaaDepartment_PK' --如果更新其他的主键，在此修改主键名go-------------------------生成主键、外键信息表-------------------------IF EXISTS(SELECT name FROM sysobjects WHERE name = N'PK_F_INFOR' AND type = 'U') DROP TABLE PK_F_INFORGOselect c_obj.name as CONSTRAINT_NAME ,t_obj.name as TABLE_NAME ,col.name as COLUMN_NAME ,case col.colid when ref.fkey1 then 1 when ref.fkey2 then 2 when ref.fkey3 then 3 when ref.fkey4 then 4 when ref.fkey5 then 5 when ref.fkey6 then 6 when ref.fkey7 then 7 when ref.fkey8 then 8 when ref.fkey9 then 9 when ref.fkey10 then 10 when ref.fkey11 then 11 when ref.fkey12 then 12 when ref.fkey13 then 13 when ref.fkey14 then 14 when ref.fkey15 then 15 when ref.fkey16 then 16 end as ORDINAL_POSITIONinto PK_F_INFOR --生成的主键、外键信息表，字段级信息from sysobjects c_obj ,sysobjects t_obj ,syscolumns col ,sysreferences refwhere permissions(t_obj.id) != 0 and c_obj.xtype in ('F ') and t_obj.id = c_obj.parent_obj and t_obj.id = col.id and col.colid in (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6, ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12, ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16) and c_obj.id = ref.constidunion select i.name as CONSTRAINT_NAME ,t_obj.name as TABLE_NAME ,col.name as COLUMN_NAME ,v.number as ORDINAL_POSITIONfrom sysobjects c_obj ,sysobjects t_obj ,syscolumns col ,master.dbo.spt_values v ,sysindexes iwhere permissions(t_obj.id) != 0 and c_obj.xtype in ('UQ' ,'PK') and t_obj.id = c_obj.parent_obj and t_obj.xtype = 'U' and t_obj.id = col.id and col.name = index_col(t_obj.name,i.indid,v.number) and t_obj.id = i.id and c_obj.name = i.name and v.number > 0 and v.number <= i.keycnt and v.type = 'P'order by CONSTRAINT_NAME, ORDINAL_POSITIONgo-- =============================================-- 生成欲更新外键的信息表-- =============================================IF EXISTS(SELECT name FROM sysobjects WHERE name = N'FKeyInfor' AND type = 'U') DROP TABLE FKeyInforGOselect * into FKeyInfor from PK_F_INFOR where CONSTRAINT_NAME in(select CONSTRAINT_NAME from PK_F_REALATION)-- =============================================-- 建立欲更新级联的游标-- =============================================DECLARE CASCADECUR CURSORREAD_ONLYFOR SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM FKeyInforDECLARE @CONSTRAINT_NAME varchar(40)DECLARE @TABLE_NAME varchar(40)DECLARE @COLUMN_NAME varchar(40)OPEN CASCADECURFETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAMEWHILE (@@fetch_status <> -1)BEGINIF (@@fetch_status <> -2)BEGINDECLARE @REMOVESTRING NVARCHAR(500)DECLARE @ADDSTRING NVARCHAR(500)DECLARE @DELSTRING NVARCHAR(500)PRINT '更新表[' + @TABLE_NAME + ']的外键[' + @CONSTRAINT_NAME + '],字段['+ @COLUMN_NAME +']'--删除约束SET @DELSTRING='ALTER TABLE '+ @TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME EXECUTE SP_EXECUTESQL @DELSTRING --添加级联更新,如果要更新其他的表，在此修改表名和列名SET @ADDSTRING='ALTER TABLE '+ @TABLE_NAME + ' ADD FOREIGN KEY( ' + @COLUMN_NAME + ') REFERENCES [Department] ([cDepCode]) on update cascade 'EXECUTESP_EXECUTESQL @ADDSTRING --取掉注释，执行以下语句，则同时更改为级联删除--SET @DELSTRING='ALTER TABLE '+ @TABLE_NAME + ' ADD FOREIGN KEY( ' + @COLUMN_NAME + ') REFERENCES [Department] ([cDepCode]) on update cascade '--EXECUTESP_EXECUTESQL @ADDSTRING PRINT '更新表[' + @TABLE_NAME + ']的外键[' + @CONSTRAINT_NAME + '],字段['+ @COLUMN_NAME +']'ENDFETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAMEENDCLOSE CASCADECURDEALLOCATE CASCADECURGOprint '原表的外键约束已经更新为级联更新！'执行完成后，再修改department及相关库表即可
解决方案：
问题原因:用户的部门编码长度设置太短了，不够用，修改部门编码方式，数据库中有多处外键约束，一个一个修改太慢了 解决方法:执行以下语句：-- =============================================---- 外键约束修改为级联更新及级联删除语句-- 一次执行，修改所有相关表的外键为级联------宁夏用友 彭桂峰----2006-3-2-- =============================================-------------------------生成主键、外键对照表-------------------------IF EXISTS(SELECT name FROM sysobjects WHERE name = N'PK_F_REALATION' AND type = 'U') DROP TABLE PK_F_REALATIONselect fc_obj.name as CONSTRAINT_NAME ,i.name as UNIQUE_CONSTRAINT_NAMEinto PK_F_REALATION --生成的目标主键、外键对照表from sysobjects fc_obj ,sysreferences r ,sysindexes i ,sysobjects pc_objwhere permissions(fc_obj.parent_obj) != 0 and fc_obj.xtype = 'F' and r.constid = fc_obj.id and r.rkeyid = i.id and r.rkeyindid = i.indid and r.rkeyid = pc_obj.idand i.name ='aaaaaDepartment_PK' --如果更新其他的主键，在此修改主键名go-------------------------生成主键、外键信息表-------------------------IF EXISTS(SELECT name FROM sysobjects WHERE name = N'PK_F_INFOR' AND type = 'U') DROP TABLE PK_F_INFORGOselect c_obj.name as CONSTRAINT_NAME ,t_obj.name as TABLE_NAME ,col.name as COLUMN_NAME ,case col.colid when ref.fkey1 then 1 when ref.fkey2 then 2 when ref.fkey3 then 3 when ref.fkey4 then 4 when ref.fkey5 then 5 when ref.fkey6 then 6 when ref.fkey7 then 7 when ref.fkey8 then 8 when ref.fkey9 then 9 when ref.fkey10 then 10 when ref.fkey11 then 11 when ref.fkey12 then 12 when ref.fkey13 then 13 when ref.fkey14 then 14 when ref.fkey15 then 15 when ref.fkey16 then 16 end as ORDINAL_POSITIONinto PK_F_INFOR --生成的主键、外键信息表，字段级信息from sysobjects c_obj ,sysobjects t_obj ,syscolumns col ,sysreferences refwhere permissions(t_obj.id) != 0 and c_obj.xtype in ('F ') and t_obj.id = c_obj.parent_obj and t_obj.id = col.id and col.colid in (ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6, ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12, ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16) and c_obj.id = ref.constidunion select i.name as CONSTRAINT_NAME ,t_obj.name as TABLE_NAME ,col.name as COLUMN_NAME ,v.number as ORDINAL_POSITIONfrom sysobjects c_obj ,sysobjects t_obj ,syscolumns col ,master.dbo.spt_values v ,sysindexes iwhere permissions(t_obj.id) != 0 and c_obj.xtype in ('UQ' ,'PK') and t_obj.id = c_obj.parent_obj and t_obj.xtype = 'U' and t_obj.id = col.id and col.name = index_col(t_obj.name,i.indid,v.number) and t_obj.id = i.id and c_obj.name = i.name and v.number > 0 and v.number <= i.keycnt and v.type = 'P'order by CONSTRAINT_NAME, ORDINAL_POSITIONgo-- =============================================-- 生成欲更新外键的信息表-- =============================================IF EXISTS(SELECT name FROM sysobjects WHERE name = N'FKeyInfor' AND type = 'U') DROP TABLE FKeyInforGOselect * into FKeyInfor from PK_F_INFOR where CONSTRAINT_NAME in(select CONSTRAINT_NAME from PK_F_REALATION)-- =============================================-- 建立欲更新级联的游标-- =============================================DECLARE CASCADECUR CURSORREAD_ONLYFOR SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM FKeyInforDECLARE @CONSTRAINT_NAME varchar(40)DECLARE @TABLE_NAME varchar(40)DECLARE @COLUMN_NAME varchar(40)OPEN CASCADECURFETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAMEWHILE (@@fetch_status <> -1)BEGINIF (@@fetch_status <> -2)BEGINDECLARE @REMOVESTRING NVARCHAR(500)DECLARE @ADDSTRING NVARCHAR(500)DECLARE @DELSTRING NVARCHAR(500)PRINT '更新表[' + @TABLE_NAME + ']的外键[' + @CONSTRAINT_NAME + '],字段['+ @COLUMN_NAME +']'--删除约束SET @DELSTRING='ALTER TABLE '+ @TABLE_NAME + ' DROP CONSTRAINT ' + @CONSTRAINT_NAME EXECUTE SP_EXECUTESQL @DELSTRING --添加级联更新,如果要更新其他的表，在此修改表名和列名SET @ADDSTRING='ALTER TABLE '+ @TABLE_NAME + ' ADD FOREIGN KEY( ' + @COLUMN_NAME + ') REFERENCES [Department] ([cDepCode]) on update cascade 'EXECUTESP_EXECUTESQL @ADDSTRING --取掉注释，执行以下语句，则同时更改为级联删除--SET @DELSTRING='ALTER TABLE '+ @TABLE_NAME + ' ADD FOREIGN KEY( ' + @COLUMN_NAME + ') REFERENCES [Department] ([cDepCode]) on update cascade '--EXECUTESP_EXECUTESQL @ADDSTRING PRINT '更新表[' + @TABLE_NAME + ']的外键[' + @CONSTRAINT_NAME + '],字段['+ @COLUMN_NAME +']'ENDFETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAMEENDCLOSE CASCADECURDEALLOCATE CASCADECURGOprint '原表的外键约束已经更新为级联更新！'执行完成后，再修改department及相关库表即可
