sp_stored_procedures (Transact-SQL)sp_stored_procedures (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

返回当前环境中的存储过程列表。Returns a list of stored procedures in the current environment.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
sp_stored_procedures [ [ @sp_name = ] 'name' ]   
    [ , [ @sp_owner = ] 'schema']   
    [ , [ @sp_qualifier = ] 'qualifier' ]  
    [ , [@fUsePattern = ] 'fUsePattern' ]  

参数Arguments

[ @sp_name = ] 'name' 用于返回目录信息的过程的名称。[ @sp_name = ] 'name' Is the name of the procedure used to return catalog information. namenvarchar (390),默认值为 NULL。name is nvarchar(390), with a default of NULL. 支持通配符模式匹配。Wildcard pattern matching is supported.

[ @sp_owner = ] 'schema' 过程所属架构的名称。[ @sp_owner = ] 'schema' Is the name of the schema to which the procedure belongs. 架构nvarchar (384),默认值为 NULL。schema is nvarchar(384), with a default of NULL. 支持通配符模式匹配。Wildcard pattern matching is supported. 如果未指定 owner ,则应用基础 DBMS 的默认过程可见性规则。If owner is not specified, the default procedure visibility rules of the underlying DBMS apply.

SQL ServerSQL Server 中,如果当前架构包含具有指定名称的过程,则返回此过程。In SQL ServerSQL Server, if the current schema contains a procedure with the specified name, that procedure is returned. 如果指定了非限定存储过程,则数据库引擎Database Engine按以下顺序搜索此过程:If a nonqualified stored procedure is specified, the 数据库引擎Database Engine searches for the procedure in the following order:

  • 当前数据库的 sys 架构。The sys schema of the current database.

  • 调用方的默认架构(在使用批或动态 SQL 执行时);或者,如果非限定的过程名称出现在另一个过程定义的主体中,则接着搜索包含这一过程的架构。The caller's default schema if executed in a batch or in dynamic SQL; or, if the non-qualified procedure name appears inside the body of another procedure definition, the schema containing this other procedure is searched next.

  • 当前数据库中的 dbo 架构。The dbo schema in the current database.

[ @qualifier = ] 'qualifier' 过程限定符的名称。[ @qualifier = ] 'qualifier' Is the name of the procedure qualifier. 限定符 的值为 sysname,默认值为 NULL。qualifier is sysname, with a default of NULL. 各种 DBMS 产品支持表的三部分命名 (限定符形式的表 架构名称Various DBMS products support three-part naming for tables in the form (qualifier.schema.name. 在中 SQL ServerSQL Server限定符 表示数据库名称。In SQL ServerSQL Server, qualifier represents the database name. 在某些产品中,它表示表所在数据库环境的服务器名称。In some products, it represents the server name of the database environment of the table.

[ @fUsePattern = ] 'fUsePattern' 确定下划线 () 、百分比 (% ) 还是方括号 [] ) 被解释为通配符。[ @fUsePattern = ] 'fUsePattern' Determines whether the underscore (), percent (%), or brackets [ ]) are interpreted as wildcard characters. fUsePattern 的值为 bit,默认值为1。fUsePattern is bit, with a default of 1.

0 = 模式匹配是关闭的。0 = Pattern matching is off.

1 = 模式匹配为 on。1 = Pattern matching is on.

返回代码值Return Code Values

None

结果集Result Sets

列名称Column name 数据类型Data type 说明Description
PROCEDURE_QUALIFIERPROCEDURE_QUALIFIER sysnamesysname 过程限定符名称。Procedure qualifier name. 该列可以为 NULL。This column can be NULL.
PROCEDURE_OWNERPROCEDURE_OWNER sysnamesysname 过程所有者名称。Procedure owner name. 该列始终返回值。This column always returns a value.
PROCEDURE_NAMEPROCEDURE_NAME nvarchar (134)nvarchar(134) 过程名。Procedure name. 该列始终返回值。This column always returns a value.
NUM_INPUT_PARAMSNUM_INPUT_PARAMS intint 留待将来使用。Reserved for future use.
NUM_OUTPUT_PARAMSNUM_OUTPUT_PARAMS intint 留待将来使用。Reserved for future use.
NUM_RESULT_SETSNUM_RESULT_SETS intint 留待将来使用。Reserved for future use.
备注REMARKS varchar (254)varchar(254) 对过程的说明。Description of the procedure. SQL ServerSQL Server 不为此列返回值。does not return a value for this column.
PROCEDURE_TYPEPROCEDURE_TYPE smallintsmallint 过程类型。Procedure type. SQL ServerSQL Server 始终返回 2.0。always returns 2.0. 此值可以为下列值之一:This value can be one of the following:

0 = SQL_PT_UNKNOWN0 = SQL_PT_UNKNOWN

1 = SQL_PT_PROCEDURE1 = SQL_PT_PROCEDURE

2 = SQL_PT_FUNCTION2 = SQL_PT_FUNCTION

备注Remarks

为了获得最大互操作性,网关客户端应只采用 SQL 标准模式匹配(百分号 (%) 和下划线 () 通配符)。For maximum interoperability, the gateway client should assume only SQL standard pattern matching (the percent (%) and underscore () wildcard characters).

由于不就当前用户对特定存储过程执行访问的权限信息进行必要的检查,因此访问得不到保证。The permission information about execute access to a specific stored procedure for the current user is not necessarily checked; therefore, access is not guaranteed. 请注意只使用三部分命名。Note that only three-part naming is used. 这表示在对 SQL ServerSQL Server 执行时,只返回本地存储过程而不返回要求四部分命名的远程存储过程。This means that only local stored procedures, not remote stored procedures (which require four-part naming), are returned when they are executed against SQL ServerSQL Server. 如果 sp_server_info 的结果集中的服务器属性 ACCESSIBLE_SPROC 为 Y,则只返回可由当前用户执行的存储过程。If the server attribute ACCESSIBLE_SPROC is Y in the result set for sp_server_info, only stored procedures that can be executed by the current user are returned.

sp_stored_procedures 等效于 ODBC 中的 SQLProceduressp_stored_procedures is equivalent to SQLProcedures in ODBC. 返回的结果按 PROCEDURE_QUALIFIERPROCEDURE_OWNERPROCEDURE_NAME 排序。The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME.

权限Permissions

需要对架构的 SELECT 权限。Requires SELECT permission on the schema.

示例Examples

A.A. 返回当前数据库中的所有存储过程Returning all stored procedures in the current database

以下示例返回 AdventureWorks2012AdventureWorks2012 数据库中的所有存储过程。The following example returns all stored procedures in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
EXEC sp_stored_procedures;  

B.B. 返回单个存储过程Returning a single stored procedure

以下示例返回 uspLogError 存储过程的结果集。The following example returns a result set for the uspLogError stored procedure.

USE AdventureWorks2012;  
GO  
sp_stored_procedures N'uspLogError', N'dbo', N'AdventureWorks2012', 1;  

另请参阅See Also

(Transact-sql)的目录存储过程 Catalog Stored Procedures (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)