判断表存在

if exists(select * from sysobjects 
	where name= '表名'
	and type = 'U')
drop table 表名
go

临时表要用下面的方法判断

create table #test(name char(8))
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#test') and type='U')
print '#test exists'

用户表和系统表可以表下面的方法判断。

IF OBJECTPROPERTY ( object_id('authors'),'ISTABLE') = 1
print 'Authors is a table'

或

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[detail]') and OBJECTPROPERTY(id, N'IsTable') = 1)
print 'table [dbo].[detail] esist'

或

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[detail]') and (type='U' or type='S'))
print 'table [dbo].[detail] esist'