Causes Microsoft® SQL Server™ not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
SET SHOWPLAN_TEXT {ON | OFF}
The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.
When SET SHOWPLAN_TEXT is ON, SQL Server returns execution information for each Transact-SQL statement without executing it. After this option is set ON, information about all subsequent Transact-SQL statements is returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_TEXT is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table; the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_TEXT is OFF, SQL Server executes statements without generating a report.
SET SHOWPLAN_TEXT is intended to return readable output for Microsoft MS-DOS® applications such as the osql utility. SET SHOWPLAN_ALL returns more detailed output intended to be used with programs designed to handle its output.
SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified in a stored procedure; they must be the only statements in a batch.
SET SHOWPLAN_TEXT returns information as a set of rows that form a hierarchical tree representing the steps taken by the SQL Server query processor as it executes each statement. Each statement reflected in the output contains a single row with the text of the statement, followed by several rows with the details of the execution steps. The table shows the column that the output contains.
| Column name | Description |
|---|---|
| StmtText | For rows which are not of type PLAN_ROW, this column contains the text of the Transact-SQL statement. For rows of type PLAN_ROW, this column contains a description of the operation. This column contains the physical operator and may optionally also contain the logical operator. This column may also be followed by a description which is determined by the physical operator. For more information about physical operators, see the Argument column in SET SHOWPLAN_ALL. |
For more information about the physical and logical operators that can be seen in showplan output, see Logical and Physical Operators.
SET SHOWPLAN_TEXT permissions default to all users.
This example shows how indexes are used by SQL Server as it processes the statements.
This is the query using an index:
SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE title_id = 'PS1372'
GO
SET SHOWPLAN_TEXT OFF
GO
Here is the result set:
StmtText
------------------------------------------------------
USE pubs
SELECT *
FROM roysched
WHERE title_id = 'PS1372'
(2 row(s) affected)
StmtText
------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([pubs].[dbo].[roysched]))
|--Index Seek(OBJECT:([pubs].[dbo].[roysched].[titleidind]), SEEK:([roysched].[title_id]='PS1372') ORDERED)
(2 row(s) affected)
Here is the query not using an index:
SET SHOWPLAN_TEXT ON
GO
USE pubs
SELECT *
FROM roysched
WHERE lorange < 5000
GO
SET SHOWPLAN_TEXT OFF
GO
Here is the result set:
StmtText
-------------------------------------------------
USE pubs
SELECT *
FROM roysched
WHERE lorange < 5000
(2 row(s) affected)
StmtText
------------------------------------------------------------------------
|--Table Scan(OBJECT:([pubs].[dbo].[roysched]), WHERE:([roysched].[lorange]<5000))
(1 row(s) affected)
| Operators | SET |
| SET SHOWPLAN_ALL |