Microsoft SQL Server forum - Truncate table if exists

Author

Truncate table if exists

rdraider

2006-01-26, 4:57 pm

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

2006-01-26, 4:57 pm

The following script may work for you::

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

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

....

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

-- Damodar Periwal
Software Tree, Inc.
Simplify Data Integration
http://www.softwaretree.com

Mike

2006-01-26, 4:57 pm

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_TruncateTable
*
* 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_TruncateTable 'table01'
* exec pr_TruncateTable 'dbo.table01'

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

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

declare @SQL varchar(1500)

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

set nocount off
end

go

Sponsored Links