The script is the second part of truncating all tables.
2009-01-23 (first published: 2009-01-14)
1,903 reads
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'