Causes Microsoft SQL Server not to execute SQL Server Transact\-SQL statements. Instead, SQL Server Transact\-SQL SQL Server returns detailed information about how the statements are executed.
Transact-SQL Syntax Conventions
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 SQL Server Transact\-SQL statement without executing it. After this option is set ON, execution plan information about all subsequent SQL Server Transact\-SQL SQL Server 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 Transact\-SQL SQL Server SQL Server returns an error message from a subsequent SELECT statement involving that same table informing the user that the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_TEXT is OFF, SQL Server Transact\-SQL SQL Server SQL Server SQL Server executes statements without generating a report with execution plan information.
SET SHOWPLAN_TEXT is intended to return readable output for Microsoft Win32 command prompt 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 [!INCLUDEtsql] 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 (Transact-SQL). |
For more information about the physical and logical operators that can be seen in Showplan output, see Showplan Logical and Physical Operators Reference
In order to use SET SHOWPLAN_TEXT, you must have sufficient permissions to execute the statements on which SET SHOWPLAN_TEXT is executed, and you must have SHOWPLAN permission for all databases containing referenced objects.
For SELECT, INSERT, UPDATE, DELETE, EXEC stored_procedure, and EXEC user_defined_function statements, to produce a Showplan the user must:
Have the appropriate permissions to execute the Transact\-SQL statements.
Have SHOWPLAN permission on all databases containing objects referenced by the Transact-SQL statements, such as tables, views, and so on.
For all other statements, such as DDL, USE database_name, SET, DECLARE, dynamic SQL, and so on, only the appropriate permissions to execute the Transact\-SQL statements are needed.
This example shows how indexes are used by SQL Server as it processes the statements.
This is the query using an index:
USE AdventureWorks2012;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Production.Product
WHERE ProductID = 905;
GO
SET SHOWPLAN_TEXT OFF;
GO
Here is the result set:
StmtText
---------------------------------------------------
SELECT *
FROM Production.Product
WHERE ProductID = 905;
StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT:([AdventureWorks2012].[Production].[Product].[PK_Product_ProductID]), SEEK:([AdventureWorks2012].[Production].[Product].[ProductID]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)
Here is the query not using an index:
USE AdventureWorks2012;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET SHOWPLAN_TEXT OFF;
GO
Here is the result set:
StmtText
------------------------------------------------------------------------
SELECT *
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([AdventureWorks2012].[Production].[ProductCostHistory].[PK_ProductCostHistory_ProductCostID]), WHERE:([AdventureWorks2012].[Production].[ProductCostHistory].[StandardCost]<[@1]))
Operators (Transact-SQL)
SET Statements (Transact-SQL)
SET SHOWPLAN_ALL (Transact-SQL)