SQL Server determines whether an object exists

1 determining whether a database exists

if exists(Select *  fromsys.databaseswhereName=' database name ')Drop Database [Database name]  if exists(Select *  fromsys.databaseswhereName=' database name ')Drop Database [Database name]

2 Determining whether a table exists

if exists(Select *  fromsysobjectswhereId= object_idN[Table name]’) and ObjectProperty(ID, N ' isusertable ')= 1)    Drop Table [Table name]  if exists(Select *  fromsysobjectswhereId= object_idN[Table name]’) and ObjectProperty(ID, N ' isusertable ')= 1)  Drop Table [Table name]

3 determining if a stored procedure exists

if exists(Select *  fromsysobjectswhereId= object_idN[Stored Procedure name]’) and ObjectProperty(ID, N ' isprocedure ')= 1)    Drop procedure [Stored Procedure name]  if exists(Select *  fromsysobjectswhereId= object_idN[Stored Procedure name]’) and ObjectProperty(ID, N ' isprocedure ')= 1)  Drop procedure [Stored Procedure name]

4 determining whether a temporary table exists

if object_id  is  not NULL      Drop table #临时表名  ifobject_idis not    null  DropTable

5 Judging whether the view exists

--SQL ServerIF EXISTS(SELECT *  fromSysviewsWHERE object_id =’[dbo].[View name]'--SQL Server 2005IF EXISTS(SELECT *  fromSys.viewsWHERE object_id =’[dbo].[View name]’--SQL ServerIF EXISTS(SELECT *  fromSysviewsWHERE object_id =’[dbo].[View name]'--SQL Server 2005IF EXISTS(SELECT *  fromSys.viewsWHERE object_id =’[dbo].[View name]’

6 determining whether a function exists

--determine if the name of the function to be created exists  if exists(Select *  fromDbo.sysobjectswhereId= object_idN[dbo].[Name of function]’) andXtypeinch(n ' FN ', n 'IF', N ' TF ')) Drop function [dbo].[Name of function]    --determine if the name of the function to be created exists  if exists(Select *  fromDbo.sysobjectswhereId= object_idN[dbo].[Name of function]’) andXtypeinch(n ' FN ', n 'IF', N ' TF ')) Drop function [dbo].[Name of function]  

7 getting user-created object information

SELECT [name],[ID], Crdate fromsysobjectswhereXtype=' U '/*the 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 = Store Process 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 Stored Procedures*/  SELECT [name],[ID], Crdate fromsysobjectswhereXtype=' U '/*the 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 K EY 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 stored Procedure*/

8 determining whether a column exists

if exists(Select *  fromsyscolumnswhereId=object_id(' Table name ') andName=' column name ')Alter TableTable nameDrop columnColumn Nameif exists(Select *  fromsyscolumnswhereId=object_id(' Table name ') andName=' column name ')Alter TableTable nameDrop columnColumn Name

9 Judging whether a column is self-increment

if ColumnProperty(object_id(’Table'), ' col ', ' isidentity ')=1    Print' self-increment column 'Else    Print' not self-increment 'SELECT *  fromSys.columnsWHERE object_id=object_id(' table name ') andIs_identity=1  if ColumnProperty(object_id(’Table'), ' col ', ' isidentity ')=1  Print' self-increment column 'Else  Print' not self-increment 'SELECT *  fromSys.columnsWHERE object_id=object_id(' table name ') andIs_identity=1

10 determine if an index exists in the table

if exists(Select *  fromsysindexeswhereId=object_id(' Table name ') andName=' index name ')Print' existence 'Else      Print' does not existif exists(Select *  fromsysindexeswhereId=object_id(' Table name ') andName=' index name ')Print' existence 'Else    Print' does not exist

11 viewing objects in a database

SELECT *  from where name=' object name '  SELECT*fromWHERE name= ' Object name '

SQL Server determines whether an object exists