Technical Article

Code For Truncating Table with foreign Key Constraints

,

This can be used for data warehouses as well as truncating table with foreign key constraints.

Use <DATABASE>
Go
/*
Created By Vimal Lohani
*/Declare @data nvarchar(max)
-- RECREATE CONSTRAINTS
SELECT ROW_NUMBER() over(order by f.object_id) 'rno', 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +
' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'
+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,
fc.referenced_column_id)+')' as Scripts
into #tempcreate
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

-- DROP CONSTRAINTS table 
SELECT ROW_NUMBER() over(order by f.object_id) 'rowno','ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']' as dropscript
into #tempdrop
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
--select * from #tempdrop
--Drop Constraints process
declare @max int=(Select max(rowno) from #tempdrop)
while(@max>0)
Begin
Select  @data=dropscript from #tempdrop where rowno=@max
Exec(@data) 
--PRINT @data
Set @max=@max-1
End

--Type your all Truncate Code Here

truncate table [dbo].[fact_Employee]



--Complete Truncate Code


--Create Constraints Process
declare @maxx int=(Select max(rno) from #tempcreate)
while(@maxx>0)
Begin
Select  @data=Scripts from #tempcreate where rno=@maxx
Exec(@data) 
--PRINT @data
Set @maxx=@maxx-1
End

drop table #tempdrop
drop table #tempcreate

Rate

3.5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (4)

You rated this post out of 5. Change rating