use database

GO

/* ----- Drop Global Temp Table ##CompaniesTables if Exist ----- */

IF OBJECT_ID('tempdb..##ConstraintsFkTable') IS NOT NULL DROP Table ##ConstraintsFkTable

/* ----- Get the List of All constraints Tables ---- */

/* ----- And insert the result into global temp table ##ConstraintsFkTable ----- */

SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable

,sc2.name MasterColumn

,object_name(fkeyid) ChildTable

,sc1.name ChildColumn

,cast (sf.keyno as int) FKOrder

into ##ConstraintsFkTable

FROM sysforeignkeys sf

INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid

INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid

ORDER BY rkeyid,fkeyid,keyno

go

declare @ConstraintName varchar (max) -- Name of the Constraint

declare @ChildTable varchar (max) -- Name of Child Table

declare @MasterTable varchar (max)--Name of Parent Table

declare @ChildColumn varchar (max)--Column of Child Table FK

declare @MasterColumn varchar (max)-- Parent Column PK

declare @FKOrder smallint -- Fk order

declare @sqlcmd varchar (max) --Dynamic Sql String

declare drop_constraints cursor

fast_forward

for

SELECT ConstraintName, MasterTable

,MasterColumn

,ChildTable

,ChildColumn

,FKOrder

FROM ##ConstraintsFkTable

ORDER BY MasterTable,ChildTable,FKOrder

open drop_constraints

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

while @@Fetch_status = 0

begin

-- Create Dynamic Sql to drop constraint

select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'

If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)

exec (@sqlcmd)

fetch next from drop_constraints

into

@ConstraintName

,@MasterTable

,@MasterColumn

,@ChildTable

,@ChildColumn

,@FKOrder

end

close drop_constraints

deallocate drop_constraints

go

--Removed CHECK Constraint-------------------------

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints

print 'All Constraints Disable'

go

------------- Truncate All Tables from Model ----------------

-----To limit tables a table with sub model tables must be created and used joins-----

EXEC sp_MSForEachTable 'truncate TABLE ? '

print 'All tables truncated'

go