SQL Server determines whether an object exists (if exists (select * from sysobjects) (go)

1 determining whether a database exists
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 Determining whether a table exists
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 determining if a stored procedure exists
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 determining whether a temporary table exists
SQL code
If object_id (' tempdb.. #临时表名 ') is not null
drop table #临时表名 if object_id (' tempdb. #临时表名 ') is not null
drop table #临时表名
5 Judging whether the view exists
SQL code
--sql Server 2000
IF 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 2000
IF 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 whether a function exists
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 '

/*
The xtype represents the parameter types, usually including the following
C = CHECK Constraint
D = defaults or DEFAULT constraints
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
*/SELECT [name],[id],crdate from sysobjects where xtype= ' U '
/*
The xtype represents the parameter types, usually including the following
C = CHECK Constraint
D = defaults or DEFAULT constraints
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 determining whether a column exists
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 Judging whether a column is self-increment
SQL code
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not self-adding column '

SELECT * from Sys.columns WHERE object_id=object_id (' Table name ')
and Is_identity=1 if ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-increment column '
Else
print ' is not self-adding column '
SELECT * from Sys.columns WHERE object_id=object_id (' Table name ')
and Is_identity=1
10 determine if an index exists in the table
SQL code
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 viewing objects in a database
SQL code
SELECT * from sys.sysobjects where name= ' object name ' SELECT * from sys.sysobjects where name= ' object name '