[转]SQL判断临时表是否存在

判断临时表是否存在

Way 1

if(exists(select name from tempdb..sysobjects where name like'%temptab%' and type='U'))
   drop table #temptab

Way 2

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#tempcitys') and type='U')
   drop table #tempcitys

Way 3

IF OBJECT_ID('tempdb..#') IS NOT NULL
   DROP TABLE #

OBJECT_ID此函数返回数据库对象标识号

判断数据库里有没有存在PerPersonData这样一张表

if exists (select * from sysobjects where objectproperty(object_id('PerPersonData'),'istable') = 1)

OBJECTPROPERTY：返回当前数据库中对象的有关信息。1表“真”。同样可以写成OBJECTPROPERTY(id, isUserTable) = 1

if exists (select * from sysobjects where id = object_id(N'PerPersonData') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table 'PerPersonData'

判断试图是否存在
if exists (select * from sysobjects where id = object_id(N‘[dbo].[ESTMP]‘)
and OBJECTPROPERTY(id, N‘IsView‘) = 1)
  drop view ESTMP

案例：

/* 
查询非优质案源
N'tempdb..#tempcitys'
If   Object_Id( N'tempdb..#temptest')   Is   Not   NULL--#Test 为临时表名 
        Begin 
            print   '存在 ' 
        End 
Else 
        Begin 
            print   '不存在 ' 
        End
*/




        
If   Object_Id( N'tempdb..#temptest')   Is   Not   NULL --#temptest 为临时表名 
        Begin 
  select * from Tempdb.dbo.#temptest
where 
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) >= '2016/01/01' 
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) <= '2016/01/31'


and
(
房源描述<=50 
or 
出外网标题< 20
or
(roomImg<3 or roomImg is null)
or 
(HouseShapeImg<=0 or HouseShapeImg is null)
)
order by ws0119
        End 
Else 
        Begin 
--创建临时表
            select  ws0119,ws0102,sum(CASE df5003  WHEN '0002' THEN df5004  END) roomImg , --室内图数量
SUM(CASE df5003 WHEN '0000' THEN df5004  END) HouseShapeImg --房型图数量 
  ,len(sa7319) as '出外网标题',sa7339 as '房源描述' 
  ,ws0101,ws0131, ws0103,ws0160, TEMP.qubu, TEMP.pr0102, ws0135,
TEMP.pr0114, sa7319, na0105 ,ws0116
into Tempdb.dbo.#temptest
from WEB..WS001F
left join WEB..SA073F on sa7301 = ws0102 
left join SINYI..DF050F on ws0101=df5002
 inner join  
 ( SELECT   AA.pr0214,AA.pr0202, a.pr0102,a.pr0114 , b.pr0102 AS qubu,a.pr0101  FROM  WEB..PR001F a
  WITH (NOLOCK)  INNER JOIN WEB..PR001F b WITH(NOLOCK) ON a.pr0113=b.pr0101 inner join WEB..PR002F AA 
  on a.pr0101=AA.pr0203 WHERE a.pr0135='0' AND a.pr0111  in('A1','B0') AND a.pr0104='1' AND a.pr0133='6' 
and AA.pr0210='1'
  )  TEMP on ws0119 = TEMP.pr0202 and ws0160 = TEMP.pr0114 
left join WEB..NA001F on na0101 = ws0102 
--
 where  ws0126 = '1' and ws0104 = '0' and ws0131 in ('1','0','2','3')       
group by 
ws0101,ws0102,sa7339,sa7319
,ws0131, ws0119,ws0103,ws0160, TEMP.qubu, TEMP.pr0102, ws0135,
TEMP.pr0114, na0105,ws0116
 
---查询非优质案源
select * from Tempdb.dbo.#temptest
where 

--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) >= '2016/01/01' 
--and SUBSTRING(CONVERT(VARCHAR, ws0116,120),1,10) <= '2016/01/31'


and
(
房源描述<=50 
or 
出外网标题< 20
or
(roomImg<3 or roomImg is null)
or 
(HouseShapeImg<=0 or HouseShapeImg is null)
)
order by ws0119


        End