Truncate table if exists

[rdraider]

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? My problem is I have 200+ tables, if I
simply use a list like:
truncate table01
truncate table02
truncate 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 in advance.

[dperiwal@softwaretree.com]

Re: Truncate table if exists

The following script may work for you::

--------------------------------------
IF OBJECT_ID('tabl e01') IS NOT NULL
TRUNCATE TABLE table1

IF OBJECT_ID('tabl e02') IS NOT NULL
TRUNCATE TABLE table2

....

IF OBJECT_ID('tabl eN') IS NOT NULL
TRUNCATE TABLE tableN
--------------------------------------

-- Damodar Periwal
Software Tree, Inc.
Simplify Data Integration

http://www.softwaretree.com

[Mike]

Re: Truncate table if exists

Checking for the object_id is indeed the way to go.

I once needed a quite similar thing and wrote this into a stored
procedure. Something like this:

/*************** *************** *************** *************** *************** ************
* Procedure: pr_TruncateTabl e
*
* Purpose:
* Truncates the table with the specified name. This is actually
nothing more
* than a truncate table which checks first if the table exists.
*
* Input: Table Name
*
* Examples:
* exec pr_TruncateTabl e 'table01'
* exec pr_TruncateTabl e 'dbo.table01'

*************** *************** *************** *************** *************** ************/

create procedure pr_TruncateTabl e
(
@Table varchar(250)
)
as
begin
set nocount on

declare @SQL varchar(1500)

if exists ( select *
from [dbo].[sysobjects]
where [id] = object_id(@Tabl e)
and objectproperty([id], N'IsUserTable') = 1 )
begin
set @SQL = 'truncate table ' + @Table
exec (@SQL)
end

set nocount off
end

go