重建库中所有表外键

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