Drop foreign keys

Sometimes you need to drop a table.  Sometimes that table doesn't want to be dropped.  It fights you like a two-year-old who was just told no more ice cream after they polished off the entire half gallon.  It's ugly.

Usually when this happens to me it's caused by foreign key references, either the table being dropped is referencing another table or another table is referencing the table to be dropped (yes, those are different things).  The script below should create a script to drop all foreign keys referencing the table to be dropped as well as all foreign keys in the table to be dropped.

I've only used this a couple of times and it worked like a charm.  You should note, however, that those constraints are probably there for a reason so you should seriously consider what you're doing before you arbitrarily drop them.

I got a large part of this from a StackOverflow answer here.

DECLARE @TARGET AS VARCHAR(10); -- SET to 'DROP' or 'CREATE' 
DECLARE @TARGET_TABLE AS SYSNAME; -- TABLE WHOSE FOREIGN-KEY WILL BE SCRIPTED 
DECLARE @TARGET_SCHEMA AS SYSNAME; -- SCHEMA OF THE TABLE 
SET @TARGET = 'DROP'; 
SET @TARGET_SCHEMA = 'dbo'; 
SET @TARGET_TABLE = 'TableToDrop'; 

-- Other variables 
DECLARE @schema_name SYSNAME; 
DECLARE @table_name SYSNAME; 
DECLARE @constraint_name SYSNAME; 
DECLARE @constraint_object_id INT; 
DECLARE @referenced_object_name SYSNAME; 
DECLARE @is_disabled BIT; 
DECLARE @is_not_for_replication BIT; 
DECLARE @is_not_trusted BIT; 
DECLARE @delete_referential_action TINYINT; 
DECLARE @update_referential_action TINYINT; 
DECLARE @tsql NVARCHAR(4000); 
DECLARE @tsql2 NVARCHAR(4000); 
DECLARE @fkCol SYSNAME; 
DECLARE @pkCol SYSNAME; 
DECLARE @col1 BIT; 
DECLARE fkcursor CURSOR FOR 
  (SELECT Object_schema_name(parent_object_id), 
          Object_name(parent_object_id), 
          name, 
          Object_name(referenced_object_id), 
          object_id, 
          is_disabled, 
          is_not_for_replication, 
          is_not_trusted, 
          delete_referential_action, 
          update_referential_action 
   FROM   sys.foreign_keys 
   WHERE  Object_name(parent_object_id) = @TARGET_TABLE 
          AND Object_schema_name(parent_object_id) = @TARGET_SCHEMA) 
  UNION ALL 
  (SELECT Object_schema_name(parent_object_id), 
          Object_name(parent_object_id), 
          name, 
          Object_name(referenced_object_id), 
          object_id, 
          is_disabled, 
          is_not_for_replication, 
          is_not_trusted, 
          delete_referential_action, 
          update_referential_action 
   FROM   sys.foreign_keys 
   WHERE  Object_name(referenced_object_id) = @TARGET_TABLE 
          AND Object_schema_name(parent_object_id) = @TARGET_SCHEMA) 
  ORDER  BY 1, 
            2; 

OPEN fkcursor; 

FETCH next FROM fkcursor INTO @schema_name, @table_name, @constraint_name, 
@referenced_object_name, @constraint_object_id, @is_disabled, 
@is_not_for_replication, @is_not_trusted, @delete_referential_action, 
@update_referential_action; 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      IF @TARGET <> 'CREATE' 
        SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.' 
                    + Quotename(@table_name) 
                    + ' DROP CONSTRAINT ' 
                    + Quotename(@constraint_name) + ';'; 
      ELSE 
        BEGIN 
            SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.' 
                        + Quotename(@table_name) + CASE @is_not_trusted WHEN 0 
                        THEN 
                        ' WITH CHECK ' ELSE ' WITH NOCHECK ' END 
                        + ' ADD CONSTRAINT ' 
                        + Quotename(@constraint_name) 
                        + ' FOREIGN KEY (' 
            SET @tsql2 = ''; 

            DECLARE columncursor CURSOR FOR 
              SELECT Col_name(fk.parent_object_id, fkc.parent_column_id), 
                     Col_name(fk.referenced_object_id, fkc.referenced_column_id) 
              FROM   sys.foreign_keys fk 
                     INNER JOIN sys.foreign_key_columns fkc 
                             ON fk.object_id = fkc.constraint_object_id 
              WHERE  fkc.constraint_object_id = @constraint_object_id 
              ORDER  BY fkc.constraint_column_id; 

            OPEN columncursor; 

            SET @col1 = 1; 

            FETCH next FROM columncursor INTO @fkCol, @pkCol; 

            WHILE @@FETCH_STATUS = 0 
              BEGIN 
                  IF ( @col1 = 1 ) 
                    SET @col1 = 0 
                  ELSE 
                    BEGIN 
                        SET @tsql = @tsql + ','; 
                        SET @tsql2 = @tsql2 + ','; 
                    END; 

                  SET @tsql = @tsql + Quotename(@fkCol); 
                  SET @tsql2 = @tsql2 + Quotename(@pkCol); 

                  FETCH next FROM columncursor INTO @fkCol, @pkCol; 
              END; 

            CLOSE columncursor; 

            DEALLOCATE columncursor; 

            SET @tsql = @tsql + ' ) REFERENCES ' 
                        + Quotename(@schema_name) + '.' 
                        + Quotename(@referenced_object_name) + ' (' 
                        + @tsql2 + ')'; 
            SET @tsql = @tsql + ' ON UPDATE ' + CASE @update_referential_action 
                        WHEN 0 
                        THEN 'NO ACTION ' 
                        WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE 
                        'SET DEFAULT ' 
                        END + ' ON DELETE ' + CASE @delete_referential_action 
                        WHEN 
                        0 
                        THEN 
                        'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 
                        'SET NULL ' 
                        ELSE 
                        'SET DEFAULT ' END + CASE @is_not_for_replication WHEN 1 
                        THEN 
                        ' NOT FOR REPLICATION ' ELSE '' END 
                        + ';'; 
        END; 

      PRINT @tsql; 

      IF @TARGET = 'CREATE' 
        BEGIN 
            SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.' 
                        + Quotename(@table_name) + CASE @is_disabled WHEN 0 THEN 
                        ' CHECK ' 
                        ELSE ' NOCHECK ' END + 'CONSTRAINT ' 
                        + Quotename(@constraint_name) + ';'; 

            PRINT @tsql; 
        END; 

      FETCH next FROM fkcursor INTO @schema_name, @table_name, @constraint_name, 
      @referenced_object_name, @constraint_object_id, @is_disabled, 
      @is_not_for_replication, @is_not_trusted, @delete_referential_action, 
      @update_referential_action; 
  END; 

CLOSE fkcursor; 

DEALLOCATE fkcursor;