prodropforeignkeys.sql

来自「本套光盘提供了本书各章实例的所需的部分源程序文件以及数据库文件。读者 需要使用」· SQL 代码 · 共 26 行

CREATE PROCEDURE prDrop_ForeignKeys
	@chvReferencedTable VARCHAR(30)
AS
DECLARE @chvTableWithForeignKey VARCHAR(30),
	@chvForeignKey VARCHAR(30),
	@chvSQL VARCHAR(255)
DECLARE cuFKs CURSOR
FOR
SELECT tb.name,	fk.name
FROM ((sysobjects tb INNER JOIN sysreferencesr ON tb.id=r.fkeyid)
	INNER  JOIN sysobjectsfk ON r.constid=fk.id)
	INNER  JOIN sysobjectsrefd_tb ON refd_tb.id=r.rkeyid
WHERE  refd_tb.name=@chvReferencedTable
OPEN  cuFKs
FETCH  NEXT  FROM  cuFKs INTO @chvTableWithForeignKey,@chvForeignKey
WHILE (@@fetch_status<>-1)
BEGIN
SELECT	@chvSQL='ALTER TABLE'+@chvTableWithForeignKey+'DROP CONSTRAINT'+@chvForeignKey
EXEC(@chvSQL)
FETCH NEXT FROM cuFKs INTO
@chvTableWithForeignKey,@chvForeignKey
END
DEALLOCATE cuFKs
GO