Technical Article

Truncate All Tables Part 2

,

The script is the second part of truncating all tables.

--------------4. Restore Relationships. ON delete NO action to be
research ------------

/* Restore relationships for delete set to no action; for update
set to no action as on 

   original reverse eng. model

 */
 

declare @ChildTable varchar (max) -- Child table Name

declare @ChildColumn varchar(max)-- Child column Name 

declare @MasterTable varchar (max) -- Master TAble

declare @MasterColumn varchar (max) -- Master Column reference

declare @sqlcmd varchar (max) -- Sql Command

declare @ConstraintName varchar(max) -- Constraint Name

declare ADD_Constraint cursor 

fast_forward for

select distinct ConstraintName,ChildTable,ChildColumn,MasterTable,MasterColumn

from [CoDE].[dbo].t_FK_Xref order
by ConstraintName

open ADD_Constraint

fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

      

while @@Fetch_Status = 0

begin

begin try

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

exec (@sqlcmd)

 fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

end try

begin catch 

print @sqlcmd+'
***** Error checkpoint '

fetch next from ADD_Constraint

into @ConstraintName,

       @ChildTable,

       @ChildColumn,

       @MasterTable,

     @MasterColumn

end catch

end

close ADD_Constraint

Deallocate ADD_Constraint

go

---------------5. Restore CHECK Constraints---------------

 -- Now enable referential integrity again

--EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

 

print 'Constraints Restored'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating