|
4>
5>
6>
7> CREATE PROCEDURE prDropForeignKeys
8> @chvReferencedTable VARCHAR(30)
9> AS
10> DECLARE @chvTableWithForeignKey VARCHAR(30),
11> @chvForeignKey VARCHAR(30),
12> @chvSQL VARCHAR(255)
13> DECLARE cuFKs CURSOR
14> FOR
15> SELECT tb.name,
16> fk.name
17> FROM ((sysobjects tb INNER JOIN sysreferences r ON tb.id = r.fkeyid)
18> INNER JOIN sysobjects fk ON r.constid = fk.id)
19> INNER JOIN sysobjects refd_tb ON refd_tb.id = r.rkeyid
20> WHERE refd_tb.name = @chvReferencedTable
21> OPEN cuFKs
22> FETCH NEXT FROM cuFKs INTO
23> @chvTableWithForeignKey, @chvForeignKey
24> WHILE (@@fetch_status <> -1)
25> BEGIN
26> SELECT @chvSQL = 'ALTER TABLE ' + @chvTableWithForeignKey
27> + ' DROP CONSTRAINT ' + @chvForeignKey
28> EXEC (@chvSQL)
29> FETCH NEXT FROM cuFKs INTO
30> @chvTableWithForeignKey, @chvForeignKey
31> END
32> DEALLOCATE cuFKs
33> GO
1>
2> drop PROCEDURE prDropForeignKeys;
3> GO
|