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_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

.

Relevant Pages