去除外鍵的存儲過程

堆碼狂人

1. create proce prdropforeignkeys
   
2. @chvreferencedtable varchar(30)
   
3. as
   
4. declare @chvtablewithforeignkey varchar(30),
   
5.             @chvforeignkey varchar(30),
   
6.             @chvsql varchar(255)
   
7. declare cufks cursor for
   
8. select  tb.name,fk.name from
   
9. ((sysobject tb inner join sysreferences r on tb.id=r.fkeyid)
   
10.   inner join sysobjects fk on r.constid=fk.id)
    
11.   inner join sysobjects refd on refd_tb.id=r.rkeyid)
    
12. where refd_tb.name=@chvreferencedtable
    
13. open cufks
    
14. fetch next from cufks into @chvtablewithforeignkey,@chvforeignkey
    
15. 
    
16. while (@@fetch_status<>-1)
    
17. begin
    
18.   select @chvsql='alter table'+@chvtablewithforeignkey+
    
19.     'drop constraint '+@chvforeignkey
    
20.   exec(@chvsql)
    
21. fetch next from cufks into @chvtablewithforeignkey,@chvforeignkey
    
22. end
    
23. deallocate cufks

復(fù)制代碼