title: How to drop all Foreign Key constraints in all tables?
body: <p>I want to write sql command to drop all constraints in all tables. I searched on the internet and found the following which works fine if the database is small and not complex.</p>
  
  <pre><code>DECLARE @name VARCHAR(128) 
  DECLARE @constraint VARCHAR(254) 
  DECLARE @SQL VARCHAR(254) 
  DECLARE @schema VARCHAR(128)
  
  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) 
  SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name) 
  
  WHILE @name is not null 
  BEGIN 
      SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 
      WHILE @constraint IS NOT NULL 
      BEGIN 
          SELECT @SQL = 'ALTER TABLE ' + @schema + '.[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']' 
          EXEC (@SQL) 
          PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name 
          SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME &lt;&gt; @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME) 
      END 
  SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME) 
  SELECT @schema = (SELECT TOP 1 schema_name(schema_id) FROM sys.objects WHERE [name] = @name) 
  END 
  GO 
  </code></pre>
  
  <p>It does not work if I run it with a more complex database or even AdventureWork. It shows some erros like below.</p>
  
  <pre><code>Msg 3728, Level 16, State 1, Line 1
  'FK_ap_invoice_modification_type_id' is not a constraint.
  Msg 3727, Level 16, State 0, Line 1
  Could not drop constraint. See previous errors.
  Msg 3725, Level 16, State 0, Line 1
  The constraint 'PK_ap_invoice' is being referenced by table '_drop_now_ap_invoice_detail', foreign key constraint 'FK_ap_invoice_detail_ap_invoice'.
  Msg 3727, Level 16, State 0, Line 1
  Could not drop constraint. See previous errors.
  </code></pre>
  
  <p>The reason is because some FKs are referenced by other table. I have to run this script for a couple times until the database is clean.</p>
  
  <p>I want to know how can I clear all FKs in the database.</p>