SQL DROP TABLE foreign key constraint

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]

No, this will not drop your table if there are indeed foreign keys referencing it.

To get all foreign key relationships referencing your table, you could use this SQL (if you're on SQL Server 2005 and up):

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

and if there are any, with this statement here, you could create SQL statements to actually drop those FK relations:

SELECT 
	'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']'
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('Student')

In SQL Server Management Studio 2008 (R2) and newer, you can Right Click on the

> DB -> Tasks -> Generate Scripts

• Select the tables you want to DROP.

• Select "Save to new query window".

• Click on the Advanced button.

• Set Script DROP and CREATE to Script DROP.

• Set Script Foreign Keys to True.

• Click OK.

• Click Next -> Next -> Finish.

• View the script and then Execute.

If you drop the "child" table first, the foreign key will be dropped as well. If you attempt to drop the "parent" table first, you will get an "Could not drop object 'a' because it is referenced by a FOREIGN KEY constraint." error.

Here is another way to drop all tables correctly, using sp_MSdropconstraints procedure. The shortest code I could think of:

exec sp_MSforeachtable "declare @name nvarchar(max); set @name = parsename('?', 1); exec sp_MSdropconstraints @name";
exec sp_MSforeachtable "drop table ?";

If it is SQL Server you must drop the constraint before you can drop the table.

Slightly more generic version of what @mark_s posted, this helped me

SELECT 
'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(k.parent_object_id) +
'.[' + OBJECT_NAME(k.parent_object_id) + 
'] DROP CONSTRAINT ' + k.name
FROM sys.foreign_keys k
WHERE referenced_object_id = object_id('your table')

just plug your table name, and execute the result of it.

Here's another way to do delete all the constraints followed by the tables themselves, using a concatenation trick involving FOR XML PATH('') which allows merging multiple input rows into a single output row. Should work on anything SQL 2005 & later.

I've left the EXECUTE commands commented out for safety.

DECLARE @SQL NVARCHAR(max)
;WITH fkeys AS (
	SELECT quotename(s.name) + '.' + quotename(o.name) tablename, quotename(fk.name) constraintname 
	FROM sys.foreign_keys fk
	JOIN sys.objects o ON fk.parent_object_id = o.object_id
	JOIN sys.schemas s ON o.schema_id = s.schema_id
)
SELECT @SQL = STUFF((SELECT '; ALTER TABLE ' + tablename + ' DROP CONSTRAINT ' + constraintname
FROM fkeys
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

SELECT @SQL = STUFF((SELECT '; DROP TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
FOR XML PATH('')),1,2,'')

-- EXECUTE(@sql)

Here is a complete script to implement a solution:

create Procedure [dev].DeleteTablesFromSchema
(
	@schemaName varchar(500)
)
As 
begin
	declare @constraintSchemaName nvarchar(128), @constraintTableName nvarchar(128),  @constraintName nvarchar(128)
	declare @sql nvarchar(max)
	-- delete FK first
	declare cur1 cursor for
	select distinct 
	CASE WHEN t2.[object_id] is NOT NULL  THEN	s2.name ELSE s.name END as SchemaName,
	CASE WHEN t2.[object_id] is NOT NULL  THEN	t2.name ELSE t.name END as TableName,
	CASE WHEN t2.[object_id] is NOT NULL  THEN  OBJECT_NAME(d2.constraint_object_id) ELSE OBJECT_NAME(d.constraint_object_id) END as ConstraintName
	from sys.objects t 
		inner join sys.schemas s 
			on t.[schema_id] = s.[schema_id]
		left join sys.foreign_key_columns d 
			on 	d.parent_object_id = t.[object_id]
		left join sys.foreign_key_columns d2 
			on 	d2.referenced_object_id = t.[object_id]
		inner join sys.objects t2 
			on 	d2.parent_object_id = t2.[object_id]
		inner join sys.schemas s2 
			on 	t2.[schema_id] = s2.[schema_id]
	WHERE t.[type]='U' 
	    AND t2.[type]='U'
		AND t.is_ms_shipped = 0 
		AND t2.is_ms_shipped = 0 
	    AND s.Name=@schemaName
	open cur1
	fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
	while @@fetch_status = 0
	BEGIN
		set @sql ='ALTER TABLE ' + @constraintSchemaName + '.' + @constraintTableName+' DROP CONSTRAINT '+@constraintName+';'
		exec(@sql)
		fetch next from cur1 into @constraintSchemaName, @constraintTableName, @constraintName
	END
	close cur1
	deallocate cur1

	DECLARE @tableName nvarchar(128)
	declare cur2 cursor for
	select s.Name, p.Name
	from sys.objects p
		INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
	WHERE p.[type]='U' and is_ms_shipped = 0 
	AND s.Name=@schemaName
	ORDER BY s.Name, p.Name
	open cur2
	
	fetch next from cur2 into @schemaName,@tableName
	while @@fetch_status = 0
	begin
		set @sql ='DROP TABLE ' + @schemaName + '.' + @tableName
		exec(@sql)
		fetch next from cur2 into @schemaName,@tableName
	end

	close cur2
	deallocate cur2

end
go

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.

If you are on a mysql server (not MSSQL) and if you don't mind loosing your tables, you can use a simple query to delete multiple tables at once:

SET foreign_key_checks = 0;
DROP TABLE IF EXISTS table_a,table_b,table_c,table_etc;
SET foreign_key_checks = 1;

In this way it doesn't matter in what order you use the table in you query.

If anybody is going to say something about the fact that this is not a good solution if you have a database with many tables: I agree!

If you want to DROP a table which has been referenced by other table using the foreign key use

DROP TABLE *table_name* CASCADE CONSTRAINTS;
I think it should work for you.

> If I want to delete all the tables in > my database

Then it's a lot easier to drop the entire database:

DROP DATABASE WorkerPensions