Displays the query processing mode of a stored procedure, view, or trigger.
sp_procqmode [object_name [, detail]]
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.
returns information about whether the object contains a subquery, and whether there is information about the object in syscomments.
Displays the query processing mode for all stored procedures in the current database:
Object Owner.name Object Type Processing Mode ----------------- ---------------- -------------- dbo.au_info stored procedure pre-System 11 dbo.titleview view System 11 or later
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
Displays detailed reports for all objects in the database:
sp_procqmode null, detail
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.
Only theDatabase Owner or object owner can execute sp_procqmode.
Stored Procedures sp_helptext