sql server 判断表/视图/存储过程是否存在

--如果是实表可以用
if exists (select * from sysobjects where id = object_id(N'[dbo].[表名]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[表名]
--如果是临时表可以用(说明,如果用查找实表方法来打临时表会找不到.发布区别对代.)
if object_id('tempdb..##temp') is not null
   drop table ##temp
--判断存储过程是否存在
MSSQL2000
view plaincopy to clipboardprint?
if exists(select * from sysobjects where id=object_id('[dbo].[sp_create_storeCount]') and xtype='P')   
DROP PROCEDURE [dbo].[sp_create_storeCount] 
if exists(select * from sysobjects where id=object_id('[dbo].[sp_create_storeCount]') and xtype='P')
DROP PROCEDURE [dbo].[sp_create_storeCount] 
MSSQL2005
 view plaincopy to clipboardprint?
IF  EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountHz]') AND type in (N'P', N'PC'))  
DROP PROCEDURE [dbo].[AccountHz] 
IF  EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AccountHz]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AccountHz]
 
--判断视图是否存在
--SQL Server 2000
IF EXISTS (SELECT * from sysviews WHERE object_id = '[dbo].[视图名]'
--SQL Server 2005
IF EXISTS (SELECT * from sys.views WHERE object_id = '[dbo].[视图名]'
/*
sysObjects (
Name sysname,      --object 名称
id   int,          --object id
xtype char(2),     -- object 类型  
type char(2),     -- Object 类型（与xtype 似乎一模一样？ 有点郁闷…）
uid   smallint,     -- object 所有者的ID
...                --其他的字段不常用到。  
)
sysobjects的xtype 代表的对象类型。可以是下列对象类型中的一种：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN =