Truncate table with an EXISTS

• RD-201664

  Ten Centuries

  Points: 1334

  More actions

  January 25, 2006 at 7:01 pm

  #90520

  Hi,

  I am trying to create a script that deletes transaction tables and leaves

  master data like customer, vendors, inventory items, etc.  How can I use

  TRUNCATE TABLE with an EXISTS or anyone have a better way?  I can use DELETE FROM but it is soooo slow. 

  SQL 2000

  My problem is I have 200+ tables, if I simply use a list like:

  truncate table table01

  truncate table table02

  truncate table table03

  ...

  I get errors if the table does not exist and have to manually run the

  truncate statements.  Some tables may not exist if that part of the app is

  never used.  I'm trying to make a list of all tables that could exist

  without it erroring out 50+ times.

  Thanks

• jordanac

  Hall of Fame

  Points: 3653

  More actions

  January 25, 2006 at 11:03 pm

  #617078

  Hi

  You can use this

  if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table01]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  truncate table [dbo].[table01]

  Tip

  In EM highlight all of the tables and generate a script that also has drop table. Paste in Query Analyser and replace the drop with truncate. This is just a fast way of creating a script like you need.

   

   

   

  
  Andy.

• RD-201664

  Ten Centuries

  Points: 1334

  More actions

  January 26, 2006 at 7:40 pm

  #617353

  Thanks, I ended up using this:

  IF OBJECT_ID('table02')  IS NOT NULL TRUNCATE TABLE table2