判断sqlserver对象是否已存在

?

 
 

?
  
  
--查看对象是否已经存在 ?
  
  
--数据库是否存在 ?
  
  
?
  
  

--if?exists?(select?*?from?sys.databases?where?name?=?’数据库名’)?? ?

  
  
--??drop?database?[数据库名] ?
  
  
?? ?
  
  

if?exists(select?*?from?sys.databases?where?name='FGM_POS') ?

  
  
print?'存在' ?
  
  
--drop?database?[数据库名] ?
  
  
?
  
  
--表是否存在 ?
  
  
?
  
  

--if?exists?(select?*?from?sysobjects?where?id?=?object_id(N’[表名]’)?and?OBJECTPROPERTY(id,?N’IsUserTable’)?=?1)?? ?

  
  
--??drop?table?[表名] ?
  
  
?? ?
  
  

if?exists?(select?*?from?sysobjects?where?id?=OBJECT_ID(N'[FGM_bt_ePlnMain]')?and?OBJECTPROPERTY(id,N'IsUserTable')=1) ?

  
  
print?'存在' ?
  
  
--drop?table?[表名] ?
  
  
--或 ?
  
  

if?exists?(select?*?from?sysobjects?where?id?=OBJECT_ID(N'dbo.FGM_bt_ePlnMain')?and?OBJECTPROPERTY(id,N'IsUserTable')=1) ?

  
  
print?'存在' ?
  
  
?
  
  
--存储过程是否存在 ?
  
  
?
  
  

--if?exists?(select?*?from?sysobjects?where?id?=?object_id(N’[存储过程名]’)?and?OBJECTPROPERTY(id,?N’IsProcedure’)?=?1)?? ?

  
  
--??drop?procedure?[存储过程名]? ?
  
  
?? ?
  
  

if?exists?(select?*?from?sysobjects?where?id?=OBJECT_ID(N'[FGM_sp_SyncePlnMain]')?and?OBJECTPROPERTY(id,N'IsProcedure')=1) ?

  
  
print?'存在' ?
  
  
--或 ?
  
  

if?exists?(select?*?from?sysobjects?where?id?=OBJECT_ID(N'dbo.FGM_sp_SyncePlnMain')?and?OBJECTPROPERTY(id,N'IsProcedure')=1) ?

  
  
print?'存在' ?
  
  
?
  
  
--临时表是否存在 ?
  
  
?
  
  
--if?object_id(’tempdb..#临时表名’)?is?not?null???? ?
  
  
--??drop?table?#临时表名? ?
  
  
?? ?
  
  
select?*?into?#temp?from?dbo.FGM_bt_ConsumInfoDetail_B ?
  
  
if?OBJECT_ID?('tempdb..#temp')is?not?null ?
  
  
print?'存在' ?
  
  
--或者 ?
  
  

if?exists(select?*?from?tempdb.dbo.sysobjects?where?id=OBJECT_ID(N'tempdb..#temp')and?type='U') ?

  
  
print?'存在' ?
  
  
?
  
  
--视图是否存在 ?
  
  
----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].[视图名]'??--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].[视图名]’ ?

  
  
----SQL?Server?2008 ?
  
  

if?exists?(select?*?from?sysobjects?where?id=OBJECT_ID(N'[dc_adplan]')?and?type='V') ?

  
  
print?'存在' ?
  
  
--或 ?
  
  

if?exists?(select?*?from?sysobjects?where?id=OBJECT_ID(N'dbo.dc_adplan')?and?type='V') ?

  
  
print?'存在' ?
  
  
--drop?view?dbo.dc_adplan ?
  
  
?
  
  
--函数是否存在 ?
  
  
?
  
  

?--if?exists?(select?*?from?dbo.sysobjects?where?id?=?object_id(N’[dbo].[函数名]’)?and?xtype?in?(N’FN’,?N’IF’,?N’TF’))???? ?

  
  
?--?drop?function?[dbo].[函数名]? ?
  
  
?? ?
  
  

if?exists?(select?*?from?dbo.sysobjects?where?id=OBJECT_ID?(N'dbo.GetResourceName')and?xtype?in?(N'FN',N'IF',N'TF')) ?

  
  
print?'存在' ?
  
  
--或 ?
  
  

if?exists?(select?*?from?dbo.sysobjects?where?id=OBJECT_ID?(N'[GetResourceName]')and?xtype?in?(N'FN',N'TF')) ?

  
  
print?'存在' ?
  
  
--drop?function?dbo.GetResourceName ?
  
  
?
  
  
--列是否存在 ?
  
  
?
  
  

--if?exists(select?*?from?syscolumns?where?id=object_id(’表名’)?and?name=’列名’)?? ?

  
  
--??alter?table?表名?drop?column?列名? ?
  
  
?
  
  

if?exists(select?*?from?syscolumns?where?id?=OBJECT_ID('FGM_bt_ePlnMain')?and?name='ePlnName') ?

  
  
print?'存在' ?
  
  
--alter?table?表名?drop?column?列名 ?
  
  
?
  
  
--判断列是否自自增列 ?
  
  
?
  
  
--if?columnproperty(object_id('table'),'col’,’IsIdentity’)=1?? ?
  
  
--??print?'自增列'?? ?
  
  
--else?? ?
  
  
--??print?'不是自增列'?? ?
  
  
?
  
  
if?COLUMNPROPERTY(object_id?('FGM_bt_ePlnMain'),'ePlnID','IsIdentity')=1 ?
  
  
print?'自增列' ?
  
  
else ?
  
  
print?'非' ?
  
  
?
  
  
?
  
  
--查看数据库中对象 ?
  
  

--select?*?from?sys.sysobjects?where?name='对象名'?

  
  
?
  
  

select?*?from?sys.sysobjects?where?name='FGM_bt_ePlnMain'?

  
  
?
  
  
--获取用户创建的对象信息 ?
  
  
?
  
  

SELECT?[name],[id],crdate?FROM?sysobjects?where?xtype='U'?

  
  
/*?? ?
  
  
xtype?的表示参数类型，通常包括如下这些?? ?
  
  

C?=?CHECK?约束?? ?

  
  

D?=?默认值或?DEFAULT?约束?? ?

  
  

F?=?FOREIGN?KEY?约束?? ?

  
  

L?=?日志?? ?

  
  

FN?=?标量函数?? ?

  
  

IF?=?内嵌表函数?? ?

  
  

P?=?存储过程?? ?

  
  

PK?=?PRIMARY?KEY?约束（类型是?K）?? ?

  
  

RF?=?复制筛选存储过程?? ?

  
  

S?=?系统表?? ?

  
  

TF?=?表函数?? ?

  
  

TR?=?触发器?? ?

  
  

U?=?用户表?? ?

  
  

UQ?=?UNIQUE?约束（类型是?K）?? ?

  
  

V?=?视图?? ?

  
  

X?=?扩展存储过程?? ?

  
  
*/ ?
 
 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!