Displays information about system and user-defined procedures.
sp_iqprocedure [ proc-name ], [ proc-owner ], [ proc-type ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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 |
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:
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 |
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
“CREATE USER statement,” in Chapter 1, “SQL Statements,” in Reference: Statements and Options
