SQL determines whether a stored procedure exists

Source: Internet
Author: User
Tags scalar table name

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 '

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.