Removing all Foreign keys from MSSQL

Today I had some problems with removing tables.
To clear all test data from a database we can use the TRUNCATE method to cleanup the table. Other solution is to clear all the foreign keys and then drop the database.


My database contains tables and a schema. When we are developing we want to reset our database. So we use a sql script where we drop all the tables of a schema and then the database itself.


When I run the query I got this error message:


fk is not a constraint. could not drop constraint. see previous errors

And after some changes in my SQL:


could not drop object because it is referenced by a foreign key constraint


So why you wont delete. The table order seems to be right but it doesnt work?
We can get all the constraints from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.


For example you see here the Northwind database:





Ok, we can remove each constraint easy by adding in our SQL a DROP CONSTRAINT for each constraint. However, not easy when the database grows.


An other easy solution is check in the database, if found, we create a query and executes the query. Our solution will be:

WHILE(exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
BEGIN
 DECLARE @sql nvarchar(2000)
 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
 FROM information_schema.table_constraints
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
 EXEC (@sql)
END

In my case I want to remove only constraints from the given schemaname, so add an extra WHERE for TABLE_SCHEMA and we are done!