SQL determines whether a stored procedure exists

SQL determines whether a stored procedure exists

1 Determine if the database tutorial exists
SQL code
if exists (SELECT * from sys.databases WHERE name = ' database name ')
drop database [database name] if exists (SELECT * from sys.databases WHERE name = ' database name ')
Drop DB [Database name]
2 Determine if the 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 determine 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 to determine 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 determining whether a 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 determine if the 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 '

/*
Xtype represents the type of parameter that typically includes the following
C = CHECK Constraint
D = defaults or 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
*/SELECT [name],[id],crdate from sysobjects where xtype= ' U '
/*
Xtype represents the type of parameter that typically includes the following
C = CHECK Constraint
D = defaults or 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 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 column name if exists (SELECT * from syscolumns where id=object_id (' table name ') and name= ' column name ')
ALTER TABLE name drop column column name
9 Determine if the column is self added
SQL code
If ColumnProperty (object_id (' table '), ' col ', ' isidentity ') =1
print ' Self-add column '
Else
print ' is not a 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-add column '
Else
print ' is not a 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 ' presence '
Else
print ' does not exist if exists (SELECT * from sysindexes where id=object_id (' table name ') and name= ' index name ')
print ' presence '
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 '