Displaying SQL Text

set show_sqltext allows you to print the SQL text for ad hoc queries, stored procedures, cursors, and dynamic prepared statements.

You need not enable set show_sqltext before you execute the query (as you do with commands like set showplan on) to collect diagnostic information for a SQL session. Instead, you can enable it while the commands are running to help determine which query is performing poorly.

Before you enable set show_sqltext, enable dbcc traceon to send the command results to standard output (stdout):

dbcc traceon(3604)

The syntax for set show_sqltext is:

set show_sqltext {on | off}

For example, this enables show_sqltext:

set show_sqltext on

Once set show_sqltext is enabled, all SQL text is printed to stdout for each command or system procedure you enter. Depending on the command or system procedure you run, this output can be extensive.

To disable show_sqltext, enter:

set show_sqltext off

Restrictions for show_sqltext

You must have the sa_role or sso_role to run show_sqltext.
You cannot use show_sqltext to print the SQL text for triggers.
You cannot use show_sqltext to show a binding variable or a view name.