sp_cursorinfo

Reports information about a specific cursor or all execute cursors that are active for your session.

Syntax

sp_cursorinfo [{cursor_level | null}] [, cursor_name]

Parameters

Examples

Usage

There are additional considerations when using sp_cursorinfo:
  • If you do not specify either cursor_level or cursor_name, the SAP ASE server displays information about all active cursors. Active cursors are those declared by you and allocated by the SAP ASE server.

  • The SAP ASE server reports the following information about each cursor:
    • The cursor name, its nesting level, its cursor ID, and the procedure name (if it is declared in a stored procedure).

    • The number of times the cursor has been opened.

    • The isolation level (0, 1, or 3) in which it was compiled and in which it is currently scanning (if open).

    • Whether the cursor is open or closed. If the cursor is open, it indicates the current cursor position and the number of rows fetched.

    • Whether the open cursor closes if the cursor’s current position is deleted.

    • Whether the cursor remains open or be closed if the cursor’s current transaction is committed or rolled back.

    • The number of rows returned for each fetch of that cursor.

    • Whether the cursor is updatable or read-only.

    • The number of columns returned by the cursor. For each column, it displays the column name, the table name or expression result, and whether it is updatable.

    The output from sp_cursorinfo varies, depending on the status of the cursor. In addition to the information listed, sp_cursorinfo displays the showplan output for the cursor. For more information about showplan, see the Performance and Tuning Guide.

See also:
  • declare cursor, set in Reference Manual: Commands

Permissions

Any user can execute sp_cursorinfo. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect