sp_showoptstats

Adaptive Server 15.7 SP100 introduces a new option in the sp_showoptstats system procedure to display information about precomputed result sets.

The syntax is:

sp_showoptstats  
[[database_name.[owner].]{table_name|prs_name}],[column_name], [h]

where prs_name is the name of the precomputed result set for which you are displaying statistics.

sp_showoptstats displays additional information when you specify:

No parameter – includes statistical information for all precomputed result sets in the current database.
A precomputed result set – includes statistical information for precomputed result sets.

This example shows sp_showopstats output for the prs1 precomputed result set:

sp_showoptstats prs1 
------------------------------------------------------------------------
 <?xml version="1.0" encoding="UTF-8"?>
 <optStats>
    <procVersion>sp_showoptstats/1.1/AnyPlatform/AnyOS/
                 Tues April 3 14:21:21 2012</procVersion>
    <serverVersion>Adaptive Server Enterprise/15.7.1/EBFXXXXX SMP
     ''/P/x86_64/Enterprise Linux/asecarina/ENG/64-bit/DEBUG/Mon
     Jul 9 00:16:37 2012</serverVersion>
    <serverName></serverName>
    <specifiedDatabase>prsdb</specifiedDatabase>
    <specifiedTableOwner></specifiedTableOwner>
    <specifiedTable>prs1</specifiedTable>
    <specifiedCol></specifiedCol>
    <tables>
        <tableOwner>dbo</tableOwner>
        <tableName>prs1</tableName>
        <tableType>precomputed result set</tableType>
        <tableStats>
 . . .

        </noStatsCol>
    </tables>
 </optStats>