Display Rows Vertically or Alphabetically

,

The first included script creates a procedure that displays a single row from a table vertically in alphabetical order with a column name and value in each row of the result set. This procedure is very handy when a table contains hundreds of columns ordered quite randomly. This will save you hours wasted per week simly trying to find a particular column in resultsets.

The second included script will do a standard "select" returning many rows but with all columns in alphabetical order. I haven't updated these to work with schemas other than the default one but most people are using dbo anyway.

To call these, supply a table name and a "where" clause as in the following example:

exec AzDisplay Customer, ''CustomerKey = 123'

exec AzSelect Customer, ''CustomerKey = 123'

My purpose in submitting these procedures is to show that key features are missing in SSMS that other products have such as dBASE and Sybase add-ons. One constant irritation for me in the SSMS Query window is the lack of commands in SQLCMD mode to control output directly such as having some result sets display as text and others as grids and others as vertical rows with or without headings and others with columns in alphabetical or type order.

 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AzDisplay]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AzDisplay]
go

create procedure [dbo].[AzDisplay]
	@TableName varchar(128) = null,
	@WhereClause1 varchar(1000) = null,
	@WhereClause2 varchar(1000) = null
as
-- written by Bill Talada

set nocount on

if @TableName is null
begin
	print 'Samples on selecting all columns alphabetically (one row):'
	print '  exec AzDisplay Customer,''CustomerKey = 123'''
	return 0
end

declare
	@col sysname,
	@list varchar(4000),
	@sql varchar(8000),
	@crlf varchar(2),
	@tab varchar(1)

set @crlf=char(13)+char(10)
set @tab=char(9)

set @col=''
set @list=''

create table #colvals( col varchar(128), val varchar(max))
create table #colvals2( col varchar(128), val varchar(max))

select
	@col = min(c.name)
from
	sys.columns c
join
	sys.tables t
	on c.object_id=t.object_id
where
	t.name = @TableName

while @col is not null
begin
	set @sql = 'insert into #colvals select '''+@col+''',cast(' + @col + ' as varchar(max)) from '+@tablename+ ' where '+@whereclause1
	exec( @sql)

	select
		@col = min(c.name)
	from
		sys.columns c
	join
		sys.tables t
		on c.object_id=t.object_id
	where
		t.name = @TableName
	and
		c.name > @col
end

----------------
if @whereclause2 is not null
begin
	select
		@col = min(c.name)
	from
		sys.columns c
	join
		sys.tables t
		on c.object_id=t.object_id
	where
		t.name = @TableName

	while @col is not null
	begin
		set @sql = 'insert into #colvals2 select '''+@col+''',cast(' + @col + ' as varchar(max)) from '+@tablename+ ' where '+@whereclause2
		exec( @sql)

		select
			@col = min(c.name)
		from
			sys.columns c
		join
			sys.tables t
			on c.object_id=t.object_id
		where
			t.name = @TableName
		and
			c.name > @col
	end
end
----------------

if @whereclause2 is null
begin
	select
		col,
		isnull(val,'<null>') as val 
	from 
		#colvals
end
else
begin
	select
		c1.col,
		isnull(c1.val,'<null>') as val1,
		isnull(c2.val,'<null>') as val2
	from 
		#colvals c1
	join
		#colvals2 c2 on c1.col = c2.col
	order by
		c1.col
end

drop table #colvals
drop table #colvals2

return 0
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AzSelect]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AzSelect]
go

create procedure [dbo].[AzSelect]
	@TableName varchar(128) = null,
	@WhereClause varchar(1000) = null
as
-- written by Bill Talada

if @TableName is null
begin
	print 'Samples on selecting all columns alphabetically:'
	print '  exec AzSelect Accessions,''1=1'''
	return 0
end

declare
	@col sysname,
	@list varchar(max),
	@sql varchar(max),
	@crlf varchar(2),
	@tab varchar(1)

set @crlf=char(13)+char(10)
set @tab=char(9)

set @col=''
set @list=''

select
	@col = min(c.name)
from
	sys.columns c
join
	sys.tables t
	on c.object_id=t.object_id
where
	t.name = @TableName

while @col is not null
begin
	if datalength(@list) > 1 set @list = @list + ',' + @crlf

	set @list = @list + @tab + @col
	--print @list

	select
		@col = min(c.name)
	from
		sys.columns c
	join
		sys.tables t
		on c.object_id=t.object_id
	where
		t.name = @TableName
	and
		c.name > @col
end

set @sql = 'select'
	+@crlf+@list
	+@crlf+'from'+@crlf+@tab+@TableName
	+@crlf+'where'+@crlf+@tab+@WhereClause

print @sql
exec (@sql)

return 0
go

Rate

4 (6)

Share

Share

Rate

4 (6)