Create proc ReCreateFK
as
declare @string varchar(1000)
declare @fkTableName varchar(100)
declare @fkcolName varchar(100)
declare @pkTablename varchar(100)
declare @pkcolName varchar(100)
---备份
SELECT
fk_name=A.NAME ,
fk_tablename=object_name(b.fkeyid)
,fk_colname=(SELECT name FROM syscolumns WHERE colid=b.fkey AND id=b.fkeyid)
,pk_tablename=object_name(b.rkeyid)
,pk_colname=(SELECT name FROM syscolumns WHERE colid=b.rkey AND id=b.rkeyid)
into #test
FROM sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' AND c.xtype='U'and object_name(b.rkeyid)='employee'
---删除
WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE TYPE='F')
BEGIN
SELECT top 1 @string='ALTER TABLE '+B.NAME+' DROP CONSTRAINT '+A.NAME
FROM (SELECT PARENT_OBJ,NAME FROM SYSOBJECTS WHERE TYPE='F') A,
(SELECT ID,NAME FROM SYSOBJECTS WHERE OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) B
WHERE A.PARENT_OBJ=B.ID
EXEC(@string)
END
---重建立
Declare Cur_ReFK Cursor Fast_forward For
select fk_tablename,fk_colname,pk_tablename,pk_colname from #test
Open Cur_ReFK
Fetch From Cur_ReFK Into @fktablename,@fkcolname,@pktablename,@pkcolname
While @@Fetch_status=0
Begin
set @string='alter table '+ @fktablename+
' add constraint FK_'+@fktablename+'_'+@pktablename+' foreign key ('+@fkcolname+')
references '+@pktablename+' ('+@pkcolname+')'
exec(@string)
Fetch From Cur_ReFK Into @fktablename,@fkcolname,@pktablename,@pkcolname
END
drop table #test
Close Cur_ReFk
Deallocate Cur_ReFK
GO