sp_iqprocedure Procedure

Displays information about system and user-defined procedures.

Syntax

sp_iqprocedure [ proc-name ], [ proc-owner ], [ proc-type ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Usage

Parameters
Parameter	Description
proc-name	The name of the procedure.
proc-owner	The owner of the procedure.
proc-type	The type of procedure. Allowed values are: SYSTEM: displays information about system procedures (procedures owned by user `SYS` or `dbo`) only ALL: displays information about user and system procedures Any other value: displays information about user procedures

The sp_iqprocedure procedure can be invoked without any parameters. If no parameters are specified, only information about user-defined procedures (procedures not owned by dbo or SYS) is displayed by default.

If you do not specify either of the first two parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqprocedure NULL, NULL, SYSTEM and sp_iqprocedure NULL, user1.

sp_iqprocedure usage examples
Syntax	Output
sp_iqprocedure	Displays information about all procedures in the database not owned by `dbo` or `SYS`
sp_iqprocedure sp_test	Displays information about the procedure sp_test
sp_iqprocedure non_existing_proc	No rows returned, as the procedure non_existing_proc does not exist
sp_iqprocedure NULL, DBA	Displays information about all procedures owned by `DBA`
sp_iqprocedure sp_test, DBA	Displays information about the procedure sp_test owned by `DBA`
sp_iqprocedure sp_iqtable	The procedure sp_iqtable is not a system procedure. If there is no user-defined procedure also named sp_iqtable, no rows are returned. (By default only user-defined procedures are returned.)
sp_iqprocedure sp_iqtable, dbo	No rows returned, as the procedure sp_iqtable is not a user procedure (by default only user procedures returned)
sp_iqprocedure NULL, NULL, SYSTEM	Displays information about all system procedures (owned by `dbo` or `SYS`)
sp_iqprocedure sp_iqtable, NULL, ‘YSTEM	Displays information about the system procedure sp_iqtable
sp_iqprocedure sp_iqtable, dbo, ALL	Displays information about the system procedure sp_iqtable owned by `dbo`

Description

The sp_iqprocedure stored procedure displays information about procedures in a database. If you specify one or more parameters, the result is filtered by the specified parameters. For example, if proc-name is specified, only information about the specified procedure is displayed. If proc-owner is specified, sp_iqprocedure returns only information about procedures owned by the specified owner. If no parameters are specified, sp_iqprocedure displays information about all the user-defined procedures in the database.

The sp_iqprocedure procedure returns information in the following columns:

sp_iqprocedure columns
Column name	Description
proc_name	The name of the procedure
proc_owner	The owner of the procedure
proc_defn	The command used to create the procedure. For hidden procedures, the keyword ‘HIDDEN’ is displayed.
replicate	Displays Y if the procedure is a primary data source in a Replication Server installation; N if not.
srvid	Indicates the remote server, if the procedure is on a remote database server
remarks	A comment string

Examples

Displays information about the user-defined procedure sp_test:

sp_iqprocedure sp_test

proc_name    proc_owner    proc_defn       replicate     srvid     remarks

sp_test      DBA        create procedure   N             (NULL)    (NULL)
                        DBA.sp_test(in n1
                        integer)
                        begin message‘sp_test’end

Displays information about all procedures owned by user DBA:

sp_iqprocedure NULL, DBA

proc_name    proc_owner    proc_defn       replicate     srvid     remarks

sp_test      DBA        create procedure   N             (NULL)    (NULL)
                        DBA.sp_test(in n1
                        integer)
                        begin message‘sp_test’end
sp_dept      DBA        create procedure   N             (NULL)    (NULL)
                        DBA.sp_dept() begin end