SQL DROP TABLE foreign key constraint

Removing Referenced FOREIGN KEY Constraints
Assuming there is a parent and child table Relationship in SQL Server:

--First find the name of the Foreign Key Constraint:
  SELECT * 
  FROM sys.foreign_keys
  WHERE referenced_object_id = object_id('States')

--Then Find foreign keys referencing to dbo.Parent(States) table:
   SELECT name AS 'Foreign Key Constraint Name', 
           OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id) AS 'Child Table'
   FROM sys.foreign_keys 
   WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
              OBJECT_NAME(referenced_object_id) = 'dbo.State'

 -- Drop the foreign key constraint by its name 
   ALTER TABLE dbo.cities DROP CONSTRAINT FK__cities__state__6442E2C9;

 -- You can also use the following T-SQL script to automatically find 
 --and drop all foreign key constraints referencing to the specified parent 
 -- table:

 BEGIN

DECLARE @stmt VARCHAR(300);

-- Cursor to generate ALTER TABLE DROP CONSTRAINT statements  
 DECLARE cur CURSOR FOR
 SELECT 'ALTER TABLE ' + OBJECT_SCHEMA_NAME(parent_object_id) + '.' + 
 OBJECT_NAME(parent_object_id) +
                ' DROP CONSTRAINT ' + name
 FROM sys.foreign_keys 
 WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo' AND 
            OBJECT_NAME(referenced_object_id) = 'states';

 OPEN cur;
 FETCH cur INTO @stmt;

 -- Drop each found foreign key constraint 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC (@stmt);
    FETCH cur INTO @stmt;
  END

  CLOSE cur;
  DEALLOCATE cur;

  END
  GO

--Now you can drop the parent table:

 DROP TABLE states;
--# Command(s) completed successfully.

everything is much simpler. There is a configuration to turn off the check and turn it on.

For example, if you are using MySQL, then to turn it off, you must write SET foreign_key_checks = 0;

Then delete or clear the table, and re-enable the check SET foreign_key_checks = 1;

Using SQL Server Manager you can drop foreign key constraints from the UI. If you want to delete the table Diary but the User table has a foreign key DiaryId pointing to the Diary table, you can expand (using the plus symbol) the User table and then expand the Foreign Keys section. Right click on the foreign key that points to the diary table then select Delete. You can then expand the Columns section, right click and delete the column DiaryId too. Then you can just run:

drop table Diary

I know your actual question is about deleting all tables, so this may not be a useful for that case. However, if you just want to delete a few tables this is useful I believe (the title does not explicitly mention deleting all tables).

execute the below code to get the foreign key constraint name which blocks your drop. For example, I take the roles table.

      SELECT *
      FROM sys.foreign_keys
      WHERE referenced_object_id = object_id('roles');

      SELECT name AS 'Foreign Key Constraint Name',
      OBJECT_SCHEMA_NAME(parent_object_id) + '.' + OBJECT_NAME(parent_object_id)
      AS 'Child Table' FROM sys.foreign_keys
      WHERE OBJECT_SCHEMA_NAME(referenced_object_id) = 'dbo'
      AND OBJECT_NAME(referenced_object_id) = 'dbo.roles'

you will get the FK name something as below : FK__Table1__roleId__1X1H55C1

now run the below code to remove the FK reference got from above.

ALTER TABLE dbo.users drop CONSTRAINT FK__Table1__roleId__1X1H55C1;

Done!

Find all foreign key.. script them

SELECT *  FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

and then delete foreign keys from child table. now you can drop parent table .

if you want to recreate the parent table make sure to run the script you have created earlier.

This script will let you delete all Foreign Key Contrainsts using a string match on the Contraint name

    DECLARE @constraintMatchString nvarchar(max) = N'FK_<SOME-MATCH-STRING>%';
    DECLARE @sql nvarchar(max) = N'';

    ;WITH x (schemaAndTableName, constraintName) AS 
    (
      SELECT 
        schemaAndTableName = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)),
        constraintName = name
      FROM sys.foreign_keys
      where name like @constraintMatchString
      group by QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)), name
    )
    SELECT @sql += N'ALTER TABLE ' + schemaAndTableName + N' DROP CONSTRAINT [' +constraintName +N'];
    ' FROM x;

    EXEC sys.sp_executesql @sql;

Just replace the FK_<SOME-MATCH-STRING>% value as needed (and note, the % is a wildcard match)