SQL - How to drop table with foreign key constraints

In SQL Server the foreign key constraints must be dropped before dropping the table.

Please use the below script to find and drop all foreign key constraints of the table.


--Find and drop the constraints

DECLARE @dynamicSQL VARCHAR(MAX)
DECLARE MY_CURSOR CURSOR

LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT dynamicSQL = 'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE object_name(referenced_object_id)  in ('table1', 'table2', 'table3')
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @dynamicSQL
EXEC (@dynamicSQL)

FETCH NEXT FROM MY_CURSOR INTO @dynamicSQL
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR


-- Drop tables
DROP 'table1'
DROP 'table2'
DROP 'table3'