Truncate Table with Foreign Key constraint

Truncate Table with Foreign Key constraint

This post helps you in automating the process of Truncate Table with Foreign Key constraint”. We recently working for a client on data migration project. There was a requirement for truncating tables. But we had to face issues as all those tables are having foreign keys. So the requirement is :

  • Everytime we’ll send you a list of tables to be truncated
  • First collect all constraint details from those tables
  • Drop all foreign keys
  • Truncate tables
  • Recreate all constraints
  • If in-case of any failure the script should not fail or through any error message instead it should continue with the next object  and failed objects / constraints should be printed

We built the below stored procedure to fulfill the client requirement and it worked perfectly as expected.

Stored Procedure Creation to Truncate Table with Foreign Key constraint:

 

Example:

EXEC USP_Truncate_Tabs ”’DEPT”,”EMP_ADD”,”EMP”,”Item”,”Sales”’

 

Output is Something like below

Posted in SQL Development, SQL Scripts, SQL Server DBA | Tagged , , , , | 1 Comment

Leave a Reply

1 Comment on "Truncate Table with Foreign Key constraint"

Notify of
avatar
Sort by:   newest | oldest | most voted
jojo
Guest

Although slick, your procedure does not handle multiple primary key constraints…

wpDiscuz