Create procedure to drop foreign key : Utility Procedure « Procedure Function « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Procedure Function
3. Utility Procedure

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