sp_procqmode

Description

Displays the query processing mode of a stored procedure, view, or trigger.

Syntax

sp_procqmode [object_name [, detail]]

Parameters

object_name: is the name of the stored procedure, view, or trigger whose query processing mode you are examining. If you do not specify an object_name, sp_procqmode reports on all procedures, views, and triggers in the current database.

detail: returns information about whether the object contains a subquery, and whether there is information about the object in syscomments.

Examples

Example 1

Displays the query processing mode for all stored procedures in the current database:

sp_procqmode

Object Owner.name  Object Type        Processing Mode
-----------------  ----------------   --------------
dbo.au_info        stored procedure   pre-System 11
dbo.titleview      view               System 11 or later

Example 2

Displays the query processing mode of the stored procedure old_sproc, reports whether old_sproc contains any subqueries, and reports whether syscomments has information about old_sproc:

sp_procqmode old_sproc, detail

Object Owner.Name     Object Type       Processing Mode     Subq Text
--------------------- ----------------- ------------------- ---- ----
dbo.au_info           stored procedure  pre-System 11       no   yes

Example 3

Displays detailed reports for all objects in the database:

sp_procqmode null, detail

Usage

The processing mode identifies whether the object was created in SQL Server release 10.0 or earlier. Objects created on release 10.x (or earlier) servers are “pre-System 11” objects. Objects created on release 11.0 or later servers are “System 11 or later” objects.
Subqueries in “pre-System 11” objects use a different processing mode than subqueries in “System 11 or later” objects. Upgrading to release 11.0 or later does not automatically change the processing mode of the subquery.

In general, the “System 11 or later” processing mode is faster than “pre-System 11” processing mode. To change the processing mode to “System 11 or later”, drop and re-create the object. You cannot create an object with “pre-System 11” processing on the current release of Adaptive Server, so you may want to create the object with another name and test it before dropping the version that uses “pre-System 11” processing mode.
The processing mode displayed for a given object is independent of whether that object actually includes a subquery, and pertains only to the specified object, not to any dependent objects. You must check each object separately.
The detailed report shows if the object contains a subquery, and reports if text is available in syscomments (for sp_helptext to report, or for the defncopy utility to copy out). sp_procqmode does not check that the text in syscomments is valid or complete.

Permissions

Only theDatabase Owner or object owner can execute sp_procqmode.