set show_sqltext allows you to print the SQL text for ad-hoc queries, stored procedures, cursors, and dynamic prepared statements. You do not need to enable the 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 and diagnose their problems.
Before you enable show_sqltext, you must first enable dbcc traceon to display the output to standard out:
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, Adaptive Server prints all SQL text to standard out for each command or system procedure you enter. Depending on the command or system procedure you run, this output can be extensive.
For example, if you run sp_who, Adaptive Server prints all SQL text associated with this system procedure (the output is abbreviated for space purposes):
sp_who 2007/02/23 02:18:25.77 SQL Text: sp_who Sproc: sp_who, Line: 0 Sproc: sp_who, Line: 20 Sproc: sp_who, Line: 22 Sproc: sp_who, Line: 25 Sproc: sp_who, Line: 27 Sproc: sp_who, Line: 30 Sproc: sp_who, Line: 55 Sproc: sp_who, Line: 64 Sproc: sp_autoformat, Line: 0 Sproc: sp_autoformat, Line: 165 Sproc: sp_autoformat, Line: 167 Sproc: sp_autoformat, Line: 177 Sproc: sp_autoformat, Line: 188 . . . Sproc: sp_autoformat, Line: 326 Sproc: sp_autoformat, Line: 332 SQL Text: INSERT #colinfo_af(colid,colname,usertype,type,typename,collength,maxlength,autoformat,selected,selectorder,asname,mbyte) SELECT c.colid,c.name,t.usertype,t.type,t.name,case when c.length < 80 then 80 else c.length end,0,0,0,0,c.name,0 FROM tempdb.dbo.syscolumns c,tempdb.dbo.systypes t WHERE c.id=1949946031 AND c.usertype=t.usertype Sproc: sp_autoformat, Line: 333 Sproc: sp_autoformat, Line: 334 . . . Sproc: sp_autoformat, Line: 535 Sproc: sp_autoformat, Line: 0 Sproc: sp_autoformat, Line: 393 Sproc: sp_autoformat, Line: 395 . . . Sproc: sp_autoformat, Line: 686 Sproc: sp_autoformat, Line: 688 SQL Text: UPDATE #colinfo_af SET maxlength=(SELECT isnull(max(isnull(char_length(convert(varchar(80),fid)),4)),1) FROM #who1result ), autoformat = 1, mbyte=case when usertype in (24, 25, 34, 35) then 1 else 0 end WHERE colname='fid' Sproc: sp_autoformat, Line: 689 Sproc: sp_autoformat, Line: 690 . . . Sproc: sp_autoformat, Line: 815 Sproc: sp_autoformat, Line: 818 SQL Text: SELECT fid=right(space(80)+isnull(convert(varchar(80),fid),'NULL'),3), spid=right(space(80)+isnull(convert(varchar(80),spid),'NULL'),4), status=SUBSTRING(convert(varchar(80),status),1,8), loginame=SUBSTRING(convert(varchar(80),loginame),1,8), origname=SUBSTRING(convert(varchar(80),origname),1,8), hostname=SUBSTRING(convert(varchar(80),hostname),1,8), blk_spid=right(space(80)+isnull(convert(varchar(80),blk_spid),'NULL'),8), dbname=SUBSTRING(convert(varchar(80),dbname),1,6), tempdbname=SUBSTRING(convert(varchar(80),tempdbname),1,10), cmd=SUBSTRING(convert(varchar(80),cmd),1,17), block_xloid=right(space(80)+isnull(convert(varchar(80),block_xloid),'NULL'),11) FROM #who1result order by fid, spid, dbname
Sproc: sp_autoformat, Line: 819 Sproc: sp_autoformat, Line: 820 Sproc: sp_autoformat, Line: 826 Sproc: sp_who, Line: 68 Sproc: sp_who, Line: 70 fid spid status loginame origname hostname blk_spid dbname tempdbnamecmd block_xloid --- ---- -------- -------- -------- -------- -------- ------------------------- ----------- 0 2 sleeping NULL NULL NULL 0 master tempdb DEADLOCK TUNE 0 0 3 sleeping NULL NULL NULL 0 master tempdb ASTC HANDLER 0 0 4 sleeping NULL NULL NULL 0 master tempdb CHECKPOINT SLEEP 0 0 5 sleeping NULL NULL NULL 0 master tempdb HK WASH 0 0 6 sleeping NULL NULL NULL 0 master tempdb HK GC 0 0 7 sleeping NULL NULL NULL 0 master tempdb HK CHORES 0 0 8 sleeping NULL NULL NULL 0 master tempdb PORT MANAGER 0 0 9 sleeping NULL NULL NULL 0 master tempdb NETWORK HANDLER 0 0 10 sleeping NULL NULL NULL 0 master tempdb LICENSE HEARTBEAT 0 0 1 running sa sa echo 0 master tempdb INSERT 0 (10 rows affected) (return status = 0)
To disable show_sqltext, enter:
set show_sqltext off
You must have the sa or sso roles 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.
