If exists usage details in SQL

Use if exists to build a table "go"

1 Determine if the database has SQL code

if exists (SELECT * from sys.databases WHERE name = ' database name ')

drop database [DB name] if exists (SELECT * from sys.databases WHERE name = ' database name ')

drop database [DB name]

2 Determine if the table has SQL code

if exists (select * from sysobjects where id = object_id (n ' [table name] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)

drop table [table name]

if exists (select * from sysobjects where id = object_id (n ' [table name] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1) drop table [Table name]

3 determine if the stored procedure has SQL code

if exists (select * from sysobjects where id = object_id (n ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' isprocedure ') = 1)

drop procedure [Stored procedure name] if exists (SELECT * from sysobjects where id = object_id (N ' [Stored procedure name] ') and OBJECTPROPERTY (ID, n ' ISPR Ocedure ') = 1)

drop procedure [Stored procedure name]

4 to determine if the temporary table has SQL code

If object_id (' tempdb.. #临时表名 ') is not null

drop table #临时表名 if object_id (' tempdb. #临时表名 ') is not a null drop table #临时表名

5 Judging whether the view has SQL code

--sql Server EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] '

--sql Server 2005 IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] '

--sql Server EXISTS (SELECT * from sysviews WHERE object_id = ' [dbo].[ View name] '

--sql Server 2005 IF EXISTS (SELECT * from sys.views WHERE object_id = ' [dbo].[ View name] '

6 determining if a function has SQL code

--Determine if the name of the function to be created exists

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))

Drop function [dbo]. [function name]

--Determine if the name of the function to be created exists

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Function name] ') and xtype in (n ' FN ', n ' IF ', n ' TF '))

Drop function [dbo]. [function name]

7 getting user-created object information SQL code

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

/* Xtype represents the parameter type, usually including the following C = CHECK constraint D = default value or defaults constraint F = FOREIGN KEY constraint L = log FN = scalar function IF = inline table function P = stored procedure PK = PRIMARY KEY constraint (type is k) RF = copy Filter stored procedure S = system table TF = table function TR = trigger U = User Table UQ = UNIQUE constraint (type k) V = view X = Extended Storage Process */SELECT [name],[id],crdate from sysobjects where xtype= ' U '/* xtype represents the parameter type, usually including the following C = CHECK constraint D = default Constraint F = FOREIGN key constraint L = log FN = scalar function IF = inline table function P = stored procedure PK = PRIMARY KEY constraint (type is K) RF = copy Filter stored procedure S = system table TF = Table function TR = Trigger U = User Table UQ = UNIQUE constraint (type is K) V = view X = Extended stored procedure */

8 Determine if the column has SQL code

if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')

ALTER TABLE name drop column name if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')

ALTER TABLE table name drop column name

9 Determining if a column is self-adding SQL code

If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1 print ' self-increment ' else print ' is not a self-increment column '

SELECT * from Sys.columns WHERE object_id=object_id (' table name ') and Is_identity=1 if ColumnProperty (object_id (' Tables '), ' col ', ' isidentity ') =1 print ' self-increment ' else print ' is not a self-increment column ' SELECT * from sys.columns WHERE object_id=object_id (' table name ') and Is_identi Ty=1

10 determine if the index SQL code exists in the table

if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ') print ' exists ' else print ' does not exist if Exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ') print ' exists ' else print ' does not exist 11 view objects in database S QL Code

SELECT * from sys.sysobjects where name= ' object name ' SELECT * from sys.sysobjects where name= ' object name '