sp_iqprocedure procedure

Function

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

proc-name The name of the procedure.

proc-owner The owner of the procedure.

proc-type The type of procedure. Allowed values are:

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.

Table 7-48: 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:

Table 7-49: 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

See also

“CREATE USER statement,” in Chapter 1, “SQL Statements,” in Reference: Statements and Options