Determine if the SQL Server object exists

Source: Internet
Author: User

--查看对象是否已经存在  --数据库是否存在    --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‘ IF ‘,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 = 扩展存储过程    */   本文出自 “Don‘t Know” 博客,请务必保留此出处http://3941954.blog.51cto.com/3931954/774083

Determine if the SQL Server object exists

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.