How to Drop All Relations in A Database

If you're new here, you may want to subscribe to my RSS feed or become a fan of this blog on Facebook. Thanks for visiting!

this is related to my earlier post on dropping all indexes from an sql database but this one is to delete all foreign key relations from a database.

set nocount on
 declare @statements cursor
 set @statements = cursor static for
 select  'alter table ' + quotename(ctu.table_schema) + '.' + quotename(ctu.table_name) +
         ' drop constraint ' + quotename(cc.constraint_name)
 from  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as cc
          join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE as ctu
           on cc.constraint_catalog = ctu.constraint_catalog
              and cc.constraint_schema = ctu.constraint_schema
              and cc.constraint_name = ctu.constraint_name

 open @statements
 declare @statement nvarchar(1000)
 While  (1=1)
  begin
         fetch from @statements into @statement
                if @@fetch_status <> 0
                 break
                exec (@statement)
         end

Hope this was Helpful!

Hatim