SQL Server - A Procedure to Display Columns as Rows

updated: Oct 12, 2012

As an Oracle DBA, I am used to using command line interface (CLI) for my many DBA tasks. When some long rows are output from a query, it is often very unconvenient to view in CLI windows due to text wrapping at certain lengh. Tom Kyte has developed a procedure in Oracle to display columns as rows. Here I try to accomolish the same thing in SQL Server.

My procedure is called usp_p, the source code can be downloaded here.

Below is an example of using this procedure.

First of all, here is the script (top10_ela.sql ) that displays top 10 sqls ordered by average execution time:

declare @sqltext varchar(4000)

set @sqltext = '
SELECT   top 10
sql_handle
, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
     ( (CASE WHEN statement_end_offset = -1
            THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
      ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
 , (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
 , max_elapsed_time/1000 AS [MaxExecTime in ms]
 , min_elapsed_time/1000 AS [MinExecTime in ms]
 , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, qs.execution_count AS NumberOfExecs
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, creation_time
, last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  s2
WHERE s2.objectid is null
ORDER BY [Avg Exec Time in ms] DESC
';



exec dbadmin.dbo.usp_p @sqltext

go

Below is a sample output from my test sql server instance:

$ sqlcmd -i  top10_ela.sql


OUTPUT COLUMNS AS ROWS - MISSING COLUMN NAME MEANS NULL VALUE

----------------------------------------------
sql_handle                    : 0x0200000027337b02ca2b54f71198517d10d457c2e93900b900000000000000000000000000000000
sql_statement                 : declare StmtCur cursor for
SELECT   top 10
sql_handle
, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
     ( (CASE WHEN statement_end_offset = -1
            THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
      ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement
 , (total_elapsed_time/execution_count)/1000 AS [Avg Exec Time in ms]
 , max_elapsed_time/1000 AS [MaxExecTime in ms]
 , min_elapsed_time/1000 AS [MinExecTime in ms]
 , (total_worker_time/execution_count)/1000 AS [Avg CPU Time in ms]
, qs.execution_count AS NumberOfExecs
, (total_logical_writes+total_logical_Reads)/execution_count AS [Avg Logical IOs]
, max_logical_reads AS MaxLogicalReads
, min_logical_reads AS MinLogicalReads
, max_logical_writes AS MaxLogicalWrites
, min_logical_writes AS MinLogicalWrites
, creation_time
, last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  s2
WHERE s2.objectid is null
ORDER BY [Avg Exec Time in ms] DESC

Avg Exec Time in ms           :        307
MaxExecTime in ms             :        307
MinExecTime in ms             :        307
Avg CPU Time in ms            :        307
NumberOfExecs                 :          1
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:46PM
last_execution_time           : Oct 12 2012  8:46PM
----------------------------------------------
sql_handle                    : 0x02000000cff2b929b1aebd642c8187892cf718f0921cf8bb00000000000000000000000000000000
sql_statement                 : select * from sys.dm_exec_query_stats;

Avg Exec Time in ms           :          0
MaxExecTime in ms             :          0
MinExecTime in ms             :          0
Avg CPU Time in ms            :          0
NumberOfExecs                 :          1
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:45PM
last_execution_time           : Oct 12 2012  8:45PM
----------------------------------------------
sql_handle                    : 0x020000004bee4622a78521352165a8a19fc021a4e00df3c900000000000000000000000000000000
sql_statement                 : select table_id, item_guid, oplsn_fseqno, oplsn_bOffset, oplsn_slotid
 from [DBAdmin].[sys].[filetable_updates_2105058535] with (readpast) order by table_id
Avg Exec Time in ms           :          0
MaxExecTime in ms             :          0
MinExecTime in ms             :          0
Avg CPU Time in ms            :          0
NumberOfExecs                 :          2
Avg Logical IOs               :          0
MaxLogicalReads               :          0
MinLogicalReads               :          0
MaxLogicalWrites              :          0
MinLogicalWrites              :          0
creation_time                 : Oct 12 2012  8:46PM
last_execution_time           : Oct 12 2012  8:46PM
----------------------------------------------

Note: I did not verify that every data type can be shown properly as of now, I will probably have to modify the code as time goes.


Update: Oct 19,2012

Learned that SQL Server has a very simple way to turn the query output to XML format by appending "for xml path" at the end.  This has similar effect as output "columns as rows". For example, run: select top 2 * from sys.objects for xml path , it will generate the following XML format: