Display rows vertically

,

When you have a table with many columns, it can become a chore to remember all of the column names and find what you need. This script displays the selected rows vertically with the columns in alpha order to help locate columns when you don't know the exact names (or if you just want to see all the columns without scrolling horizontally). This uses SQL Server 2005's UNPIVOT command, and so will not work in SQL 2000.

It will not display columns having null values or columns of type text, ntext, image, or timestamp.

 

It takes two parameters, the second of which is optional: tablename (in single-quotes) and an optional Where clause in quotes (including the word WHERE).

 

For example:

vselect 'mytable', 'where id=123'

 

ALTER procedure [dbo].[vselect]
@table_name varchar(max),
@where_clause varchar(max)=''
AS
    -- Display table row(s) vertically, in alphabetic order.
    -- Usage: vselect 'tablename' [,'where ~']
set nocount on

declare @col_names varchar(max)
declare @variants varchar(max)
declare @sql varchar(max)
set @col_names=''
set @variants = ''

select @col_names = @col_names + c.column_name + ',',
    @variants = @variants + 'CONVERT(SQL_VARIANT, '+c.column_name + ') AS '+c.column_name+','
from 
    information_schema.columns c
where table_name = @table_name
 and table_schema = 'dbo'
 and data_type not in ('text','ntext','image','timestamp')
order by c.column_name

set @col_names = left(@col_names,len(@col_names)-1)
set @variants = left(@variants,len(@variants)-1)

set @sql = 
'
SELECT 
 TBLPIVOT.Column_Name,
 TBLPIVOT.Value
FROM 
(SELECT ' + @variants +
' FROM ' + @table_name + ' ' +
@where_clause + ') x UNPIVOT (VALUE FOR Column_Name IN ('
+ @col_names + ')) AS TBLPIVOT'

exec(@sql)

Rate

2.2 (5)

Share

Share

Rate

2.2 (5)