sql-server - sql server中如何判断一个存储过程是否存在

Question

请帮助我想检查我的数据库中是否存在存储过程。如果确实存在，则应删除该存储过程，如果不存在，则应创建该存储过程。

请告诉我查询

这是我目前的尝试:

try {
objData.Query = "if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].["+ ProcName+"]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1) 
drop procedure [dbo].[ "+ ProcName+"]" ; // 
objData.Query = " Drop Proc " + ProcName;
objData.Execute(); 
} catch 
{
}

Answer 1

IF OBJECT_ID(N'[dbo].[spa_Search]', N'P') IS NOT NULL
    DROP PROCEDURE [dbo].[spa_Search]
GO

您可以检查 OBJECT_ID，它是架构范围对象的对象标识号。

OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ]   
  object_name' [ ,'object_type' ] )

Arguments
' object_name '
Is the object to be used. object_name is either varchar or nvarchar. If object_name is varchar, it is implicitly converted to nvarchar. Specifying the database and schema names is optional.

' object_type '
Is the schema-scoped object type. object_type is either varchar or nvarchar. If object_type is varchar, it is implicitly converted to nvarchar. For a list of object types, see the type column in sys.objects (Transact-SQL)

Return Types
int

Exceptions
For a spatial index, OBJECT_ID returns NULL.
Returns NULL on error.
A user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as OBJECT_ID may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.

快速查看 OBJECT_ID