Export (0) Print
Expand All
Expand Minimize

sp_stored_procedures

SQL Server 2000

Returns a list of stored procedures in the current environment.

Syntax

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

Arguments

[@sp_name =] 'name'

Is the name of the procedure used to return catalog information. name is nvarchar(390), with a default of NULL. Wildcard pattern matching is supported.

[@sp_owner =] 'owner'

Is the name of the owner of the procedure. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If owner is not specified, the default procedure visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server™, if the current user owns a procedure with the specified name, that procedure is returned. If owner is not specified and the current user does not own a procedure with the specified name, this procedure looks for a procedure with the specified name owned by the database owner. If one exists, that procedure is returned.

[@qualifier =] 'qualifier'

Is the name of the procedure qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.

Return Code Values

None

Result Sets
Column name Data type Description
PROCEDURE_QUALIFIER sysname Procedure qualifier name. This field can be NULL.
PROCEDURE_OWNER sysname Procedure owner name. This field always returns a value.
PROCEDURE_NAME nvarchar(134) Procedure name. This field always returns a value.
NUM_INPUT_PARAMS int Reserved for future use.
NUM_OUTPUT_PARAMS int Reserved for future use.
NUM_RESULT_SETS int Reserved for future use.
REMARKS varchar(254) Description of the procedure. SQL Server does not return a value for this column.
PROCEDURE_TYPE smallint Procedure type. SQL Server always returns 2.0. Can be:

0 = SQL_PT_UNKNOWN
1 = SQL_PT_PROCEDURE
2 = SQL_PT_FUNCTION


Remarks

For maximum interoperability, the gateway client should assume only SQL-92-standard pattern matching (the % and _ wildcards).

The privilege information about the current user's execute access to a specific stored procedure is not necessarily checked, so access is not guaranteed. Note that only three-part naming is used, so that only local stored procedures, not remote stored procedures (which need four-part naming), are returned when implemented against SQL Server. 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 is equivalent to SQLProcedures in ODBC. The results returned are ordered by PROCEDURE_QUALIFIER, PROCEDURE_OWNER, and PROCEDURE_NAME.

Permissions

Execute permissions default to the public role.

See Also

System Stored Procedures

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft