判断sqlserver对象是否存在（转）

发布时间：2020-12-12 13:55:22 所属栏目：MsSql教程来源：网络整理

导读：--查看对象是否已经存在? --数据库是否存在? ? ? --if exists (select * from sys.databases where name = ’数据库名’)??? --? drop database [数据库名]? ???? ? if exists( select ? * from ? sys.databases where ? name = 'FGM_POS' )? print '存在' ?

--查看对象是否已经存在? --数据库是否存在? ?? --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 ( sysobjects where? id =OBJECT_ID(N '[FGM_bt_ePlnMain]' ) and? OBJECTPROPERTY(id,N 'IsUserTable' )=1)? '存在'? --drop table [表名]? --或? 'dbo.FGM_bt_ePlnMain' )=1)? '存在'? ?? --存储过程是否存在? ?? --if exists (select * from sysobjects where id = object_id(N’[存储过程名]’) and OBJECTPROPERTY(id,N’IsProcedure’) = 1)??? --? drop procedure [存储过程名]?? ????? '[FGM_sp_SyncePlnMain]' 'IsProcedure' )=1)? '存在'? --或? 'dbo.FGM_sp_SyncePlnMain' )=1)? '存在'? ?? --临时表是否存在? ?? --if object_id(’tempdb..#临时表名’) is not null????? --? drop table #临时表名?? ????? into? # temp? dbo.FGM_bt_ConsumInfoDetail_B? if OBJECT_ID ( 'tempdb..#temp' ) is? not? null? '存在'? --或者? tempdb.dbo.sysobjects id=OBJECT_ID(N ) type= 'U' )? '存在'? ?? --视图是否存在? ----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? id=OBJECT_ID(N'[dc_adplan] ') and type=' V ')? print ' 存在 '? --或? if exists (select * from sysobjects where id=OBJECT_ID(N' dbo.dc_adplan ')? '? --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 TF '))? '? --或? [GetResourceName] '))? '? --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 ')? '? --alter table 表名 drop column 列名? ?? --判断列是否自自增列? ?? --if columnproperty(object_id(' table '),' col’,’IsIdentity’)=1??? --? print '自增列'??? --else??? --? print '不是自增列'??? ?? if COLUMNPROPERTY(object_id ( 'FGM_bt_ePlnMain' ), 'ePlnID' ,monospace!important; min-height:inherit!important; color:blue!important">'IsIdentity' )=1? '自增列'? else? '非'? ?? ?? --查看数据库中对象? --select * from sys.sysobjects where name='对象名' ?? sys.sysobjects 'FGM_bt_ePlnMain' ?? --获取用户创建的对象信息? ?? SELECT? [ ],[id],crdate FROM? xtype= 'U' /*??? xtype 的表示参数类型，通常包括如下这些??? C = CHECK? 约束??? D = 默认值或 DEFAULT? 约束??? F = FOREIGN? KEY? 约束??? L = 日志??? FN = 标量函数??? IF = 内嵌表函数??? P = 存储过程??? PK = PRIMARY? 约束（类型是 K）??? RF = 复制筛选存储过程??? S = 系统表??? TF = 表函数??? TR = 触发器??? U = 用户表??? UQ = UNIQUE? 约束（类型是 K）??? V = 视图??? X = 扩展存储过程??? */? ?? 本文出自 “Don't Know” 博客，请务必保留此出处http://www.voidcn.com/article/p-buikrbqo-ps.html

（编辑：李大同）

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