show_cached

Displays the SQL text of a cached statement.

Syntax

show_cached_text(statement_id)

Parameters

statement_id – is the ID of the statement. Derived from the SSQLID column of monCachedStatement.

Examples

Example 1 – Displays the contents of monCachedStatement, then uses the show_cached_text function to show the SQL text:

select InstanceID, SSQLID, Hashkey, UseCount, StmtType
from monCachedStatement

InstanceID  SSQLID     Hashkey      UseCount  StmtType
----------  ---------- ---------- ----------- --------
0           329111220  1108036110           0       2
0           345111277  1663781964           1       1

select show_cached_text(329111220)

--------------------------------------
select id from sysroles

Usage

show_cached_text displays up to 16K of SQL text, and truncates text longer than 16K. Use show_cached_text_long for text longer than 16K.
show_cached_text returns a varchar datatype.

Permissions

The permission checks for show_cached_text differ based on your granular permissions settings.

Granular Permissions	Description
Enabled	With granular permissions enabled, you must be a user with mon_role, or have `monitor qp performance` permission to execute show_cached_text.
Disabled	With granular permissions disabled, you must be a user with mon_role or sa_role to execute show_cached_text.