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

Source: Internet
Author: User
Tags scalar

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 '

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.