| 解决方案： |
执行以下语句：
-- =============================================
--
-- 外键约束修改为级联更新及级联删除语句
-- 一次执行，修改所有相关表的外键为级联
--
--
-- 宁夏用友 彭桂峰
--
-- 2006-3-2
-- =============================================
-----------------------
--生成主键、外键对照表--
-----------------------
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'PK_F_REALATION'
AND type = 'U')
DROP TABLE PK_F_REALATION
select
fc_obj.name as CONSTRAINT_NAME
,i.name as UNIQUE_CONSTRAINT_NAME
into PK_F_REALATION --生成的目标主键、外键对照表
from
sysobjects fc_obj
,sysreferences r
,sysindexes i
,sysobjects pc_obj
where
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.id
and i.name ='aaaaaDepartment_PK' --如果更新其他的主键，在此修改主键名
go
-----------------------
--生成主键、外键信息表--
-----------------------
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'PK_F_INFOR'
AND type = 'U')
DROP TABLE PK_F_INFOR
GO
select
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_POSITION
into PK_F_INFOR --生成的主键、外键信息表，字段级信息
from
sysobjects c_obj
,sysobjects t_obj
,syscolumns col
,sysreferences ref
where
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.constid
union
select
i.name as CONSTRAINT_NAME
,t_obj.name as TABLE_NAME
,col.name as COLUMN_NAME
,v.number as ORDINAL_POSITION
from
sysobjects c_obj
,sysobjects t_obj
,syscolumns col
,master.dbo.spt_values v
,sysindexes i
where
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_POSITION
go
-- =============================================
-- 生成欲更新外键的信息表
-- =============================================
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'FKeyInfor'
AND type = 'U')
DROP TABLE FKeyInfor
GO
select * into FKeyInfor from PK_F_INFOR where CONSTRAINT_NAME in(select CONSTRAINT_NAME from PK_F_REALATION)
-- =============================================
-- 建立欲更新级联的游标
-- =============================================
DECLARE CASCADECUR CURSOR
READ_ONLY
FOR SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME FROM FKeyInfor
DECLARE @CONSTRAINT_NAME varchar(40)
DECLARE @TABLE_NAME varchar(40)
DECLARE @COLUMN_NAME varchar(40)
OPEN CASCADECUR
FETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAME
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @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 '
EXECUTE SP_EXECUTESQL @ADDSTRING
--取掉注释，执行以下语句，则同时更改为级联删除
--SET @DELSTRING='ALTER TABLE '+ @TABLE_NAME + ' ADD FOREIGN KEY( ' + @COLUMN_NAME + ') REFERENCES [Department] ([cDepCode]) on update cascade '
--EXECUTE SP_EXECUTESQL @ADDSTRING
PRINT '更新表[' + @TABLE_NAME + ']的外键[' + @CONSTRAINT_NAME + '],字段['+ @COLUMN_NAME +']'
END
FETCH NEXT FROM CASCADECUR INTO @CONSTRAINT_NAME,@TABLE_NAME,@COLUMN_NAME
END
CLOSE CASCADECUR
DEALLOCATE CASCADECUR
GO
print '原表的外键约束已经更新为级联更新！'
执行完成后，再修改department及相关库表即可
|