Truncate Table generic script

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  June 19, 2008 at 10:53 am

  #208598

  Is there any way to develop a generic Truncate table script for all the tables in the database based on foreign key relationships

  for example Employee table is parent of the EmployeeSal and EmployeeBenefits, HR table is HRDivision and HRManager

  My truncate statement order should be as follows I have tried using sysobjects and sysforeignkeys to develop a generic script but it didnt work out.

  TRUNCATE TABLE EMPLOYEE

  TRUNCATE TABLE EMPLOYEESAL

  TRUNCATE TABLE EMPLOYEEBENEFITS

  TRUNCATE TABLE HR

  TRUNCATE TABLE HRDIVISION

  TRUNCATE TABLE HRMANAGER

  Is this even possible to develop a generic script for all the tables in the database.

  Thanks

• KenpoDBA

  SSCrazy Eights

  Points: 9811

  More actions

  June 19, 2008 at 10:58 am

  #831250

  Can you truncate tables with relationships? I thought you had to delete.

  Watch my free SQL Server Tutorials at:
  http://MidnightDBA.com
  Blog Author of:
  DBA Rant – http://www.MidnightDBA.com/DBARant

  Minion Maintenance is FREE:
  
  

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  June 19, 2008 at 11:02 am

  #831254

  Yes, the TRUNCATE TABLE command will fail if the table has a foreign key referencing it. You must either drop the FK or use the DELETE statement.

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 19, 2008 at 11:02 am

  #831255

  If you really want to truncate all the tables in a database, you're probably better off using a create script to drop the whole database and rebuild it.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• MannySingh

  SSChampion

  Points: 12465

  More actions

  June 19, 2008 at 11:03 am

  #831256

  Create a Script first to drop the required contraints and then

  Truncate table and reapply the constraints.

  OR

  Script all tables using Delete, which will work even with constraints in place.

  But Remember, if the Tables are huge, DELETE is a Logged operation and will write every row in the LOG File.

  My perspective the Better Way is Drop/Truncate/Recreate is yoyur tables are BIG.

  Maninder
  www.dbanation.com